Parsing XML Data Into SQL Server

Question

I am struggling with importing XML Data into SQL Server 2016. I have tried a few things, but keep either getting errors or just no data is returned.

I have this XML Data stored in an XML file (limited the data because it is pretty sensitive:

<?xml version='1.0' encoding='UTF-8'?>
<wd:Report_Data xmlns:wd="urn:com.workday.report/Worker_Details_-_EXPORT_-_Workplace">
    <wd:Report_Entry>
        <wd:Active_Status>0</wd:Active_Status>
        <wd:Legal_Name_-_First_Name>Charlotte</wd:Legal_Name_-_First_Name>
        <wd:Position>Executive Housekeeper I</wd:Position>
        <wd:Worker_Management_Level>Supervisor</wd:Worker_Management_Level>
        <wd:continuous_service_date>1979-04-29-08:00</wd:continuous_service_date>
        <wd:Hire_Date>1979-04-29-08:00</wd:Hire_Date>
        <wd:termination_date>2019-12-22-08:00</wd:termination_date>
        <wd:Anniversary_Month>04</wd:Anniversary_Month>
        <wd:Years_of_Service>40</wd:Years_of_Service>
        <wd:Employee_Type>Hotel</wd:Employee_Type>
        <wd:Time_Type>Full Time</wd:Time_Type>
        <wd:Pay_Rate_Type>Salary</wd:Pay_Rate_Type>
        <wd:Marital_Status>Single</wd:Marital_Status>
    </wd:Report_Entry>
    <wd:Report_Entry>
        <wd:Active_Status>0</wd:Active_Status>
        <wd:Legal_Name_-_First_Name>Robert</wd:Legal_Name_-_First_Name>
        <wd:Cost_Center_-_Name>Electronics</wd:Cost_Center_-_Name>
        <wd:Work_Address_-_State_Province>Missouri</wd:Work_Address_-_State_Province>
        <wd:Position>Manager Of Voice Networks</wd:Position>
        <wd:Worker_Management_Level>Manager</wd:Worker_Management_Level>
        <wd:continuous_service_date>1980-02-25-08:00</wd:continuous_service_date>
        <wd:Hire_Date>1980-02-25-08:00</wd:Hire_Date>
        <wd:termination_date>2020-03-22-07:00</wd:termination_date>
        <wd:Anniversary_Month>02</wd:Anniversary_Month>
        <wd:Years_of_Service>40</wd:Years_of_Service>
        <wd:Employee_Type>Corporate</wd:Employee_Type>
        <wd:Time_Type>Full Time</wd:Time_Type>
        <wd:Pay_Rate_Type>Salary</wd:Pay_Rate_Type>
        <wd:Marital_Status>Married</wd:Marital_Status>
    </wd:Report_Entry>
</wd:Report_Data>

I have this code that I am trying to use, but keep getting just an empty result:

SELECT  
    XMLCol.ReportEntry.query('Active_Status').value('.', 'VARCHAR(20)') AS ActiveStatus
FROM
    (SELECT 
         CAST(XMLCol AS XML)
     FROM 
         OPENROWSET(BULK '\\afcn2011\root\DATA\VisualCron\Employee Export\EmployeeExport.xml', SINGLE_BLOB) AS T(XMLCol)
    ) AS T(XMLCol)
CROSS APPLY 
    XMLCol.nodes('Report_Data/Report_Entry') AS XMLCol(ReportEntry);

Because you are ignoring XML namespaces.

Serg
– Serg

2021-09-07 18:14:48 +00:00
Commented Sep 7, 2021 at 18:14 — Serg
– Serg, Commented Sep 7, 2021 at 18:14
See the doc on xml namespaces.

Dan Guzman
– Dan Guzman

2021-09-07 18:15:17 +00:00
Commented Sep 7, 2021 at 18:15 — Dan Guzman
– Dan Guzman, Commented Sep 7, 2021 at 18:15

marc_s · Accepted Answer · 2021-09-07 19:34:57Z

1

You need to respect and include the XML namespace defined in your document.

Try something like this:

-- define the namespace and give it a prefix - here "wd"
;WITH XMLNAMESPACES ('urn:com.workday.report/Worker_Details_-_EXPORT_-_Workplace' as wd)
SELECT  
    -- you need to include namespace prefix when referring to the XML element
    -- also: is "VARCHAR(20)" really the best datatype?? Looks more like "INT" to me ...
    XMLCol.ReportEntry.value('(wd:Active_Status/text())[1]', 'VARCHAR(20)') AS ActiveStatus
FROM
    (SELECT 
         CAST(XMLCol AS XML)
     FROM 
         OPENROWSET(BULK '\\afcn2011\root\DATA\VisualCron\Employee Export\EmployeeExport.xml', SINGLE_BLOB) AS T(XMLCol)
    ) AS T(XMLCol)
CROSS APPLY 
    -- you need to include namespace prefix in your XPath expression
    XMLCol.nodes('/wd:Report_Data/wd:Report_Entry') AS XMLCol(ReportEntry);

answered Sep 7, 2021 at 19:34

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Femmer Over a year ago

Thank you for the help. I was able to get it fixed with this. And yes, VARCHAR(20) is incorrect, it is actually a BIT, but it is an old legacy dataset that someone decided that NVARCHAR(100) was good for literally everything...

Collectives™ on Stack Overflow

Parsing XML Data Into SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related