Parsing XML Data using T-SQL-CodePudding

I am attempting to parse XML data using SQL Server 2019.

I have the following dataset:

<?xml version='1.0' encoding='UTF-8'?>
<wd:Report_Data xmlns:wd="urn:com.test.report/Reported_Time_Blocks_for_a_Worker_s__-_Test_Worked_Hours_-_Copy">
    <wd:Report_Entry>
        <wd:Worker_group>
            <wd:Employee_ID>111111</wd:Employee_ID>
            <wd:Legal_Name_-_First_Name>TestFirstName</wd:Legal_Name_-_First_Name>
            <wd:Legal_Name_-_Last_Name>TestLastName</wd:Legal_Name_-_Last_Name>
        </wd:Worker_group>
        <wd:Employee_Type wd:Descriptor="Property">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Employee_Type_ID">Regular</wd:ID>
        </wd:Employee_Type>
        <wd:Pay_Rate_Type wd:Descriptor="Salary">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Pay_Rate_Type_ID">Salary</wd:ID>
        </wd:Pay_Rate_Type>
        <wd:Pay_Rate>0</wd:Pay_Rate>
        <wd:Home_Cost_Center wd:Descriptor="CC0001 Test Property">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Organization_Reference_ID">CC0001</wd:ID>
            <wd:ID wd:type="Cost_Center_Reference_ID">CC0001</wd:ID>
        </wd:Home_Cost_Center>
        <wd:Home_Subarea wd:Descriptor="HS001 Test Home">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Organization_Reference_ID">HS001</wd:ID>
            <wd:ID wd:type="Custom_Organization_Reference_ID">HS001</wd:ID>
        </wd:Home_Subarea>
        <wd:Home_Job_Profile wd:Descriptor="Test Job Title">
            <wd:ID wd:type="WID">6db2e1cea51601ac7fdd4d733e001646</wd:ID>
            <wd:ID wd:type="Job_Profile_ID">100000001</wd:ID>
        </wd:Home_Job_Profile>
        <wd:Reported_Date>2022-04-11-07:00</wd:Reported_Date>
        <wd:Reported_Quantity>8</wd:Reported_Quantity>
        <wd:Time_Entry_Code wd:Descriptor="Worked Time (Hours Only)">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Time_Code_Reference_ID">Worked_Time_Hours_Only_Time_Code</wd:ID>
        </wd:Time_Entry_Code>
        <wd:Calculation_Tags wd:Descriptor="Regular">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Time_Calculation_Tag_ID">Regular</wd:ID>
        </wd:Calculation_Tags>
    </wd:Report_Entry>

I know how to get some of the data, but I can't figure out how to work with the multiple row groups.

I have this query that pulls the Employee_ID, Legal_Name_-First_Name, and Legal_Name-_Last_Name, but I can't figure out how to also pull the Employee_Type, Pay_Rate_Type, etc. with it:

