SQL XML parsing different nodes

Question

i would like to ask you for help with parsing XML in SQL, where my XML looks like this, where Load is parrent which can be repeated X-times. I need Column SerNr and for each row need to bound Order name where final table will looks like this

Example of table:

screenshot example of table

<ImageHistory>    
    <Load targets="2" totalTime="417">
    <Orders>
    <Order name="20548976"/>
    </Orders>
    <Data>
    <Disk SerNr="XXXXXX" Size_mb="228936" LoadSuccessfull="true" /> 
    <Disk SerNr="ZZZZZ" Size_mb="228936" LoadSuccessfull="true" /> 
    </Data>
    </Load>
    </ImageHistory>

sql is

with data as (SELECT CAST(MY_XML AS xml) as MY_XML FROM OPENROWSET(BULK 'addres to xml', SINGLE_BLOB) AS T(MY_XML)), 
datainfo as (
SELECT
MY_XML.Blasting.value(' @name', 'BIGINT') as Size_mb,
MY_XML.Blasting.value('@SerNr', 'varchar(32)') as SerNr
FROM data CROSS APPLY MY_XML.nodes('ImageHistory/Load/Data/Disk') AS MY_XML (Blasting))
select * from datainfo

thank you for help

Yitzhak Khabinsky · Accepted Answer · 2019-10-24T20:17:19.580

0

I saved the XML as a file on the file system: e:\Temp\DiskSerialNumbers.xml The rest is below.

SQL

;WITH XmlFile (Contents) AS
(
   SELECT CAST(BulkColumn AS XML) 
   FROM OPENROWSET(BULK 'e:\Temp\DiskSerialNumbers.xml', SINGLE_BLOB) AS XmlData
)
SELECT c.value('(../../Orders/Order/@name)[1]', 'INT') AS [Name]
    , c.value('@SerNr', 'VARCHAR(20)') AS [SerNr]
FROM XmlFile CROSS APPLY Contents.nodes('/ImageHistory/Load/Data/Disk') AS t(c);

Output

+----------+--------+
|   Name   | SerNr  |
+----------+--------+
| 20548976 | XXXXXX |
| 20548976 | ZZZZZ  |
+----------+--------+

edited Oct 24 '19 at 20:17

answered Oct 24 '19 at 20:11

Yitzhak Khabinsky

18,471
2
15
21

Hi Yitzhak, I just added an alternative as backward navigation is a performance killer... – Shnugo Oct 25 '19 at 09:31
Hi Shnugo, Good deal. I am very well aware about the backward XPath navigation as a performance killer. The XML sample provided is so small so I didn't bother to use two CROSS APPLY clauses... – Yitzhak Khabinsky Oct 25 '19 at 13:49

score 0 · Answer 2 · answered Oct 25 '19 at 09:27

You've got your answer already, but I want to point to the fact, that backward navigation (using ../../) is horribly slow with bigger structures.

I'd suggest this:

A mockup to simulate your issue:

DECLARE @yourTable TABLE(ID INT IDENTITY,YourXml XML);
INSERT INTO @yourTable VALUES 
(N'<ImageHistory>
    <Load targets="2" totalTime="417">
    <Orders>
        <Order name="20548976" />
    </Orders>
    <Data>
        <Disk SerNr="XXXXXX" Size_mb="228936" LoadSuccessfull="true" />
        <Disk SerNr="ZZZZZ" Size_mb="228936" LoadSuccessfull="true" />
    </Data>
    </Load>
</ImageHistory>');

--The query

SELECT t.ID
        ,ld.value('@targets','int') AS Load_Targets
        ,ld.value('@totalTime','int') AS Load_TotalTime
        ,ld.value('(Orders/Order/@name)[1]','int') AS Order_Name
        ,dsk.value('@SerNr','nvarchar(100)') AS Disk_SerNr
        ,dsk.value('@Size_mb','nvarchar(100)') AS Disk_Size_mb
        ,dsk.value('@LoadSuccessfull','bit') AS Disk_LoadSuccessfull
FROM @yourTable AS t
CROSS APPLY t.YourXml.nodes('/ImageHistory/Load') A(ld)
CROSS APPLY A.ld.nodes('Data/Disk') B(dsk);

The idea in short:

The first APPLY will dive down to <Load> and will return all <Load> elements (if there are more...

The second APPLY will use the fragment returned by the first APPLY and dive deeper down to <Disk>.

We fetch the order's name (and other values) calling .value() against the first fragment (which is the <Load> element) and we fetch the values of <Disk> calling .value() against the fragment of the second APPLY.

SQL XML parsing different nodes

2 Answers2