How can I create a SQL table from an XML file with a dynamic number of nodes?

Question

I am using SQL Server 2008.
Task: take the XML file and parse it in the (n) SQL table.
Problem: the number of columns and their names will vary depending on the XML.

Here is the code:

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>' 

For example, using the XML above, I will need a returned SQL table that looks like this:

This is how I got the table above:

  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)) 

Now let's say that the XML has changed where each node element has a new node child named "new_node". For instance:

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

I have to change my code to include a new node:

  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)) 

To get this table:

Thus, the problem is that the child nodes of the "item" will change.

How can I generate the same tables without specifying columns? Is there any other approach than using OPENXML?

Answer 1

With a dynamic number of columns, you need dynamic 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) = ', TNvalue(''[COLNAME][1]'', ''varchar(100)'') as [COLNAME]' select @SQL = @SQL + replace(@Col, '[COLNAME]', TNvalue('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 

Answer 2

Michael's answer improves:

With a dynamic number of columns, you need dynamic SQL. This code will dynamically build a select statement that supports an unknown number of nodes, including elements with different node lists.

 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) = ', TNvalue(''[COLNAME][1]'', ''varchar(100)'') as [COLNAME]' --use the unique column list xml object to build the select statement select @SQL = @SQL + replace(@Col, '[COLNAME]', TNvalue('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;