在SQL Server 2016中从xml列重新创建一行

Question

我将通过应用于约60个表的触发器创建的行存储到单个审计表中。

我有一个xml列在行存储英寸

如何重新创建随机XML数据的行（在查询我不知道，因为所有的表行存储的XML结构的点（在这种情况下，节点）都与不同的列不同）

一个条目可以像这样

<Log> 
    <id>2</id> 
    <text>232323</text> 
    <text1>2323</text1> 
    <text2>2323</text2> 
    <text3>2323</text3> 
    <text4>2323</text4> 
    <text5>2323</text5> 
</Log> 

和另一个条目可能看起来像这样

<Log> 
    <id>3</id> 
    <tableName>ExcludeTables</tableName> 
</Log> 

现在，当我从logtable选择其中id = 50我想看到的列和值在一个普通的SQL服务器数据行格式

感谢您的帮助

Answer 1

SQL Server不动态支持创建列别名。实现这个与列名未知的唯一方法是动态创建的SQL。

在这个例子中，我创建了一个模型表并插入两行。

第一行将返回您的节点作为键值对。

第二，动态地创建一个查询，因为你需要它返回的结果：

DECLARE @tbl TABLE(ID INT, SomeValue VARCHAR(100),AuditColumn XML) 
INSERT INTO @tbl VALUES 
(1,'Row 1' 
,N'<Log> 
    <id>2</id> 
    <text>232323</text> 
    <text1>2323</text1> 
    <text2>2323</text2> 
    <text3>2323</text3> 
    <text4>2323</text4> 
    <text5>2323</text5> 
</Log>') 
,(2,'Row 2' 
,N'<Log> 
    <id>3</id> 
    <tableName>ExcludeTables</tableName> 
</Log>'); 

- 这是您正在寻找

DECLARE @id INT=1; 

的ID --The结果是键值对

SELECT ID 
     ,SomeValue 
     ,nd.value('local-name(.)','nvarchar(max)') AS NodeName 
     ,nd.value('.','nvarchar(max)') AS NodeValue 
FROM @tbl AS t 
CROSS APPLY t.AuditColumn.nodes('/Log/*') A(nd) 
WHERE [email protected]; 

--The结果

名单10

+----+-----------+----------+-----------+ 
| ID | SomeValue | NodeName | NodeValue | 
+----+-----------+----------+-----------+ 
| 1 | Row 1  | id  | 2   | 
+----+-----------+----------+-----------+ 
| 1 | Row 1  | text  | 232323 | 
+----+-----------+----------+-----------+ 
| 1 | Row 1  | text1 | 2323  | 
+----+-----------+----------+-----------+ 
| 1 | Row 1  | text2 | 2323  | 
+----+-----------+----------+-----------+ 
| 1 | Row 1  | text3 | 2323  | 
+----+-----------+----------+-----------+ 
| 1 | Row 1  | text4 | 2323  | 
+----+-----------+----------+-----------+ 
| 1 | Row 1  | text5 | 2323  | 
+----+-----------+----------+-----------+ 

--dynamically创建SQL语句

DECLARE @cmd NVARCHAR(MAX)= 
(
    SELECT N'SELECT ' 
      + STUFF(
      (
       (
       SELECT ',''' + nd.value('.','nvarchar(max)') + ''' AS ' + QUOTENAME(nd.value('local-name(.)','nvarchar(max)')) 
       FROM @tbl AS t 
       CROSS APPLY t.AuditColumn.nodes('/Log/*') A(nd) 
       WHERE [email protected] 
       FOR XML PATH(''),TYPE).value('.','nvarchar(max)') 
       ),1,1,'' 
      ) 
); 

- 这是声明

PRINT @cmd; 

SELECT '2' AS [id],'232323' AS [text],'2323' AS [text1],'2323' AS [text2],'2323' AS [text3],'2323' AS [text4],'2323' AS [text5] 

- 兼，这是它的执行

EXEC (@cmd); 

+----+--------+-------+-------+-------+-------+-------+ 
| id | text | text1 | text2 | text3 | text4 | text5 | 
+----+--------+-------+-------+-------+-------+-------+ 
| 2 | 232323 | 2323 | 2323 | 2323 | 2323 | 2323 | 
+----+--------+-------+-------+-------+-------+-------+