Get value of Descriptor from XML

Question

I'm on version Microsoft SQL Server 2019 and have a table that contains a XML column:

--Sample table and data DDL
CREATE TABLE import.WorkdayXMLData (id INT IDENTITY PRIMARY KEY, WorkdayDataXML XML);
INSERT import.WorkdayXMLData (WorkdayDataXML) VALUES
(N'<wd:Report_Data xmlns:wd="urn:com.workday.report/INT02C">
    <wd:Report_Entry>
        <wd:Job_Posting_Title wd:Descriptor="JR01 Program Wholesaler">
            <wd:ID wd:type="WID">ec98bef15bdd01d48e622807e4244918</wd:ID>
            <wd:ID wd:type="Job_Requisition_ID">JR01</wd:ID>
        </wd:Job_Posting_Title>
        <wd:Date_Request_Entered>2021-04-18-07:00</wd:Date_Request_Entered>
    </wd:Report_Entry>
</wd:Report_Data>');

Using the SQL below, I can get the Job_Posting_Title WID and Job_Requisition_ID, as well as the Date_Request_Entered, but I can't seem to figure out how to get the Descriptor value, "JR01 Program Wholesaler." When I run the below code:

WITH XMLNAMESPACES('urn:com.workday.report/INT02C' AS wd)
SELECT
  Tab11.Col.value('wd:ID[1]', 'varchar(max)') as WID,
  Tab11.Col.value('wd:ID[2]', 'varchar(max)') as Job_Requisition_ID,
  Tab11.Col.value('wd:Descriptor[1]', 'varchar(max)') as Job_Posting_Title_Descriptor,
  Tab.Col.value('wd:Date_Request_Entered[1]', 'datetime') as Date_Request_Entered
FROM import.WorkdayXMLData
  CROSS APPLY WorkdayDataXML.nodes('/wd:Report_Data/wd:Report_Entry') Tab(Col) -- My XML file
  OUTER APPLY Tab.Col.nodes('wd:Job_Posting_Title') as Tab11(Col)

This is what I get:

WID                               Job_Requisition_ID    Job_Posting_Title_Descriptor  Date_Request_Entered
--------------------------------- ------------------- ------------------------------ -----------------------
ec98bef15bdd01d48e622807e4244918  JR01                NULL                           2021-04-18 07:00:00.000

And this is the output that I am trying to achieve:

WID                               Job_Requisition_ID  Job_Posting_Title_Descriptor                           Date_Request_Entered
--------------------------------- ------------------- ------------------------------------------------------ -----------------------
ec98bef15bdd01d48e622807e4244918  JR01                JR01 Senior Program Coordinator (Closed)               2021-04-18 07:00:00.000

I've tried a couple different ways, but is there a some special way I'm supposed to reference that Descriptor value here? Thank you for any help.

Answer 1

It is better to declare a DEFAULT namespace. It makes XPath expressions more concise.

Also, it is more reliable to retrieve WID and Job_Requisition_ID based on the ID attribute value instead of its sequential order.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, WorkdayDataXML XML);
INSERT @tbl (WorkdayDataXML) VALUES
(N'<wd:Report_Data xmlns:wd="urn:com.workday.report/INT02C">
    <wd:Report_Entry>
        <wd:Job_Posting_Title wd:Descriptor="JR01 Program Wholesaler">
            <wd:ID wd:type="WID">ec98bef15bdd01d48e622807e4244918</wd:ID>
            <wd:ID wd:type="Job_Requisition_ID">JR01</wd:ID>
        </wd:Job_Posting_Title>
        <wd:Date_Request_Entered>2021-04-18-07:00</wd:Date_Request_Entered>
    </wd:Report_Entry>
</wd:Report_Data>');
-- DDL and sample data population, end

WITH XMLNAMESPACES(DEFAULT 'urn:com.workday.report/INT02C')
SELECT c.value('(Job_Posting_Title/ID[@*:type="WID"]/text())[1]', 'VARCHAR(100)') AS WID
    , c.value('(Job_Posting_Title/ID[@*:type="Job_Requisition_ID"]/text())[1]', 'VARCHAR(100)') AS Job_Requisition_ID
    , c.value('(Job_Posting_Title/@*:Descriptor)[1]', 'VARCHAR(100)') AS Job_Posting_Title_Descriptor
    , c.value('(Date_Request_Entered/text())[1]', 'VARCHAR(100)') AS Date_Request_Entered
FROM @tbl
  CROSS APPLY WorkdayDataXML.nodes('/Report_Data/Report_Entry') AS t(c);

Output

WID	Job_Requisition_ID	Job_Posting_Title_Descriptor	Date_Request_Entered
ec98bef15bdd01d48e622807e4244918	JR01	JR01 Program Wholesaler	2021-04-18-07:00