从 XML 中获取描述符的值-解网

问：

我使用的是 Microsoft SQL Server 2019 版本，并且有一个包含 XML 列的表：

--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>');

使用下面的 SQL，我可以获得 WID 和 Job_Requisition_ID Job_Posting_Title，以及Date_Request_Entered，但我似乎无法弄清楚如何获取描述符值“JR01 Program Wholesaler”。当我运行以下代码时：

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)

这是我得到的：

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

这是我试图实现的输出：

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

我已经尝试了几种不同的方法，但是我应该在这里引用该描述符值吗？感谢您的任何帮助。

sql-server xml

在提出问题时，您需要提供一个最小的可重现示例：（1） DDL 和示例数据填充，即 CREATE 表和 INSERT T-SQL 语句。（2）需要执行的操作，即逻辑和代码尝试在 T-SQL 中实现它。（3）期望的输出，基于上面 #1 中的示例数据。（4）您的 SQL Server 版本（SELECT @@version;）。问题中的所有内容都是文本，没有图像。

答：

1赞 Yitzhak Khabinsky 11/1/2023 #1

最好声明一个 DEFAULT 命名空间。它使 XPath 表达式更加简洁。

此外，根据 ID 属性值而不是其顺序检索 WID 和 Job_Requisition_ID 更可靠。

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);

输出

WID的	Job_Requisition_ID	Job_Posting_Title_Descriptor	Date_Request_Entered
EC98BEF15BDD01D48E622807E4244918	JR01型	JR01 程序批发商	2021-04-18-07:00

从 XML 中获取描述符的值

Get value of Descriptor from XML

评论

评论