时间戳

首页 > 解决方案 > 在 T-SQL SQL-SERVER 中将单个 XML 括号列转换为多列

sql - 在 T-SQL SQL-SERVER 中将单个 XML 括号列转换为多列

问题描述

我见过与我类似的问题，但 XML 格式一直不同，因为我拥有的 XML 格式不遵循“标准”结构。我的表如下所示（以 XML 括号作为行值的单列）：

|VAL|
|<person name="bob" age="22" city="new york" occupation="student"></person>|
|<person name="bob" age="22" city="new york" occupation="student"></person>|

我正在寻找的结果是：

|Name|age|city    |occupation|
|bob |22 |new york|student   |
|bob |22 |new york|student   |

我可以使用这些列名创建硬编码脚本，但问题是我有 20 多个表，这些表都需要自定义脚本。我的想法是，有一种方法可以让我动态地考虑到目标表和源表（xml），我可以有一个生成这些数据的过程。

标签： sqlsql-servertsqlstored-proceduresdynamic

解决方案

你的问题不是很清楚...

据我了解，您有各种不同的XML，并且您希望通用地阅读它们。如果这是真的，我建议你的下一个问题，在你的样本数据中反映这一点。

一种通用的说法是：如果您想动态地设置结果集的描述性元素（在这种情况下：列的名称），则无法绕过动态创建的语句。T-SQL 依赖于一些你必须提前知道的事情。

尝试这个：

我设置了一个模型场景来模拟您的问题（请在您的下一个问题中尝试自己这样做）：

DECLARE @tbl TABLE(ID INT IDENTITY, Descr VARCHAR(100), VAL XML);
INSERT INTO @tbl VALUES
 ('One person',N'|<person name="bob" age="22" city="new york" occupation="student"></person>')
,('One more person','<person name="bob" age="22" city="new york" occupation="student"></person>')
,('One country','<country name="Germany" capital="Berlin" continent="Europe"></country>');

--此查询依赖于预先知道的所有可能的属性。
--common 属性，如name，为个人和国家
返回 --differing 属性返回为 NULL。-- 一个优点可能是，如果合适，您可以使用特定的数据类型。

SELECT t.ID 
      ,t.Descr
      ,t.VAL.value('(/*[1]/@name)[1]','nvarchar(max)') AS [name] 
      ,t.VAL.value('(/*[1]/@age)[1]','nvarchar(max)') AS [age] 
      ,t.VAL.value('(/*[1]/@city)[1]','nvarchar(max)') AS [city] 
      ,t.VAL.value('(/*[1]/@occupation)[1]','nvarchar(max)') AS [occupation] 
      ,t.VAL.value('(/*[1]/@city)[1]','nvarchar(max)') AS [city] 
      ,t.VAL.value('(/*[1]/@capital)[1]','nvarchar(max)') AS [capital] 
      ,t.VAL.value('(/*[1]/@continent)[1]','nvarchar(max)') AS [continent] 
FROM @tbl t;

--此查询以经典 EAV (entity-attribute-value) 列表形式返回 --
在此结果中，您可以在自己的行中获取每个属性

SELECT t.ID 
      ,t.Descr
      ,A.attrs.value('local-name(.)','nvarchar(max)') AS AttrName
      ,A.attrs.value('.','nvarchar(max)') AS AttrValue
FROM @tbl t
CROSS APPLY t.VAL.nodes('/*[1]/@*') A(attrs);

这两种方法都可以作为字符串级别的语句生成，然后由EXEC()or执行sp_executesql。

提示：一种方法可能是将 EAV 列表插入到一个容错的临时表中，然后从那里进行条件聚合或PIVOT硬编码的 VIEW。

动态方法

为了读取<person>元素，我们需要这个：

SELECT t.ID 
      ,t.Descr
      ,t.VAL.value('(/*[1]/@name)[1]','nvarchar(max)') AS [name] 
      ,t.VAL.value('(/*[1]/@age)[1]','nvarchar(max)') AS [age] 
      ,t.VAL.value('(/*[1]/@city)[1]','nvarchar(max)') AS [city] 
      ,t.VAL.value('(/*[1]/@occupation)[1]','nvarchar(max)') AS [occupation] 
FROM @tbl t
WHERE VAL.value('local-name(/*[1])','varchar(100)')='person';

我们所要做的就是生成变化的部分：

尝试这个：

带有真实桌子的新模型

CREATE TABLE SimulateYourTable(ID INT IDENTITY, Descr VARCHAR(100), VAL XML);
INSERT INTO SimulateYourTable VALUES
 ('One person',N'|<person name="bob" age="22" city="new york" occupation="student"></person>')
,('One more person','<person name="bob" age="22" city="new york" occupation="student"></person>')
,('One country','<country name="Germany" capital="Berlin" continent="Europe"></country>');

--过滤<person>实体

DECLARE @entityName NVARCHAR(100)='person';

--这是一个代表命令的字符串

DECLARE @cmd NVARCHAR(MAX)=
'SELECT t.ID 
      ,t.Descr
      ***columns here***
FROM SimulateYourTable t
WHERE VAL.value(''local-name(/*[1])'',''varchar(100)'')=''***name here***''';

--有了这个我们可以创建所有的列
--提示：使用 SQL Server 2017+ 有STRING_AGG()- 更简单！

DECLARE @columns NVARCHAR(MAX)=
(
    SELECT CONCAT(',t.VAL.value(''(/*[1]/@',Attrib.[name],')[1]'',''nvarchar(max)'') AS ',QUOTENAME(Attrib.[name]))
    FROM SimulateYourTable t
    CROSS APPLY t.VAL.nodes('//@*') AllAttrs(a)
    CROSS APPLY (SELECT a.value('local-name(.)','varchar(max)')) Attrib([name])
    WHERE VAL.value('local-name(/*[1])','varchar(100)')=@entityName
    GROUP BY Attrib.[name]
    FOR XML PATH(''),TYPE
).value('.','nvarchar(max)');

--现在我们把它塞进我们的命令中

SET @cmd=REPLACE(@cmd,'***columns here***',@columns);
SET @cmd=REPLACE(@cmd,'***name here***',@entityName);

——这是命令。
--提示：您可以使用它来创建物理视图，而无需键入它们...

PRINT @cmd;

您可以使用EXEC(@cmd)此动态 SQL 来执行并检查结果。

推荐阅读