Difficulty parsing XML with SQL Server TSQL

Question

I'm having some difficulties in parsing some XML into the table format that I need. Here is an example of the xml I will be receiving

<p>8abbdcf1-eff2-4b26-b905-343298338954</p>
<d>
    <id>172fcf08-79d6-467a-a40e-362583683680</id>
    <id>2149987c-1311-4ad0-b8bb-362477435274</id>
</d>

There will always be 1 element in "p" and there can be any number of IDs in "d".

I want the results to apply "p" to every id in "d". I've looked around and tried to understand the way xml parsing works but have been having some difficulties. My results are usually either 1 record with a single ID or 2 records (the number of elements in "d") but the value returned is null. As I've not managed to get the list of IDs yet I have not moved on to applying P to this.

Below is my most recent effort:

SET @InputXML = 
    '
    <p>8abbdcf1-eff2-4b26-b905-343298338954</p>
    <d>
        <id>172fcf08-79d6-467a-a40e-362583683680</id>
        <id>2149987c-1311-4ad0-b8bb-362477435274</id>
    </d>
    '
SELECT list.d.value('id[1]','nvarchar(100)') AS ID
FROM @InputXML.nodes('/d') AS list(d)

Hoped for result would be:

p                                     |ID
8abbdcf1-eff2-4b26-b905-343298338954  |172fcf08-79d6-467a-a40e-362583683680
8abbdcf1-eff2-4b26-b905-343298338954  |2149987c-1311-4ad0-b8bb-362477435274

can you edit the question with the expected result in table format — Pரதீப்
– Pரதீப், Commented Jan 20, 2015 at 10:40
will you consider not to parse XML with TSQL ? it'll be easier if you work with server-side languages, such as JSP, ASP.NET or PHP — Raptor
– Raptor, Commented Jan 20, 2015 at 10:43
Raptor, no it's not currently possible to do it another way. however I may be able to influence a change in the xml format if that could help. — Andrew
– Andrew, Commented Jan 20, 2015 at 10:46

GarethD · Accepted Answer · 2015-01-20 10:59:59Z

1

You need to add /id to your nodes function in order to get a new row for each id, then you can use .value('.','nvarchar(100)') to get the inner text of each id node. This appears to work as expected:

DECLARE @InputXML XML = 
    '<p>8abbdcf1-eff2-4b26-b905-343298338954</p>
    <d>
        <id>172fcf08-79d6-467a-a40e-362583683680</id>
        <id>2149987c-1311-4ad0-b8bb-362477435274</id>
    </d>';

SELECT  p = @InputXML.value('p[1]', 'nvarchar(100)'),
        id = list.d.value('.','nvarchar(100)')
FROM    @InputXML.nodes('d/id') AS list(d);

answered Jan 20, 2015 at 10:59

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Over a year ago

Hi Gareth, that works thank you very much. I had /id in the nodes function at one point but at that point i was returning nulls for each row. it was the '.' part that i did not know about. Thank you again

Collectives™ on Stack Overflow

Difficulty parsing XML with SQL Server TSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related