DECLARE @XML XML
    SET @XML = ( SELECT XMLData FROM #XML )

DECLARE @XML XML
    SET @XML = ( SELECT XMLData FROM #XML )

;WITH XMLNAMESPACES( 'urn:com.test.report/Reported_Time_Blocks_for_a_Worker_s__-_Test_Worked_Hours_-_Copy' AS wd )
SELECT  EmployeeId = Data.Col.value( 'wd:Employee_ID[1]', 'VARCHAR(12)' ),
        FirstName = Data.Col.value( 'wd:Legal_Name_-_First_Name[1]', 'VARCHAR(100)' ),
        LastName = Data.Col.value( 'wd:Legal_Name_-_Last_Name[1]', 'VARCHAR(100)' )
    --INTO #EmployeeInformation
    FROM @XML.nodes( 'wd:Report_Data/wd:Report_Entry/wd:Worker_group' ) Data( Col )

I am essentially trying to pull the following data from the XML above:

Employee_ID	Legal_Name_-_First_Name	Legal_Name_-_Last_Name	Employee_Type_ID	Pay_Rate_Type_ID	Pay_Rate	Cost_Center_Reference_ID	Reported_Date	Reported_Quantity	Home_Job_Profile_Descriptor
111111	TestFirstName	TestLastName	Regular	Salary	0	CC0001	2022-04-11-07:00	8	Test Job Title

CodePudding user response：

Please try the following solution.

You may need to adjust data types.

SQL

DECLARE @xml XML =
N'<wd:Report_Data xmlns:wd="urn:com.test.report/Reported_Time_Blocks_for_a_Worker_s__-_Test_Worked_Hours_-_Copy">
    <wd:Report_Entry>
        <wd:Worker_group>
            <wd:Employee_ID>111111</wd:Employee_ID>
            <wd:Legal_Name_-_First_Name>TestFirstName</wd:Legal_Name_-_First_Name>
            <wd:Legal_Name_-_Last_Name>TestLastName</wd:Legal_Name_-_Last_Name>
        </wd:Worker_group>
        <wd:Employee_Type wd:Descriptor="Property">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Employee_Type_ID">Regular</wd:ID>
        </wd:Employee_Type>
        <wd:Pay_Rate_Type wd:Descriptor="Salary">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Pay_Rate_Type_ID">Salary</wd:ID>
        </wd:Pay_Rate_Type>
        <wd:Pay_Rate>0</wd:Pay_Rate>
        <wd:Home_Cost_Center wd:Descriptor="CC0001 Test Property">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Organization_Reference_ID">CC0001</wd:ID>
            <wd:ID wd:type="Cost_Center_Reference_ID">CC0001</wd:ID>
        </wd:Home_Cost_Center>
        <wd:Home_Subarea wd:Descriptor="HS001 Test Home">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Organization_Reference_ID">HS001</wd:ID>
            <wd:ID wd:type="Custom_Organization_Reference_ID">HS001</wd:ID>
        </wd:Home_Subarea>
        <wd:Home_Job_Profile wd:Descriptor="Test Job Title">
            <wd:ID wd:type="WID">6db2e1cea51601ac7fdd4d733e001646</wd:ID>
            <wd:ID wd:type="Job_Profile_ID">100000001</wd:ID>
        </wd:Home_Job_Profile>
        <wd:Reported_Date>2022-04-11-07:00</wd:Reported_Date>
        <wd:Reported_Quantity>8</wd:Reported_Quantity>
        <wd:Time_Entry_Code wd:Descriptor="Worked Time (Hours Only)">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Time_Code_Reference_ID">Worked_Time_Hours_Only_Time_Code</wd:ID>
        </wd:Time_Entry_Code>
        <wd:Calculation_Tags wd:Descriptor="Regular">
            <wd:ID wd:type="WID">xxxxxxxxxxxxxxxxxxxxxxxxxx</wd:ID>
            <wd:ID wd:type="Time_Calculation_Tag_ID">Regular</wd:ID>
        </wd:Calculation_Tags>
    </wd:Report_Entry>
</wd:Report_Data>';

;WITH XMLNAMESPACES(DEFAULT 'urn:com.test.report/Reported_Time_Blocks_for_a_Worker_s__-_Test_Worked_Hours_-_Copy')
SELECT EmployeeId = c.value('(Worker_group/Employee_ID/text())[1]', 'VARCHAR(12)' )
    , FirstName = c.value('(Worker_group/Legal_Name_-_First_Name/text())[1]', 'VARCHAR(100)' )
    , LastName = c.value('(Worker_group/Legal_Name_-_Last_Name/text())[1]', 'VARCHAR(100)' )
    , Employee_Type_ID = c.value('(Employee_Type/ID[@*:type="Employee_Type_ID"]/text())[1]', 'VARCHAR(100)' )
    , Pay_Rate_Type_ID = c.value('(Pay_Rate_Type/ID[@*:type="Pay_Rate_Type_ID"]/text())[1]', 'VARCHAR(100)' )
    , Pay_Rate = c.value('(Pay_Rate/text())[1]', 'VARCHAR(100)' )
    , Cost_Center_Reference_ID = c.value('(Home_Cost_Center/ID[@*:type="Cost_Center_Reference_ID"]/text())[1]', 'VARCHAR(100)' )
    , Reported_Date = c.value('(Reported_Date/text())[1]', 'VARCHAR(100)' )
    , Reported_Quantity = c.value('(Reported_Quantity/text())[1]', 'VARCHAR(100)' )
    , Home_Job_Profile_Descriptor = c.value('(Home_Job_Profile/@*:Descriptor)[1]', 'VARCHAR(100)' )
FROM @XML.nodes('/Report_Data/Report_Entry') AS t(c);

Output

 ------------ --------------- -------------- ------------------ ------------------ ---------- -------------------------- ------------------ ------------------- ----------------------------- 
| EmployeeId |   FirstName   |   LastName   | Employee_Type_ID | Pay_Rate_Type_ID | Pay_Rate | Cost_Center_Reference_ID |  Reported_Date   | Reported_Quantity | Home_Job_Profile_Descriptor |
 ------------ --------------- -------------- ------------------ ------------------ ---------- -------------------------- ------------------ ------------------- ----------------------------- 
|     111111 | TestFirstName | TestLastName | Regular          | Salary           |        0 | CC0001                   | 2022-04-11-07:00 |                 8 | Test Job Title              |
 ------------ --------------- -------------- ------------------ ------------------ ---------- -------------------------- ------------------ ------------------- -----------------------------