SQL XML 列的元素来分隔列

Question

我有一个带有 XML 列的 SQL 表。我想将 XML 元素分离到 View 中它们自己的列中。

我可以使用 .value 来做到这一点，但我并不总是知道元素名称是什么。例如，在下面的选择中，缺少 c 元素。

create table #temp (Id int, Name varchar(32), taskdata xml)

insert into #temp values
(1, 'Fred','<data><a>Red</a><b>Apple</b></data>'),
(2, 'Mary','<data><a>Blue</a><b>Ball</b></data>'),
(3, 'Paul','<data><a>Green</a><b>Tree</b></data>'),
(4, 'Lisa','<data><a>Yellow</a><b>Hat</b><c>House</c></data>')

select Id
      ,Name
      ,Taskdata.value('(/data/a)[1]', 'nvarchar(max)') AS a
      ,Taskdata.value('(/data/b)[1]', 'nvarchar(max)') AS b
from #temp

drop table #temp

我可以使用以下方法获取所有元素名称的列表:

select distinct T.N.value('local-name(.)','nvarchar(64)') ColNames
from #temp
cross apply Taskdata.nodes('//data/*') as T(N)

但是我不知道如何替换:

Taskdata.value('(/data/a)[1]', 'nvarchar(max)') AS a

为了更动态的东西。

Answer 1

编辑

如果您需要一种完全通用的方法，您可以尝试动态 SQL:

DECLARE @cmd VARCHAR(1000)=
'select Id
      ,Name' + 
      (
      SELECT DISTINCT',Taskdata.value(''(/data/' + TheNode.value('local-name(.)','nvarchar(64)')  + ')[1]'', ''nvarchar(max)'') AS [' + TheNode.value('local-name(.)','nvarchar(64)') + '] '
      FROM #temp AS innerT
      CROSS APPLY innerT.taskdata.nodes('/data/*') AS ThisIs(TheNode)
      FOR XML PATH('')
      )
      +
'from #temp;'
EXEC (@cmd);

EDIT 2 - 用它来创建一个 View

View 无法使用临时表，必须将您的#temp 更改为普通表...

create table temp (Id int, Name varchar(32), taskdata xml)

insert into temp values
(1, 'Fred','<data><a>Red</a><b>Apple</b></data>'),
(2, 'Mary','<data><a>Blue</a><b>Ball</b></data>'),
(3, 'Paul','<data><a>Green</a><b>Tree</b></data>'),
(4, 'Lisa','<data><a>Yellow</a><b>Hat</b><c>House</c></data>')

DECLARE @cmd VARCHAR(1000)=
'CREATE VIEW dbo.SomeName AS select Id
      ,Name' + 
      (
      SELECT DISTINCT',Taskdata.value(''(/data/' + TheNode.value('local-name(.)','nvarchar(64)')  + ')[1]'', ''nvarchar(max)'') AS [' + TheNode.value('local-name(.)','nvarchar(64)') + '] '
      FROM temp AS innerT
      CROSS APPLY innerT.taskdata.nodes('/data/*') AS ThisIs(TheNode)
      FOR XML PATH('')
      )
      +
'from temp;'
EXEC (@cmd);
GO

SELECT * FROM dbo.SomeName;
GO

drop view dbo.SomeName;
drop table temp;

上一个

XML 的问题是:您必须了解结构，至少了解您的数据的一些共同点:是否总有一个根元素“数据”？是否总是有 1:n 个内部元素而没有其他元素？他们的最大数量是多少？如果你有 a 和 c 但没有 b，你怎么知道缺少哪个元素？

这是一种方法:

select Id
      ,Name
      ,Taskdata.value('/data[1]/*[1]', 'nvarchar(max)') AS a
      ,Taskdata.value('/data[1]/*[2]', 'nvarchar(max)') AS b
      ,Taskdata.value('/data[1]/*[3]', 'nvarchar(max)') AS c
from #temp

如果你通过查询知道内部元素的名称，你会得到相同的结果

      ,Taskdata.value('(/data/c)[1]', 'nvarchar(max)') AS c

结果

Id  Name    a       b       c
1   Fred    Red     Apple   NULL
2   Mary    Blue    Ball    NULL
3   Paul    Green   Tree    NULL
4   Lisa    Yellow  Hat     House