sql-server – 如何从具有动态节点数的XML文件创建SQL表？

这是一些代码：

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’的子节点会有所不同.

如何在不指定列的情况下生成相同的表？除了必须使用OPENXML之外还有其他方法吗？

解决方法

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!