将SQL表中的XML字符串转换为动态列

Question

我有两个表(使用表变量进行说明，您可以在管理工具中直接运行这些表)，它们通过Id列相关联。第一个表中的项目具有一些标准列，而第二个表中的同一记录具有一些扩展参数数据。我将扩展集存储为 xml，因为它在所有方面都是动态的(每个产品不同或添加新值等)。

我能够连接这两个表并展开列列表，如下例所示。但我的查询需要预先定义动态列。我希望这是真正动态的，如果我在 @extended 表中添加了一个新列，它应自动显示为输出列列表中的新列。

基本上，附加列的列表应由该记录的 xml 决定。对于每个id，列名应为xml标记，值应为xml标记的值。

有任何指针吗？(并且它是否能够快速处理每个表中约100,000条记录或更多)

declare @standard table
(
   Id INT,
   Column1 varchar(10),
   Column2 varchar(10),
   Column3 varchar(10)
)

declare @extended table
(
    Id INT,
    column1 xml
)

insert into @standard values (1,'11', '12', '13')
insert into @standard values (2,'21', '22', '23')

insert into @extended values (1,'<FieldSet><Field><id>1</id><column4>1x</column4><column5>4x</column5></Field></FieldSet>')
insert into @extended values (2,'<FieldSet><Field><id>2</id><column4>2x</column4><column5>5x</column5></Field></FieldSet>')

select s.column1, s.column2,

( 
    SELECT Item2.value('(column4)[1]', 'varchar(50)')
    FROM 
    e.column1.nodes('/FieldSet') AS T(Item)
    CROSS APPLY e.column1.nodes('/FieldSet/Field') AS T2(Item2)

 ) column4,

 ( 
    SELECT Item2.value('(column5)[1]', 'varchar(50)')
    FROM 
    e.column1.nodes('/FieldSet') AS T(Item)
    CROSS APPLY e.column1.nodes('/FieldSet/Field') AS T2(Item2)

 ) column5 

from @extended e
join @standard s on s.Id = e.Id

- coder net

---

我明白你的意思。但这只是一个非常小的部分，而整个系统需要在特定实体上存储一些属性。这些属性（名称值对）是针对每个产品动态添加的，并且在产品之间不同。在我的示例中，该实体是标准表，扩展了属性。那么，除了处理此场景的三个或四个相对复杂的表组之外，还有哪些其他选项呢？但这似乎更简单。 - coder net

当然，马克删除了他的评论，所以我的评论成为了孤儿。无论如何，我想要的是“http://en.wikipedia.org/wiki/Entity-attribute-value_model”。XML确实是一种选择，但现在，我将使用常规的EAV表。 - coder net

Answer 1

首先，您可以稍微简化当前的查询。

select s.column1,
       s.column2,
       e.column1.value('(/FieldSet/Field/column4)[1]', 'varchar(50)') as column4,
       e.column1.value('(/FieldSet/Field/column5)[1]', 'varchar(50)') as column5 
from extended as e
  join standard as s
    on s.Id = e.Id

想要做你想要的事情并不容易或快速。你需要获取XML中所有名称/值对的列表。

select T1.X.value('.', 'int') as Id,
       T2.X.value('local-name(.)', 'sysname') as Name,
       T2.X.value('.', 'varchar(10)') as Value
from extended as e
  cross apply e.column1.nodes('/FieldSet/Field/id') as T1(X)
  cross apply e.column1.nodes('/FieldSet/Field/*[position() > 1]') as T2(X)

在透视查询中使用它并连接到standard。

select S.column1,
       S.column2,
       P.column4,
       P.column5
from standard as s
  inner join
      (
      select id, P.column4, P.column5
      from (
           select T1.X.value('.', 'int') as Id,
                  T2.X.value('local-name(.)', 'sysname') as Name,
                  T2.X.value('.', 'varchar(10)') as Value
           from extended as e
             cross apply e.column1.nodes('/FieldSet/Field/id') as T1(X)
             cross apply e.column1.nodes('/FieldSet/Field/*[position() > 1]') as T2(X)
           ) as e
      pivot (min(Value) for Name in (column4, column5)) P
      ) P
    on S.Id = P.Id

为了在动态返回列数的情况下执行此操作，您需要动态构建此数据透视查询。
将名称/值对存储在临时表中，使用该表来确定所需的列并构建查询。

create table #ext
(
  Id int,
  Name sysname,
  Value varchar(10),
  primary key(Id, Name)
)

insert into #ext(Id, Name, Value)
select T1.X.value('.', 'int') as Id,
       T2.X.value('local-name(.)', 'sysname') as Name,
       T2.X.value('.', 'varchar(10)') as Value
from extended as e
  cross apply e.column1.nodes('/FieldSet/Field/id') as T1(X)
  cross apply e.column1.nodes('/FieldSet/Field/*[position() > 1]') as T2(X)

declare @SQL nvarchar(max)
set @SQL = 
'select S.column1,
        S.column2,
        [COLLIST]
from standard as s
  inner join
      (
      select id, [COLLIST]
      from #ext as e
      pivot (min(Value) for Name in ([COLLIST])) P
      ) P
    on S.Id = P.Id'

declare @ColList nvarchar(max)

set @ColList = 
  (select ','+Name
   from #ext
   group by Name
   for xml path(''), type).value('.', 'nvarchar(max)')

set @SQL = replace(@SQL, '[COLLIST]', stuff(@ColList, 1, 1, ''))

exec (@SQL)

drop table #ext

- Mikael Eriksson

---

谢谢你的回答。我可能会选择EAV表。但是你回答了我的问题。 - coder net

Answer 2

希望它能对你有所帮助

SELECT @COUNT_XML=0

                    SELECT @COUNT_XML=(SELECT @xxxxx_GROUP_ID.value('count(/NewDataSet/position/ID)', 'INT'))

                    IF(@COUNT_XML > 0)            

                        BEGIN
                          IF OBJECT_ID('tempdb..#TBL_TEMPOSITION') IS NOT NULL  
                           DROP TABLE  #TBL_TEMPOSITION

                             CREATE TABLE #TBL_TEMPOSITION (ID NUMERIC(18,0)) 
                             INSERT INTO #TBL_TEMPOSITION (ID)    
                             SELECT XMLxxxxGroup.PositionGPItem.value('.','NUMERIC(18,0)')
                             FROM   @xxxxx_GROUP_ID.nodes('/NewDataSet/position/ID') AS XMLPositionGroup(PositionGPItem) 
                             SELECT @emp_cond =@emp_cond+ N' AND CM.STATIC_EMP_INFO.POSITION_ID IN (SELECT ID FROM #TBL_TEMPOSITION) '        
                        END 

- Praveen P K