时间戳

首页 > 解决方案 > 将稀疏列转换为表行；使用具有未知节点名称的 XML

sql-server - 将稀疏列转换为表行；使用具有未知节点名称的 XML

问题描述

我继承了一个使用稀疏列的应用程序。这种架构让我很头疼。最终我的目标是将这些列转换为表格行。数据库位于 SQL Server 2012 上。

表中有超过 8000 列，并且有一个名为“SpecialPurposeColumns”的列，我相信它被称为“列集”。该列包含具有数据的列的 XML 表示。SpecialPurposeColumns 中的数据如下所示：

<q7109>2</q7109><q7110>4</q7110><q7111>1</q7111><q7120>4</q7120>

这些节点名称对应于 8000 多个列之一，并且对于给定的记录，列只有在它们包含值时才会在 XML 中表示。我永远不知道哪些列将在 XML 中表示，所以我永远不知道会出现哪些节点名称。

这可能是一个禁忌，但这里确实有两个问题：

  1. 如何遍历 SpecialPurposeColumns 中的节点并获取节点名称和值？我看过很多关于在 SQL 中解析 XML 的帖子，并且似乎都认为节点名称是已知的。

  2. 鉴于我的最终目标是将此列数据转换为行，有没有比迭代每一行和每个 SpecialPurposeColumns 节点以将这些值作为行放入另一个表更好的方法？

标签： sql-serverxml

解决方案

例子

Declare @YourTable Table (ID int,SpecialPurposeColumns xml)
Insert Into @YourTable Values 
(1,'<q7109>2</q7109><q7110>4</q7110><q7111>1</q7111><q7120>4</q7120>')

Select A.ID
      ,B.*
 From @YourTable A
 Cross Apply (
               Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                     ,Value = xAttr.value('.','varchar(max)')
                From  SpecialPurposeColumns.nodes('*') xNode(xAttr)
             ) B

退货

ID  Item    Value
1   q7109   2
1   q7110   4
1   q7111   1
1   q7120   4

推荐阅读