3

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
Micha
  • 81
  • 1
  • 8

2 Answers2

2

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
Charlieface
  • 52,284
  • 6
  • 19
  • 43
1

Please try the following solution.

Your XPath expressions were way off.

SQL

DECLARE @xml XML =
N'<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 a.value('@id','INT') AS [FeedbackItem]
    , a.value('(message/text())[1]','Varchar(50)') AS [Message]
    , a.value('(sentiment-score/text())[1]','Varchar(50)') AS [SentimentScore]
    , a.value('(channel/text())[1]','Varchar(50)') AS [channel]
    , a.value('(structured-fields/structured-field[name="loyalty_card_number"]/value/text())[1]','Varchar(50)') AS [loyalty_card_number]
    , a.value('(structured-fields/structured-field[name="given_score"]/value/text())[1]','Varchar(50)') AS [GivenScore]
    , b.value('(username/text())[1]','Varchar(50)') AS [Username]
    , b.value('(created/text())[1]','DATE') AS [created]
    , b.value('(content/text())[1]','VARCHAR(100)') AS [content]
FROM @XML.nodes('/feedback-items/feedback-item') t1(a)
    CROSS APPLY t1.a.nodes('notes/note') AS t2(b);

Output

+--------------+-------------+----------------+---------+---------------------+------------+----------+------------+------------------------+
| FeedbackItem |   Message   | SentimentScore | channel | loyalty_card_number | GivenScore | Username |  created   |        content         |
+--------------+-------------+----------------+---------+---------------------+------------+----------+------------+------------------------+
|         1001 | The message |              3 | SMS     |              123456 |          4 | Bob      | 2012-12-11 | The customer was happy |
|         1002 | The message |              3 | SMS     |              123456 |          6 | Mike     | 2012-12-12 | The customer was happy |
+--------------+-------------+----------------+---------+---------------------+------------+----------+------------+------------------------+
miriamka
  • 459
  • 5
  • 9