Select multiple XML childnodes as separate results

Question

I need to select data from an XML document. The document is structured as in the following example:

<parameters>
    <set>
        <attribId>4711</attribId>
        <attribId>4712</attribId>
        <matnr>000000000001206433</matnr>
        <vkorg>2420</vkorg>
        <spras>NL</spras>
    </set>
    <set>
        <attribId>4750</attribId>
        <matnr>000000000001007885</matnr>
        <matnr>000000000001007886</matnr>
        <vkorg>2420</vkorg>
        <spras>NL</spras>
    </set>
</parameters>

The resultset should look like the following

attribId    matnr               vkorg   spras
4711        000000000001206433  2420    NL
4712        000000000001206433  2420    NL
4750        000000000001007885  2420    NL
4750        000000000001007886  2420    NL

As you can see the nodes attribId and matnr may occure several times. Currently I tried to select the following way:

SELECT
  c.value('attribId[1]', 'int')            AS attribId,
  c.value('vkorg[1]', 'char(4)')           AS VKORG,
  c.value('spras[1]', 'char(2)')           AS SPRAS,
  c.value('matnr[1]', 'nvarchar(18)')      AS MATNR
FROM @parameters.nodes('/parameters/set')      AS t(c); 

In this select statement the variable @parameters holds the XML document. The result of this is obviously:

attribId    matnr               vkorg   spras
4711        000000000001206433  2420    NL
4750        000000000001007885  2420    NL

The additional result rows are missing. How would I select to get the first described result set? Thanks in advance.

Answer 1

You could try this:

First select all elements into separate tables (I am just using inline tables) then join them all together based on the set id. In this case I am inferring a set id as it is not specified in your xml.

I am also assuming that there will be at least one value of each column in each set - therefore I am using an inner join

DECLARE @parameters xml = '<parameters>
    <set>
        <attribId>4711</attribId>
        <attribId>4712</attribId>
        <matnr>000000000001206433</matnr>
        <vkorg>2420</vkorg>
        <spras>NL</spras>
    </set>
    <set>
        <attribId>4750</attribId>
        <matnr>000000000001007885</matnr>
        <matnr>000000000001007886</matnr>
        <vkorg>2420</vkorg>
        <spras>NL</spras>
    </set>
</parameters>'

select 
    attribId,
    matnr,
    vkorg,
    spras
FROM (  
        select  
            t.c.value('count(for $a in . return $a/../../*[. << $a])','int') as parentID,
            c.value('.', 'int') AS attribId
        FROM @parameters.nodes('/parameters/set/attribId')      AS t(c)
    ) a
INNER JOIN (
        select  
            t.c.value('count(for $a in . return $a/../../*[. << $a])','int') as parentID,
            c.value('.', 'char(4)') AS vkorg
        FROM @parameters.nodes('/parameters/set/vkorg')      AS t(c)
    ) v ON a.parentID = v.parentID
INNER JOIN (
        select  
            t.c.value('count(for $a in . return $a/../../*[. << $a])','int') as parentID,
            c.value('.', 'char(2)') AS spras
        FROM @parameters.nodes('/parameters/set/spras')      AS t(c)
    ) s ON s.parentID = a.parentID
 INNER JOIN (
     select  
        t.c.value('count(for $a in . return $a/../../*[. << $a])','int') as parentID,
        c.value('.', 'nvarchar(18)') AS matnr
    FROM @parameters.nodes('/parameters/set/matnr')      AS t(c)
 ) m ON m.parentID = a.parentID

If there was an id on the set one could do this:

DECLARE @parameters xml = '<parameters>
    <set id="1">
        <attribId>4711</attribId>
        <attribId>4712</attribId>
        <matnr>000000000001206433</matnr>
        <vkorg>2420</vkorg>
        <spras>NL</spras>
    </set>
    <set id="2">
        <attribId>4750</attribId>
        <matnr>000000000001007885</matnr>
        <matnr>000000000001007886</matnr>
        <vkorg>2420</vkorg>
        <spras>NL</spras>
    </set>  
</parameters>'


select 
    attribId,
    matnr,
    vkorg,
    spras
FROM (  
        select  
            c.value('./../@id','varchar(200)') as parentID,
            c.value('.', 'int') AS attribId
        FROM @parameters.nodes('/parameters/set/attribId')      AS t(c)
    ) a
INNER JOIN (
        select  
            c.value('./../@id','varchar(200)') as parentID,
            c.value('.', 'char(4)') AS vkorg
        FROM @parameters.nodes('/parameters/set/vkorg')      AS t(c)
    ) v ON a.parentID = v.parentID
INNER JOIN (
        select  
            c.value('./../@id','varchar(200)') as parentID,
            c.value('.', 'char(2)') AS spras
        FROM @parameters.nodes('/parameters/set/spras')      AS t(c)
    ) s ON s.parentID = a.parentID
 INNER JOIN (
     select  
        c.value('./../@id','varchar(200)') as parentID,
        c.value('.', 'nvarchar(18)') AS matnr
    FROM @parameters.nodes('/parameters/set/matnr')      AS t(c)
 ) m ON m.parentID = a.parentID

Answer 2

This should give you what you're looking for:

DECLARE @xml XML = '<parameters>
    <set>
        <attribId>4711</attribId>
        <attribId>4712</attribId>
        <matnr>000000000001206433</matnr>
        <vkorg>2420</vkorg>
        <spras>NL</spras>
    </set>
    <set>
        <attribId>4750</attribId>
        <matnr>000000000001007885</matnr>
        <matnr>000000000001007886</matnr>
        <vkorg>2420</vkorg>
        <spras>NL</spras>
    </set>
</parameters>'

SELECT
    attribId_node.value('(./text())[1]', 'varchar(50)') AS attribId,
    matnr_node.value('(./text())[1]', 'varchar(50)') AS matnr,
    vkorg_node.value('(./text())[1]', 'varchar(50)') AS vkorg,
    spras_node.value('(./text())[1]', 'varchar(50)') AS spras
FROM @xml.nodes('/parameters/set') AS s (set_node)
CROSS APPLY s.set_node.nodes('./attribId') AS a (attribId_node)
CROSS APPLY s.set_node.nodes('./matnr') AS b (matnr_node)
CROSS APPLY s.set_node.nodes('./vkorg') AS c (vkorg_node)
CROSS APPLY s.set_node.nodes('./spras') AS d (spras_node)