CODEWITHSUNDEEP
sql-serverxml
Mark
Mark

Reputation: 8678

SQL Server : how to parse this xml into table columns?

I am trying to parse below xml into table data. However, I am only getting one row. What do I need to do to get all the data? I have a link to demonstrate this as well.

INSERT BATCHES (BatchID, RawXML)
VALUES (1, '
    <ParamData>
        <moduleRole>
            <moduleId>1</moduleId>
            <bmRoleId>4</bmRoleId>
            <moduleId>2</moduleId>
            <bmRoleId>8</bmRoleId>
            <moduleId>3</moduleId>
            <bmRoleId>255</bmRoleId>
            <moduleId>8</moduleId>
            <bmRoleId>4</bmRoleId>
            <moduleId>16</moduleId>
            <bmRoleId>4</bmRoleId>
            <moduleId>64</moduleId>
            <bmRoleId>4</bmRoleId>
            <moduleId>128</moduleId>
            <bmRoleId>4</bmRoleId>
        </moduleRole>
    </ParamData>
');

SELECT  
    b.BatchID,
    x.XmlCol.value('(moduleId)[1]','INT') AS moduleId,
    x.XmlCol.value('(bmRoleId)[1]','INT') AS bmRoleId
FROM
    Batches b
CROSS APPLY 
    b.RawXml.nodes('/ParamData/moduleRole') x(XmlCol);

Upvotes: 2

Views: 81

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

I'd rather solve this with a numbers table. In my example I'll create one on-the-fly from master..spt_values, which contains about 2.500 entries. This should suffice. We do not need the values, just the return of ROW_NUMBER():

WITH Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nr FROM master..spt_values)
SELECT b.BatchID
     ,b.RawXml.value(N'(/ParamData/moduleRole/moduleId)[sql:column("Nr")][1]','int') AS moduleId
     ,b.RawXml.value(N'(/ParamData/moduleRole/bmRoleId)[sql:column("Nr")][1]','int') AS bmRoleId
FROM Numbers
CROSS JOIN [BATCHES] AS b
WHERE b.RawXml.exist(N'(/ParamData/moduleRole/moduleId)[sql:column("Nr")]')=1;

The result

1   1   4
1   2   8
1   3   255
1   8   4
1   16  4
1   64  4
1   128 4

Hint

It would help - if performance matters - to use TOP before ROW_NUMBER to limit the numbers for the CROSS JOIN to an appropriate count...

Upvotes: 0

har07
har07

Reputation: 89285

I would think to shred the XML on moduleId, then select current element and the nearest following sibling bmRoleId element :

SELECT  b.BatchID,
        x.XmlCol.value('.','INT') AS moduleId,
        x.XmlCol.value('following-sibling::bmRoleId)[1]','INT') AS bmRoleId
FROM    Batches b
CROSS APPLY b.RawXml.nodes('/ParamData/moduleRole/moduleId') x(XmlCol);

Unfortunately, following-sibling axis isn't supported by SQL Server, so we need to use the trick mentioned in this post :

SELECT  b.BatchID,
        x.XmlCol.value('.','INT') AS moduleId,
        x.XmlCol.value('let $c := . return (../bmRoleId[. >> $c])[1]','INT') AS bmRoleId
FROM    Batches b
CROSS APPLY b.RawXml.nodes('/ParamData/moduleRole/moduleId') x(XmlCol);

sqlfiddle demo

This part return (../bmRoleId[. >> $c])[1], get all bmRoleId that located after current moduleId (referenced by $c), and then limit the result to the first of such bmRoleId in document order

Upvotes: 3

Related Questions