sql-server - 在 SQL Server 中解析 XML(使用数组)

Question

我正在尝试在 SQL Server 中解析一些 XML，我已经开发了一些代码来执行此操作，但是返回的数据项之一以数组的形式出现？

SQL 到目前为止，示例 XML...

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 [FeedbackItem]
    --fields.A.value('./Name/text())[1]','Varchar(50)') AS [Name]
    --xmlData.A.value('(./structured-fields/structured-field/Name/text())[1]','Varchar(50)') AS [Name]
FROM @XML.nodes('feedback-items/feedback-item') xmlData(A)
        --CROSS APPLY xmlData.A.nodes('/structured-fields/structured-field') AS fields(A)

期望的输出...

<表类="s-表"><头>反馈项目留言情绪得分 channel 成员(member)卡号给定分数用户名创建内容<正文>1001消息3短信1234566鲍勃2012-12-11客户很高兴1002消息3短信1234564迈克2012-12-12客户很高兴

Answer 1

你可以使用下面的代码

SELECT
  [Feedback Item] = fb.value('@id','int'),
  Message = fb.value('(message/text())[1]','nvarchar(200)'),
  [Sentiment Score] = fb.value('(sentiment-score/text())[1]','int'),
  Channel = fb.value('(channel/text())[1]','nvarchar(200)'),
  [Loyalty Card Number] = fb.value('(structured-fields/structured-field[name[text()="loyalty_card_number"]]/value/text())[1]','nvarchar(200)'),
  [Given Score] = fb.value('(structured-fields/structured-field[name[text()="given_score"]]/value/text())[1]','nvarchar(200)'),
  Username = fb.value('(notes/note/username/text())[1]','nvarchar(200)'),
  Created = fb.value('(notes/note/created/text())[1]','datetime'),
  Content = fb.value('(notes/note/content/text())[1]','nvarchar(200)')
FROM @xml.nodes('feedback-items/feedback-item') x(fb)

db<>fiddle

进一步说明:

• XML 被截断了，我向其中添加了一些以获得所需的结果。我想你有更多。
• 注意 structured-field 是如何在 name/text() 上过滤的，然后 value/text(0 被检索
• notes/note 看起来它可能包含多个项目，您可能希望将其与另一个 .nodes
分开