Как я могу сделать таблицу SQL из XML-файла, который будет иметь динамическое число узлов?

Я использую SQL Server 2008.
Задача: возьмите XML-файл и разберите его в таблицу SQL(n).
Проблема: количество столбцов и их имена будут отличаться в зависимости от XML.

Вот какой код: 

DECLARE @xmlData XML;  
SET @xmlData = '<root>
  <item id="1">
    <item_number>IT23</item_number>
    <title>Item number twenty-three</title>
    <setting>5 to 20</setting>
    <parameter>10 to 16</parameter>
  </item>
  <item id="2">
    <item_number>RJ12</item_number>
     <title>Another item with a 12</title>
     <setting>7 to 35</setting>
     <parameter>1 to 34</parameter>
  </item>
  <item id="3">
     <item_number>LN90</item_number>
     <title>LN with 90</title>
     <setting>3 to 35</setting>
     <parameter>9 to 50</parameter>
  </item>
</root>'

Например, используя приведенный выше XML, мне понадобится возвращаемая таблица SQL, которая будет выглядеть следующим образом:

моментальный снимок таблицы

Вот как я получил вышеприведенную таблицу: 

   DECLARE @idoc INT;
   EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlData

   SELECT * 
   FROM  OPENXML (@idoc, '/root/item', 2)
   WITH (item_number VARCHAR(100),
         title VARCHAR(100),
         setting VARCHAR(100),
         parameter VARCHAR(100))

Теперь предположим, что XML изменился, где каждый узел элемента имел новый дочерний узел с именем "new_node". Вот так: 

<root>
  <item id="1">
    <item_number>IT23</item_number>
    <title>Item number twenty-three</title>
    <setting>5 to 20</setting>
    <parameter>10 to 16</parameter>
    <new_node>data</new_node>
  </item>
  <item id="2">
    <item_number>RJ12</item_number>
    <title>Another item with a 12</title>
    <setting>7 to 35</setting>
    <parameter>1 to 34</parameter>
    <new_node>goes</new_node>
  </item>
  <item id="3">
    <item_number>LN90</item_number>
    <title>LN with 90</title>
    <setting>3 to 35</setting>
    <parameter>9 to 50</parameter>
    <new_node>here</new_node>
  </item>
</root>

Я должен изменить свой код, чтобы включить новый узел: 

   SELECT * 
   FROM  OPENXML (@idoc, '/root/item', 2)
   WITH (item_number VARCHAR(100),
         title VARCHAR(100),
         setting VARCHAR(100),
         parameter VARCHAR(100),
         new_node VARCHAR(100))

Чтобы получить эту таблицу:

вторая таблица из XML

Таким образом, проблема заключается в том, что дочерние узлы "элемента" будут различаться.

Как я могу генерировать одни и те же таблицы без указания столбцов? Есть ли какой-то другой подход, кроме использования OPENXML?

2 6
xml sql-server openxml

2 ответа:

С динамическим числом столбцов вам нужен динамический SQL.

declare @XML xml = 
'<root>
  <item id="1">
    <item_number>IT23</item_number>
    <title>Item number twenty-three</title>
    <setting>5 to 20</setting>
    <parameter>10 to 16</parameter>
    <new_node>data</new_node>
  </item>
  <item id="2">
    <item_number>RJ12</item_number>
    <title>Another item with a 12</title>
    <setting>7 to 35</setting>
    <parameter>1 to 34</parameter>
    <new_node>goes</new_node>
  </item>
  <item id="3">
    <item_number>LN90</item_number>
    <title>LN with 90</title>
    <setting>3 to 35</setting>
    <parameter>9 to 50</parameter>
    <new_node>here</new_node>
  </item>
</root>'

declare @SQL nvarchar(max) = ''
declare @Col nvarchar(max) = ', T.N.value(''[COLNAME][1]'', ''varchar(100)'') as [COLNAME]' 

select @SQL = @SQL + replace(@Col, '[COLNAME]', T.N.value('local-name(.)', 'sysname'))
from @XML.nodes('/root/item[1]/*') as T(N)

set @SQL = 'select '+stuff(@SQL, 1, 2, '')+' from @XML.nodes(''/root/item'') as T(N)' 

exec sp_executesql @SQL, N'@XML xml', @XML
11

Улучшая ответ Микаэля:

С динамическим числом столбцов вам нужен динамический SQL. Этот код будет динамически создавать инструкцию select, поддерживающую неизвестное число узлов, включая элементы с различными списками узлов.

declare @XML xml = 
'<root>
  <item id="1">
    <item_number>IT23</item_number>
    <title>Item number twenty-three</title>
    <setting>5 to 20</setting>
    <parameter>10 to 16</parameter>
    <new_node>data</new_node>
  </item>
  <item id="2">
    <item_number>RJ12</item_number>
    <title>Another item with a 12</title>
    <setting>7 to 35</setting>
    <parameter>1 to 34</parameter>
    <new_node>goes</new_node>
  </item>
  <item id="3">
    <item_number>LN90</item_number>
    <title>LN with 90</title>
    <setting>3 to 35</setting>
    <parameter>9 to 50</parameter>
    <new_node>here</new_node>
    <unique_node>test</unique_node>
  </item>
</root>'

--build an XML object with the unique list of nodes
DECLARE @xmlcolumns XML;
WITH Xml_CTE AS
(
    SELECT
        CAST('<' + node.value('fn:local-name(.)',
            'varchar(100)') + '>' AS varchar(100) ) 
         + CAST('</' + node.value('fn:local-name(.)',
            'varchar(100)') + '>' AS varchar(100) ) AS name 
    FROM @xml.nodes('/root/item/*') AS roots(node)

)

SELECT @xmlcolumns = (
SELECT CONVERT(XML,name) FROM (
SELECT DISTINCT name 
FROM Xml_CTE
) a
FOR XML PATH(''), ROOT('root')
)



declare @SQL nvarchar(max) = ''
declare @Col nvarchar(max) = ', T.N.value(''[COLNAME][1]'', ''varchar(100)'') as [COLNAME]'  

--use the unique column list xml object to build the select statement
select @SQL = @SQL + replace(@Col, '[COLNAME]', T.N.value('local-name(.)', 'sysname'))
from @XMLcolumns.nodes('/root/*') as T(N) 

--build the entire query statement, using the original XML object as the data source
set @SQL = 'select '+stuff(@SQL, 1, 2, '')+' from @XML.nodes(''/root/item'') as T(N)' 

exec sp_executesql @SQL, N'@XML xml', @XML;
1