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

Edit php.ini with .htaccess

.htaccess php

I'm slowly getting back into PHP, and now I run into a problem, I want to install some web software on our host and I need to have either the latest Zend (which they don't have) or IonCube on the s...