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

Question

我有两个表（使用表变量进行说明。可以直接在管理工作室中运行这些内容），这些内容与 Id 列相关。

第一个表中的项目具有

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

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

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

有什么指示吗？（而且每个表中大约有 100k 条记录或更多，它也可以很快）

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)

在透视查询中使用它并连接到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

Answer 2

我希望它能帮助你

选择 @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