我正在使用sql Server 2008.
任务:获取 XML文件并将其解析为(n)sql表.
问题:列数及其名称将根据XML而有所不同.
任务:获取 XML文件并将其解析为(n)sql表.
问题:列数及其名称将根据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,parameter VARCHAR(100),new_node VARCHAR(100))
要获得此表:
所以问题是’item’的子节点会有所不同.
解决方法
使用动态数量的列,您需要动态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