将XML导入SQL Server

前端之家收集整理的这篇文章主要介绍了将XML导入SQL Server前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我可以找到很多关于如何将某些类型的XML数据导入sql Server 2005的示例.但是我已经获得了以下格式的数据(重复“行”和“单元格”,而不是标记名称等等:
  1. <?xml version="1.0"?> <rows>
  2. <row id='1'>
  3. <cell id='category'>Simple</cell>
  4. <cell id='query'>summary</cell>
  5. <cell id='clientsfound'>6</cell>
  6. <cell id='eligibleclients'>11</cell>
  7. <cell id='percentage'>55</cell>
  8. <cell id='days'>0</cell>
  9. </row>
  10.  
  11. <row id='2'>
  12. <cell id='category'>Complex</cell>
  13. <cell id='query'>details</cell>
  14. <cell id='clientsfound'>4</cell>
  15. <cell id='eligibleclients'>6</cell>
  16. <cell id='percentage'>67</cell>
  17. <cell id='days'>5</cell>
  18. </row>
  19.  
  20. ...
  21. </rows>

理想情况下,我想将其加载到表中,例如:

  1. CREATE TABLE [dbo].[QueryResults](
  2. [UserString] [varchar](50) NULL,[ImportStamp] [timestamp] NULL,[RowID] [int] NULL,[Category] [nchar](10) NULL,[Query] [nchar](10) NULL,[ClientsFound] [int] NULL,[EligibleClients] [int] NULL,[Percentage] [int] NULL,[Days] [int] NULL
  3. )

有人能为我提供一个示例或指向在线教程吗?

xml应该“”不是’内部,不是?

无论如何,您可以本机解析XML数据类型.
由于内存使用量的开销,sp_xml_preparedocument非常危险.

  1. DECLARE @foo XML;
  2.  
  3. SET @foo = N'<?xml version="1.0"?>
  4. <rows>
  5. <row id="1">
  6. <cell id="category">Simple</cell>
  7. <cell id="query">summary</cell>
  8. <cell id="clientsfound">6</cell>
  9. <cell id="eligibleclients">11</cell>
  10. <cell id="percentage">55</cell>
  11. <cell id="days">0</cell>
  12. </row>
  13. <row id="2">
  14. <cell id="category">Complex</cell>
  15. <cell id="query">details</cell>
  16. <cell id="clientsfound">4</cell>
  17. <cell id="eligibleclients">6</cell>
  18. <cell id="percentage">67</cell>
  19. <cell id="days">5</cell>
  20. </row>
  21. </rows>';
  22.  
  23. SELECT
  24. x.item.value('@id','int') AS RowID,y.item.value('(./cell[@id="category"])[1]','nchar(10)') AS category,y.item.value('(./cell[@id="query"])[1]','nchar(10)') AS query,y.item.value('(./cell[@id="clientsfound"])[1]','int') AS clientsfound,y.item.value('(./cell[@id="eligibleclients"])[1]','int') AS eligibleclients,y.item.value('(./cell[@id="percentage"])[1]','int') AS percentage,y.item.value('(./cell[@id="days"])[1]','int') AS days
  25. FROM
  26. @foo.nodes('/rows/row') x(item)
  27. CROSS APPLY
  28. x.item.nodes('.') AS y(item)

猜你在找的XML相关文章