SQL 从包含 xml 的 nvarchar 列中透视动态列

Question

我得到了一个包含以下列和一些示例数据的表格:

ID    Title   FieldsXml [nvarchar(max)]
--    -----   -------------------------
1     A       <Fields><Field Name="X">x1</Field><Field Name="Y">y1</Field></Fields>
2     B       <Fields><Field Name="Y">y2</Field><Field Name="Z">z2</Field></Fields>
3     C       <Fields><Field Name="Z">z3</Field></Fields>

我需要查询它以获得这样的结果:

ID    Title   X   Y   Z
--    -----   --  --  --
1     A       x1  y1
2     B           y2  z2
3     C               z3

xml 字段应该保证格式正确并且与模式匹配，即使它是 nvarchar 而不是 xml 类型。但是，无法提前知道 Name 属性的值。

我正在使用 SQL Server 2008。如有必要，我可以使用存储过程，但我正在寻找一种可以避免这种情况并避免动态 SQL 的解决方案。写这样的查询是不可能的吗？

如果存储过程或动态 sql 是唯一的方法，我愿意接受使用它的解决方案。

Answer 1

-- Sample data
declare @T table
(
  ID int,
  Title nvarchar(10),
  FieldsXml nvarchar(max)
)
insert into @T values
(1,     'A',       '<Fields><Field Name="X">x1</Field><Field Name="Y">y1</Field></Fields>'),
(2,     'B',       '<Fields><Field Name="Y">y2</Field><Field Name="Z">z2</Field></Fields>'),
(3,     'C',       '<Fields><Field Name="Z">z3</Field></Fields>')

-- Create temp table 
select T.ID,
       T.Title,
       TN.X.value('@Name', 'nvarchar(128)') as FieldName,
       TN.X.value('.', 'nvarchar(max)') as FieldValue
into #tmp       
from @T as T     
  cross apply (select cast(FieldsXml as XML)) as TX(X)
  cross apply TX.X.nodes('/Fields/Field') as TN(X)  


declare @ColList nvarchar(max)
declare @Sql nvarchar(max)

-- Build column list
select @ColList = stuff((select '], ['+FieldName
                         from #tmp
                         group by FieldName
                         for xml path('')), 1, 2, '')+']'

-- Build query
set @Sql = 'select * 
            from (select ID,
                         Title, 
                         FieldName, 
                         FieldValue
                  from #tmp 
                 ) as T
            pivot (min(FieldValue) for FieldName in (' + @ColList + ')) as P'

exec (@Sql)

drop table #tmp