xmlt-sqlxquerysql-server-2019

Import XML data into SQLSERVER using TSQL


I have developed query to fetch XML file from a FTP path using OPENROWSET. When I try to read the nodes data, the query gives no result.

I have consulted many blogs and material but unable to find success. Please see the reference screenshot of my TSQL which reads the xml correctly but when I select the output column it returns blank.

XML

<?xml version="1.0" encoding="utf-8"?>
<ws:Worker_Sync xmlns:ws="urn:com.workday/workersync"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <ws:Header>
        <ws:Version>v29.2</ws:Version>
        <ws:Prior_Entry_Time>2023-09-27T11:00:00.000-07:00</ws:Prior_Entry_Time>
        <ws:Current_Entry_Time>2023-09-27T12:00:00.000-07:00</ws:Current_Entry_Time>
        <ws:Prior_Effective_Time>2023-09-27T11:00:00.000-07:00</ws:Prior_Effective_Time>
        <ws:Current_Effective_Time>2023-09-27T12:00:00.000-07:00</ws:Current_Effective_Time>
        <ws:Full_File>false</ws:Full_File>
        <ws:Document_Retention_Policy>180</ws:Document_Retention_Policy>
        <ws:Worker_Count>931</ws:Worker_Count>
    </ws:Header>
    <ws:Worker>
        <ws:Summary>
            <ws:Employee_ID>220511967</ws:Employee_ID>
            <ws:Name>Jazmine Samujh</ws:Name>
        </ws:Summary>
        <ws:Eligibility>true</ws:Eligibility>
        <ws:Personal>
            <ws:Name_Data>
                <ws:Name_Type>Legal</ws:Name_Type>
                <ws:First_Name>Jazmine</ws:First_Name>
                <ws:Last_Name>Samujh</ws:Last_Name>
                <ws:Formatted_Name>Jazmine Samujh</ws:Formatted_Name>
                <ws:Reporting_Name>Samujh, Jazmine</ws:Reporting_Name>
            </ws:Name_Data>
            <ws:Name_Data>
                <ws:Name_Type>Preferred</ws:Name_Type>
                <ws:First_Name>Jazmine</ws:First_Name>
                <ws:Last_Name>Samujh</ws:Last_Name>
                <ws:Formatted_Name>Jazmine Samujh</ws:Formatted_Name>
                <ws:Reporting_Name>Samujh, Jazmine</ws:Reporting_Name>
            </ws:Name_Data>
            <ws:Gender>Global_Female</ws:Gender>
            <ws:Birth_Date>1980-12-04-08:00</ws:Birth_Date>
            <ws:Address_Data>
                <ws:Address_Type>HOME</ws:Address_Type>
                <ws:Address_Is_Public>false</ws:Address_Is_Public>
                <ws:Is_Primary>true</ws:Is_Primary>
                <ws:Address_Line_Data ws:Label="Address Line 1"
                                      ws:Type="ADDRESS_LINE_1">406 Avalon
                    Dr.</ws:Address_Line_Data>
                <ws:Municipality>South San Francisc</ws:Municipality>
                <ws:Region>California</ws:Region>
                <ws:Postal_Code>94080</ws:Postal_Code>
                <ws:Country>US</ws:Country>
            </ws:Address_Data>
            <ws:Address_Data>
                <ws:Address_Type>WORK</ws:Address_Type>
                <ws:Address_Is_Public>true</ws:Address_Is_Public>
                <ws:Is_Primary>true</ws:Is_Primary>
                <ws:Address_Line_Data ws:Label="Address Line 1"
                                      ws:Type="ADDRESS_LINE_1">850 Cherry
                    Avenue</ws:Address_Line_Data>
                <ws:Municipality>San Bruno</ws:Municipality>
                <ws:Region>California</ws:Region>
                <ws:Postal_Code>94066-3031</ws:Postal_Code>
                <ws:Country>US</ws:Country>
                <ws:Address_Usage>Mailing_Postal</ws:Address_Usage>
            </ws:Address_Data>
            <ws:Phone_Data>
                <ws:Phone_Type>HOME</ws:Phone_Type>
                <ws:Phone_Device_Type>Global_Not_Specified</ws:Phone_Device_Type>
                <ws:Phone_Is_Public>false</ws:Phone_Is_Public>
                <ws:Is_Primary>true</ws:Is_Primary>
                <ws:Formatted_Phone_Number>+1 650 9225273</ws:Formatted_Phone_Number>
                <ws:International_Phone_Code>1</ws:International_Phone_Code>
                <ws:Phone_Area_Code>650</ws:Phone_Area_Code>
                <ws:Phone_Number>9225273</ws:Phone_Number>
                <!--2FA-->
                <ws:Enroll_MFA>Y</ws:Enroll_MFA>
                <ws:WD_Personal_Phone_Country_Code>1</ws:WD_Personal_Phone_Country_Code>
                <ws:WD_Personal_Phone_Number>6509225273</ws:WD_Personal_Phone_Number>
                <ws:WD_Personal_Email>[email protected]</ws:WD_Personal_Email>-->
                <!--2FA--></ws:Phone_Data>
            <ws:Phone_Data>
                <ws:Phone_Type>WORK</ws:Phone_Type>
                <ws:Phone_Device_Type>Global_Not_Specified</ws:Phone_Device_Type>
                <ws:Phone_Is_Public>true</ws:Phone_Is_Public>
                <ws:Is_Primary>true</ws:Is_Primary>
                <ws:Formatted_Phone_Number>+1 650 8375404</ws:Formatted_Phone_Number>
                <ws:International_Phone_Code>1</ws:International_Phone_Code>
                <ws:Phone_Area_Code>650</ws:Phone_Area_Code>
                <ws:Phone_Number>8375404</ws:Phone_Number>
            </ws:Phone_Data>
            <ws:Email_Data>
                <ws:Email_Type>WORK</ws:Email_Type>
                <ws:Email_Is_Public>true</ws:Email_Is_Public>
                <ws:Is_Primary>true</ws:Is_Primary>
                <ws:Email_Address>[email protected]</ws:Email_Address>
                <!--EmailForAll-->
                <ws:Email_Domain>walmart.com</ws:Email_Domain>
                <ws:Email_External_Flag>N</ws:Email_External_Flag>
                <ws:Email_Flag>Y</ws:Email_Flag>
                <!--EmailForAll-->
            </ws:Email_Data>
            <ws:Ethnicity>US_Asian_or_Pacific_Islander</ws:Ethnicity>
        </ws:Personal>
        <ws:Status>
            <ws:Employee_Status>Active</ws:Employee_Status>
            <ws:Active>true</ws:Active>
            <ws:Hire_Date>2015-12-21-08:00</ws:Hire_Date>
            <ws:Original_Hire_Date>2014-09-02-07:00</ws:Original_Hire_Date>
            <ws:Hire_Reason>Hire_Employee_Hire_Employee_Conversion</ws:Hire_Reason>
            <ws:Continuous_Service_Date>2015-12-21-08:00</ws:Continuous_Service_Date>
            <ws:First_Day_of_Work>2015-12-21-08:00</ws:First_Day_of_Work>
            <ws:Seniority_Date>2015-12-21-08:00</ws:Seniority_Date>
            <ws:Terminated>false</ws:Terminated>
            <ws:Has_International_Assignment>false</ws:Has_International_Assignment>
            <ws:Rehire>false</ws:Rehire>
        </ws:Status>
        <ws:Position>
            <ws:Operation>NONE</ws:Operation>
            <ws:Position_ID>P_0001816962</ws:Position_ID>
            <ws:Time_in_Job_Profile_Start_Date>2022-03-12-08:00</ws:Time_in_Job_Profile_Start_Date>
            <ws:Effective_Date>2021-12-18-08:00</ws:Effective_Date>
            <ws:Job_Effective_Date>2021-12-18-08:00</ws:Job_Effective_Date>
            <ws:Primary_Position>true</ws:Primary_Position>
            <ws:Position_Title>(USA) Analyst II, Talent Acquisition, Ecomm</ws:Position_Title>
            <ws:Business_Title>(USA) Analyst II, Talent Acquisition, Ecomm</ws:Business_Title>
            <ws:Worker_Type>Regular_Permanent</ws:Worker_Type>
            <ws:Position_Time_Type>Full_time</ws:Position_Time_Type>
            <ws:Job_Exempt>true</ws:Job_Exempt>
            <ws:Scheduled_Weekly_Hours>40</ws:Scheduled_Weekly_Hours>
            <ws:Default_Weekly_Hours>40</ws:Default_Weekly_Hours>
            <ws:Full_Time_Equivalent_Percentage>100.00</ws:Full_Time_Equivalent_Percentage>
            <ws:Pay_Rate_Type>Global_Salaried</ws:Pay_Rate_Type>
            <ws:Job_Classification_Data>
                <ws:Job_Classification>JAID_HRE13058</ws:Job_Classification>
            </ws:Job_Classification_Data>
            <ws:Organization_Data>
                <ws:Operation>NONE</ws:Operation>
                <ws:Organization>A110</ws:Organization>
                <ws:Organization_Type>Company</ws:Organization_Type>
            </ws:Organization_Data>
            <ws:Organization_Data>
                <ws:Operation>NONE</ws:Operation>
                <ws:Organization>US11095</ws:Organization>
                <ws:Organization_Type>Cost_Center</ws:Organization_Type>
            </ws:Organization_Data>
            <ws:Organization_Data>
                <ws:Operation>NONE</ws:Operation>
                <ws:Organization>USA - Bi-weekly_CA</ws:Organization>
                <ws:Organization_Type>Pay_Group</ws:Organization_Type>
            </ws:Organization_Data>
            <ws:Organization_Data>
                <ws:Operation>NONE</ws:Operation>
                <ws:Organization>Sup_0000039529</ws:Organization>
            </ws:Organization_Data>
        </ws:Position>
    </ws:Worker>
