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>Testme@gmail.com</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>JSamujh@walmart.com</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/
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 |