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

前端之家收集整理的这篇文章主要介绍了sql-server – 如何从具有动态节点数的XML文件创建SQL表?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我正在使用sql Server 2008.
任务:获取 XML文件并将其解析为(n)sql表.
问题:列数及其名称将根据XML而有所不同.

这是一些代码

  1. DECLARE @xmlData XML;
  2. SET @xmlData = '<root>
  3. <item id="1">
  4. <item_number>IT23</item_number>
  5. <title>Item number twenty-three</title>
  6. <setting>5 to 20</setting>
  7. <parameter>10 to 16</parameter>
  8. </item>
  9. <item id="2">
  10. <item_number>RJ12</item_number>
  11. <title>Another item with a 12</title>
  12. <setting>7 to 35</setting>
  13. <parameter>1 to 34</parameter>
  14. </item>
  15. <item id="3">
  16. <item_number>LN90</item_number>
  17. <title>LN with 90</title>
  18. <setting>3 to 35</setting>
  19. <parameter>9 to 50</parameter>
  20. </item>
  21. </root>'

例如,使用上面的XML,我需要返回一个如下所示的sql表:

以下是我如何得到上表:

  1. DECLARE @idoc INT;
  2. EXEC sp_xml_preparedocument @idoc OUTPUT,@xmlData
  3.  
  4. SELECT *
  5. FROM OPENXML (@idoc,'/root/item',2)
  6. WITH (item_number VARCHAR(100),title VARCHAR(100),setting VARCHAR(100),parameter VARCHAR(100))

现在让我们说XML改变了每个项节点都有一个名为’new_node’的新子节点.像这样:

  1. <root>
  2. <item id="1">
  3. <item_number>IT23</item_number>
  4. <title>Item number twenty-three</title>
  5. <setting>5 to 20</setting>
  6. <parameter>10 to 16</parameter>
  7. <new_node>data</new_node>
  8. </item>
  9. <item id="2">
  10. <item_number>RJ12</item_number>
  11. <title>Another item with a 12</title>
  12. <setting>7 to 35</setting>
  13. <parameter>1 to 34</parameter>
  14. <new_node>goes</new_node>
  15. </item>
  16. <item id="3">
  17. <item_number>LN90</item_number>
  18. <title>LN with 90</title>
  19. <setting>3 to 35</setting>
  20. <parameter>9 to 50</parameter>
  21. <new_node>here</new_node>
  22. </item>
  23. </root>

我必须更改我的代码以包含新节点:

  1. SELECT *
  2. FROM OPENXML (@idoc,parameter VARCHAR(100),new_node VARCHAR(100))

要获得此表:

所以问题是’item’的子节点会有所不同.

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

解决方法

使用动态数量的列,您需要动态sql.
  1. declare @XML xml =
  2. '<root>
  3. <item id="1">
  4. <item_number>IT23</item_number>
  5. <title>Item number twenty-three</title>
  6. <setting>5 to 20</setting>
  7. <parameter>10 to 16</parameter>
  8. <new_node>data</new_node>
  9. </item>
  10. <item id="2">
  11. <item_number>RJ12</item_number>
  12. <title>Another item with a 12</title>
  13. <setting>7 to 35</setting>
  14. <parameter>1 to 34</parameter>
  15. <new_node>goes</new_node>
  16. </item>
  17. <item id="3">
  18. <item_number>LN90</item_number>
  19. <title>LN with 90</title>
  20. <setting>3 to 35</setting>
  21. <parameter>9 to 50</parameter>
  22. <new_node>here</new_node>
  23. </item>
  24. </root>'
  25.  
  26. declare @sql nvarchar(max) = ''
  27. declare @Col nvarchar(max) = ',T.N.value(''[COLNAME][1]'',''varchar(100)'') as [COLNAME]'
  28.  
  29. select @sql = @sql + replace(@Col,'[COLNAME]',T.N.value('local-name(.)','sysname'))
  30. from @XML.nodes('/root/item[1]/*') as T(N)
  31.  
  32. set @sql = 'select '+stuff(@sql,1,2,'')+' from @XML.nodes(''/root/item'') as T(N)'
  33.  
  34. exec sp_executesql @sql,N'@XML xml',@XML

猜你在找的MsSQL相关文章