c# - 将sql表中的xml字符串转换为动态列

Question

我有两个通过 Id 列关联的表(使用表变量进行说明。您可以直接在管理工作室中运行它们)。

第一个表中的项目有一些标准的列集，第二个表有一些相同记录的扩展参数数据。我将扩展集存储为 xml，因为它在所有方面都是动态的(每个产品不同或添加的新值等)。

我可以连接这两个表并展平列列表，如下例所示。但是我的查询需要事先定义动态列。如果我要在 @extended 表中添加一个新列，它应该会自 Action 为输出列列表中的一个新列出现，我希望它具有真正的动态性。

基本上，附加列的列表应该由该记录的 xml 确定。列名应该是 xml 标记，值应该是每个 id 的 xml 标记的值。

有什么指点吗？ (如果每个表中有大约 10 万条记录或更多记录，它也可以很快吗)

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

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)

在数据透视查询中使用它并加入标准。

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