How to read data from multiple XML files in SQL Server?

Question

Background :

I want to obtain data from multiple XML files (stored in database) and fetch them into one result set. The basic working solution, with single XML file looks similar to this one :

DECLARE @xml xml
SET @xml = 
(SELECT TOP 1 convert(varchar(max), convert(varbinary(max), [XML_FILE]))
  FROM [SOME_TABLE])
SELECT
    b.value('(./SomeNode/text())[1]','nvarchar(100)')) as [Some_Text],
    b.value('(./SomeOtherNode/@VAL)[1]','int')) as [Some_Val]
FROM @xml.nodes('Example/File') as a(b)

Obviously this won't work with SELECT that returns many rows (many XML files). Sub-optimal solution could be achieved using cursor (iterating over collection -> pushing data into temporary table -> SELECT (*) FROM temporary_table) however, I believe thats not necessary and more straightforward solution can be achieved.

Question :

How to fetch data from multiple XML files, obtained via SELECT query, into a single result-set, without using cursor?

FILE_NAME ||   Value 1   ||   Value 2  || ...
----------------------------------------------
XML_FILE_1 || Node1Value || Node2Value || ...
XML_FILE_2 || Node1Value || Node2Value || ...

CROSS APPLY is your friend.

Jeroen Mostert
– Jeroen Mostert

2019-01-14 14:44:08 +00:00
Commented Jan 14, 2019 at 14:44 — Jeroen Mostert
– Jeroen Mostert, Commented Jan 14, 2019 at 14:44

baka1408 · Accepted Answer · 2019-01-14 17:55:59Z

3

I've found solution thanks to @Shnugo answer.

If the type of xml-container column is different then XML MS-SQL dedicated one, then double CROSS APPLY should be performed. Example below :

DECLARE @mockup TABLE(ID INT IDENTITY, [XML_DATA] VARBINARY(MAX));
INSERT INTO @mockup VALUES('<Example><File><SomeNode>blah</SomeNode><SomeOtherNode VAL="1"/></File></Example>')
                         ,('<Example><File><SomeNode>blub</SomeNode><SomeOtherNode VAL="2"/></File></Example>')

SELECT
    ID,
    b.value('(SomeNode/text())[1]','nvarchar(100)') as [Some_Text],
    b.value('(SomeOtherNode/@VAL)[1]','int') as [Some_Val]
FROM @mockup
CROSS APPLY (SELECT CAST(convert(varbinary(max), [XML_DATA]) as XML)) as RAW_XML(xml_field)
CROSS APPLY RAW_XML.xml_field.nodes('Example/File') as a(b)

answered Jan 14, 2019 at 17:55

baka1408

4336 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gottfried Lesigang Over a year ago

Great, +1 from my side. This second cross apply is a good choice. A CTE would work too and so would a sub-query... (y)

Gottfried Lesigang · Accepted Answer · 2019-01-14 16:11:24Z

2

For sure the CURSOR approach is not needed and would be wrong entirely...

The general approach should be something like this:

SELECT
    b.value('(./SomeNode/text())[1]','nvarchar(100)') as [Some_Text],
    b.value('(./SomeOtherNode/@VAL)[1]','int') as [Some_Val]
FROM [SOME_TABLE]
CROSS APPLY [XML_FILE].nodes('Example/File') as a(b);

But there are questions open:

Speaking about xml files is a bit bewildering... I hope to get this correctly, that all these XMLs are living in a table's column.
If the first is true: Are all these XMLs of the same structure? if not you will need some kind of filtering.
is the XML in your table's column a native XML-type already? Your example uses CONVERT extensivly... You will need a native XML in order to use .nodes()
If there's no native XML: Do you have to deal with invalid / uncastable data?
Are there rows with no data but you want to see them anyway? In this case you can try OUTER APPLY instead of CROSS APPLY.

For demonstration a running stand-alone mockup:

DECLARE @mockup TABLE(ID INT IDENTITY, [XML_FILE] XML);
INSERT INTO @mockup VALUES('<Example><File><SomeNode>blah</SomeNode><SomeOtherNode VAL="1"/></File></Example>')
                         ,('<Example><File><SomeNode>blub</SomeNode><SomeOtherNode VAL="2"/></File></Example>')

SELECT
    ID,
    b.value('(SomeNode/text())[1]','nvarchar(100)') as [Some_Text],
    b.value('(SomeOtherNode/@VAL)[1]','int') as [Some_Val]
FROM @mockup
CROSS APPLY [XML_FILE].nodes('Example/File') as a(b)

answered Jan 14, 2019 at 16:11

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

2 Comments

baka1408 Over a year ago

Yes, the problem is that [XML_FILE] is an IMAGE type (don't ask me why, I'm just a user of this database), so [XML_FILE].nodes() won't work. However, ss far as I know, data should be 'stable' in a manner of casting. Could you propose any solution with CAST/CONVERT that should do the trick?

Gottfried Lesigang Over a year ago

@baka1408 wheee, image is a walking dead, deprecated for centuries... I'd try to change this or at least maintain an additional typed column...

Collectives™ on Stack Overflow

How to read data from multiple XML files in SQL Server?

Background :

Question :

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Background :

Question :

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related