SQL 将 XML 转换为表

Question

在SQL 2005中，有没有办法将以下xml转换为表？

<root>
  <r>
    <data>"col1"</data>
    <data>"col2"</data>
    <data>"col3"</data>
  </r>
  <r>
    <data>"data1"</data>
    <data>""</data>
    <data>"data3"</data>
  </r>
  <r>
    <data>"data"</data>
    <data>"data"</data>
    <data>"data"</data>
  </r>
</root>

我希望输出为

col1 col2 col3
----------------
data      data3
data data data

xml 可以有不同数量的列，因此解决方案需要考虑到这一点。

提前致谢。

Answer 1

declare @xml xml
set @xml = 
'<root>
  <r>
    <data>"col1"</data>
    <data>"col2"</data>
    <data>"col3"</data>
  </r>
  <r>
    <data>"data1"</data>
    <data>""</data>
    <data>"data3"</data>
  </r>
  <r>
    <data>"data"</data>
    <data>"data"</data>
    <data>"data"</data>
  </r>
</root>'

declare @SQL nvarchar(max)
set @SQL = ''

select @SQL = @SQL + ',replace(r.r.value(''data['+
         cast(T.rn as nvarchar(10))+
         ']'', ''varchar(10)''), ''"'','''') as '+
         quotename(replace(T.ColName, '"', '')) 
from
(
  select
    r.r.value('.', 'sysname') as ColName,
    row_number() over(order by (select 1)) as rn
  from @xml.nodes('/root/r[1]/data') r(r)
) as T

set @SQL = 'select '+stuff(@SQL, 1, 1, '')+
        ' from @x.nodes(''/root/r[position()>1]'') r(r)'

exec sp_executesql @SQL, N'@x xml', @x = @xml

由于我在这里使用动态 SQL，因此建议阅读动态 SQL 的诅咒和祝福 。

对正在发生的事情的解释。

此查询用于从第一个 r 节点获取列名称：

select
    r.r.value('.', 'varchar(10)') as ColName,
    row_number() over(order by (select 1)) as rn
  from @xml.nodes('/root/r[1]/data') r(r)

/root/r[1] 确保我们获取第一行。 row_number() 枚举在数字和列名之间建立连接的列。

@SQL 中的结果查询如下：

select 
  replace(r.r.value('data[1]', 'varchar(10)'), '"','') as [col1],
  replace(r.r.value('data[2]', 'varchar(10)'), '"','') as [col2],
  replace(r.r.value('data[3]', 'varchar(10)'), '"','') as [col3] 
from @xml.nodes('/root/r[position()>1]') r(r)

/root/r[position()>1] 获取除第一个节点之外的所有 r 节点。 data[1] 中的 1 来自 row_number()，[col1] 来自相应的列名称。 quotename() 将方括号 [] 添加到列别名中。如果没有 quotename()，此查询可用于 SQL 注入。 replace() 用于从字符串中删除 "。它将删除所有出现的 "，所以如果您期望 " 作为值的一部分，您可以使用 substring() 来删除 "。

我使用 varchar(10) 作为列数据的大小。您应该将其修改为您需要的任何内容。

Answer 2

列数不可变：SQL 通常是固定列

但是，您可以在某种程度上预见到这一点。

DECLARE @foo AS xml = '<root>
  <r>
    <data>"col1"</data>
    <data>"col2"</data>
    <data>"col3"</data>
  </r>
  <r>
    <data>"data1"</data>
    <data>""</data>
    <data>"data3"</data>
  </r>
  <r>
    <data>"data"</data>
    <data>"data"</data>
    <data>"data"</data>
  </r>
</root>'

SELECT
   REPLACE(x.item.value('(data)[1]', 'varchar(100)'), '"', '') AS col1,
   REPLACE(x.item.value('(data)[2]', 'varchar(100)'), '"', '') AS col2,
   REPLACE(x.item.value('(data)[3]', 'varchar(100)'), '"', '') AS col3,
   REPLACE(x.item.value('(data)[4]', 'varchar(100)'), '"', '') AS col4,
   REPLACE(x.item.value('(data)[5]', 'varchar(100)'), '"', '') AS col5,
   REPLACE(x.item.value('(data)[6]', 'varchar(100)'), '"', '') AS col6,
   REPLACE(x.item.value('(data)[7]', 'varchar(100)'), '"', '') AS col7,
   REPLACE(x.item.value('(data)[8]', 'varchar(100)'), '"', '') AS col8,
   REPLACE(x.item.value('(data)[9]', 'varchar(100)'), '"', '') AS col9,
   REPLACE(x.item.value('(data)[10]', 'varchar(100)'), '"', '') AS col10
FROM
   @foo.nodes('/root/r') x(item)

默认情况下也无法保证节点顺序评估，这使得提取第一行列名称变得复杂。

基于这个答案：http://stackoverflow.com/q/1134075/27535，你可以使用这个SQL来识别第1行

;WITH n(i) AS (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9),
     o(i) AS (SELECT n3.i * 100 + n2.i * 10 + n1.i FROM n n1, n n2, n n3)
SELECT
   REPLACE(x.item.value('(data)[1]', 'varchar(100)'), '"', '') AS col1,
   REPLACE(x.item.value('(data)[2]', 'varchar(100)'), '"', '') AS col2,
   REPLACE(x.item.value('(data)[3]', 'varchar(100)'), '"', '') AS col3,
   REPLACE(x.item.value('(data)[4]', 'varchar(100)'), '"', '') AS col4,
   REPLACE(x.item.value('(data)[5]', 'varchar(100)'), '"', '') AS col5,
   REPLACE(x.item.value('(data)[6]', 'varchar(100)'), '"', '') AS col6,
   REPLACE(x.item.value('(data)[7]', 'varchar(100)'), '"', '') AS col7,
   REPLACE(x.item.value('(data)[8]', 'varchar(100)'), '"', '') AS col8,
   REPLACE(x.item.value('(data)[9]', 'varchar(100)'), '"', '') AS col9,
   REPLACE(x.item.value('(data)[10]', 'varchar(100)'), '"', '') AS col10,
   o.i
FROM
   o
   CROSS APPLY
   @foo.nodes('/root/r[sql:column("o.i")]') x(item)

Answer 3

我从来没有这样做过，但我知道它可以做到......

看看这个教程，它似乎非常接近你所要求的。
http://weblogs.sqlteam.com/mladenp/archive/2007 /06/18/60235.aspx