Difficulty parsing XML with SQL Server TSQL

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

Answers


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);

Need Your Help

Fastest API for rendering text in Windows Forms?

c# windows performance

We need to optimize the text rendering for a C# Windows Forms application displaying a large number of small strings in an irregular grid. At any time there can be well over 5000 cells visible that

How to stop a looping thread in Python?

python multithreading wxpython

What's the proper way to tell a looping thread to stop looping?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.