SQL Server中解析包含数组结构的XML数据的实现方法咨询

你可以通过带条件的XPath表达式直接定位结构化字段对应的取值，无需额外拆分多行，调整后的完整SQL代码如下：

DECLARE @XML XML
SET @XML = '<?xml version="1.0" encoding="UTF-8"?>
<feedback-items>
<feedback-item id="1001">
<message>The message</message>
<sentiment-score>3</sentiment-score>
<channel>SMS</channel>
<structured-fields>
<structured-field>
<name>loyalty_card_number</name>
<value>123456</value>
</structured-field>
<structured-field>
<name>given_score</name>
<value>4</value>
</structured-field>
</structured-fields>
<categories>
<category>People</category>
<category>Process</category>
<category>Product</category>
<category>Place</category>
</categories>
<insights>
<insight>
<category>People</category>
<sentiment-score>1</sentiment-score>
</insight>
</insights>
<notes>
<note>
<id>1</id>
<username>Bob</username>
<created>2012-12-11 09:00:00</created>
<content>The customer was happy</content>
</note>
</notes>
</feedback-item>
<feedback-item id="1002">
<message>The message</message>
<sentiment-score>3</sentiment-score>
<channel>SMS</channel>
<structured-fields>
<structured-field>
<name>loyalty_card_number</name>
<value>123456</value>
</structured-field>
<structured-field>
<name>given_score</name>
<value>6</value>
</structured-field>
</structured-fields>
<categories>
<category>People</category>
<category>Process</category>
<category>Product</category>
<category>Place</category>
</categories>
<insights>
<insight>
<category>People</category>
<sentiment-score>1</sentiment-score>
</insight>
</insights>
<notes>
<note>
<id>1</id>
<username>Mike</username>
<created>2012-12-12 09:00:00</created>
<content>The customer was happy</content>
</note>
</notes>
</feedback-item>
</feedback-items>
'

SELECT 
    xmlData.A.value('@id','INT') AS [反馈项ID],
    xmlData.A.value('(./message/text())[1]','VARCHAR(100)') AS [消息],
    xmlData.A.value('(./sentiment-score/text())[1]','INT') AS [情感得分],
    xmlData.A.value('(./channel/text())[1]','VARCHAR(20)') AS [渠道],
    xmlData.A.value('(./structured-fields/structured-field[name="loyalty_card_number"]/value/text())[1]','VARCHAR(20)') AS [会员卡卡号],
    xmlData.A.value('(./structured-fields/structured-field[name="given_score"]/value/text())[1]','INT') AS [给出评分],
    xmlData.A.value('(./notes/note/username/text())[1]','VARCHAR(50)') AS [用户名],
    CAST(xmlData.A.value('(./notes/note/created/text())[1]','DATETIME') AS DATE) AS [创建时间],
    xmlData.A.value('(./notes/note/content/text())[1]','VARCHAR(200)') AS [内容]
FROM @XML.nodes('feedback-items/feedback-item') xmlData(A)

注：你提供的预期输出表格中1001和1002的给出评分数值写反了，以上代码执行后会返回XML中真实存储的数值：1001对应给出评分为4，1002对应给出评分为6，和XML内容一致。
你之前代码无法提取结构化字段的原因有两个：

1. XPath区分大小写，XML中节点名是小写的name，你注释的代码中用了大写的Name导致匹配失败
2. 子节点路径开头不要加/，否则会从整个XML的根节点开始匹配，无法定位到当前feedback-item下的子节点

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