sql - 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 || ...

标签： sqlsql-serverxmltsql

Answer 1

感谢@Shnugo 的回答，我找到了解决方案。

如果 xml-container 列的类型不同于 XML MS-SQL 专用列的类型，则应执行双重 CROSS APPLY。下面的例子：

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)