</ws:Worker_Sync>

TSQL (inner select works fine, outer return empty column):

select MY_XML.phrase.query('phrase').value('.' , 'VARCHAR(200)')
FROM (
SELECT CAST ( MY_XML as xml) FROM OPENROWSET(BULK 'E:\Test\version.xml', SINGLE_BLOB) AS T(MY_XML)
) AS T(MY_XML)
CROSS APPLYMY_XML.nodes('phrases/phrase') AS MY_XML(phrase);

I consulted help from blogs i.e.

https://www.mssqltips.com/sqlservertip/5707/simple-way-to-import-xml-data-into-sql-server-with-tsql/


Solution

  • You need to take into account XML namespaces. It is done via XMLNAMESPACES() clause.

    All XML elements in the file are bound to the xmlns:ws="urn:com.workday/workersync" namespace.

    Check it out.

    SQL

    WITH XMLNAMESPACES(DEFAULT 'urn:com.workday/workersync')
    SELECT c.value('(Version/text())[1]' , 'VARCHAR(200)') AS Version
        , c.value('(Document_Retention_Policy/text())[1]' , 'INT') AS Document_Retention_Policy
        , c.value('(Worker_Count/text())[1]' , 'INT') AS Worker_Count
    FROM (
        SELECT TRY_CAST(MY_XML as XML) FROM OPENROWSET(BULK 'E:\Test\version.xml', SINGLE_BLOB) AS T(MY_XML)
    ) AS T(MY_XML)
    CROSS APPLY MY_XML.nodes('/Worker_Sync/Header') AS MY_XML(c);
    

    Output

    Version Document_Retention_Policy Worker_Count
    v29.2 180 931