SQL Server 2019中如何从XML列提取Descriptor属性值？

问题分析 #

你当前查询中Job_Posting_Title_Descriptor返回NULL，原因是wd:Descriptor是<wd:Job_Posting_Title>元素的属性，而非子元素，原查询用了子元素的定位语法wd:Descriptor[1]，自然无法匹配到属性值。

修正后的查询语句 #

将属性的XPath语法改为@wd:Descriptor即可正确获取属性值，修正后的完整SQL如下：

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', '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)
  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                JR01 Program Wholesaler        2021-04-18 07:00:00.000

关键语法说明 #

在SQL Server的XML查询中：

• 定位子元素使用元素名，如wd:ID[1]表示第一个<wd:ID>子元素
• 定位属性使用@属性名，如@wd:Descriptor表示当前元素的wd:Descriptor属性

内容的提问来源于stack exchange，提问作者olorin