Parse XML in SQL Server (with an array)

Question

I'm trying to parse some XML in SQL Server, i've developed some code to do it but one of the data items returned come in the form of an array?

SQL So far, with example 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)

Desired output...

Feedback Item	Message	Sentiment Score	Channel	Loyalty Card Number	Given Score	Username	Created	Content
1001	The message	3	SMS	123456	6	Bob	2012-12-11	The Customer was happy
1002	The message	3	SMS	123456	4	Mike	2012-12-12	The Customer was happy

Answer 1

You can use the following code

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

Further Notes:

• The XML was truncated, I've added some more to it to get the desired result. I assume you had more.
• Note how structured-field is filtered on name/text() and then value/text(0 is retrieved
• notes/note looks like it may contain multiple items, you may want to break this out separately with another .nodes