SQL中通过XQuery获取XML空标签属性值的正确语法

问题背景 #

我是SQL与XML新手，遇到基础问题未找到答案。现有含空标签的XML数据，需在SQL的SELECT语句中查询这些标签的属性值以生成目标表格。

原始XML结构（已修正拼写错误） #

<Question Id='Q.01' Multiselect='False'>
  <Answers>
    <Answer Id='A.01.01' IsChosen='False'/>
    <Answer Id='A.01.02' IsChosen='True'/>
  </Answers>
</Question>
<Question Id='Q.02' Multiselect='False'>
  <Answers>
    <Answer Id='A.02.01' IsChosen='True'/>
    <Answer Id='A.02.02' IsChosen='False'/>
  </Answers>
</Question>

注：原XML里的Ansewr是拼写错误，必须改成Answer才能被正常解析

期望输出表格 #

我尝试的语句（报错） #

../Questions/Question[@Id="Q.01"]/Answers/Answer[@Id="A.01.01"]/IsChosen').value('.','nvarchar(10)') as A.01.01

解决方案 #

你的问题主要出在XPath属性访问语法错误，还有XML结构的小问题，下面给你详细的修正方案：

核心问题分析 #

1. 属性访问错误：IsChosen是Answer标签的属性，不是子节点，所以不能用/IsChosen，必须用@IsChosen来定位属性。
2. XML根节点缺失：标准XML要求有且只有一个根节点，你给出的XML直接包含多个Question节点，需要先包裹一个根节点（比如<Questions>）才能被SQL正确解析。
3. 列名格式错误：因为别名包含.，SQL会把它当成对象分隔符，所以要用方括号[]把别名括起来。

完整的SQL查询语句 #

假设你的XML数据存储在名为YourTable的表中，XML列名为XmlData，可以用下面的语句：

-- 先给XML添加根节点，确保解析正常
WITH FormattedXml AS (
    SELECT CAST('<Questions>' + XmlData + '</Questions>' AS XML) AS FormattedXml
    FROM YourTable
)
SELECT
    -- 获取Q.01下A.01.01的IsChosen属性
    FormattedXml.value('(/Questions/Question[@Id="Q.01"]/Answers/Answer[@Id="A.01.01"]/@IsChosen)[1]', 'nvarchar(10)') AS [A.01.01],
    -- 获取Q.01下A.01.02的IsChosen属性
    FormattedXml.value('(/Questions/Question[@Id="Q.01"]/Answers/Answer[@Id="A.01.02"]/@IsChosen)[1]', 'nvarchar(10)') AS [A.01.02],
    -- 获取Q.02下A.02.01的IsChosen属性
    FormattedXml.value('(/Questions/Question[@Id="Q.02"]/Answers/Answer[@Id="A.02.01"]/@IsChosen)[1]', 'nvarchar(10)') AS [A.02.01],
    -- 获取Q.02下A.02.02的IsChosen属性
    FormattedXml.value('(/Questions/Question[@Id="Q.02"]/Answers/Answer[@Id="A.02.02"]/@IsChosen)[1]', 'nvarchar(10)') AS [A.02.02]
FROM FormattedXml;

关键细节解释 #

• @IsChosen：用@前缀表示访问标签的属性，这是XPath访问属性的标准语法。
• [1]索引：添加这个索引是为了确保只返回匹配到的第一个节点值，避免因为多个匹配节点导致的SQL报错。
• 根节点包裹：通过CTE（公共表表达式）把原始XML包裹进<Questions>根节点，满足XML的语法规范。
• 别名的方括号：[A.01.01]这种写法告诉SQL这是一个完整的列名，不会把.当成表和列的分隔符。

如果你的XML已经有根节点 #

如果你的原始XML已经包含<Questions>根节点，那可以简化语句，去掉CTE部分：

SELECT
    XmlData.value('(/Questions/Question[@Id="Q.01"]/Answers/Answer[@Id="A.01.01"]/@IsChosen)[1]', 'nvarchar(10)') AS [A.01.01],
    XmlData.value('(/Questions/Question[@Id="Q.01"]/Answers/Answer[@Id="A.01.02"]/@IsChosen)[1]', 'nvarchar(10)') AS [A.01.02],
    XmlData.value('(/Questions/Question[@Id="Q.02"]/Answers/Answer[@Id="A.02.01"]/@IsChosen)[1]', 'nvarchar(10)') AS [A.02.01],
    XmlData.value('(/Questions/Question[@Id="Q.02"]/Answers/Answer[@Id="A.02.02"]/@IsChosen)[1]', 'nvarchar(10)') AS [A.02.02]
FROM YourTable;

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