SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @XML 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
  [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)

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