convert xml string in a sql table to dynamic columns

Question

I have two tables (using table variables for illustration. You can run these directly in management studio) that are related by the Id column.

Items in the first table has some standard set of columns and the second table has some extended parameter data for the same record. I'm storing the extended set as xml as it is dynamic in all aspects (different per product or new values being added etc).

I'm able to join these two tables and flatten out the column list as you can see in the example below. But my query requires to have the dynamic columns be defined beforehand. I would like to have this truly dynamic in the sense that if I were to add a new column in the @extended table, it should automatically come out as a new column in the output column list.

Basically the list of additional columns should be determined by the xml for that record. column name should be the xml tag and value should be value for the xml tag for each id.

Any pointers? (and can it be fast too with around 100k records or more in each table)

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

I see what you are saying. But this is a very minor part of a huge system where you need to store some attributes on a particular entity. These attributes (name value pair) are dynamically added for the entity per product and are different between products. This entity is the standard table in my example, extended are the attributes. What are some other options then to handle this scenario. I can probably have a somewhat complex 3 or 4 table group and handle this, but this seemed simpler. — coder net, Jun 18 '12 at 18:35
Of course, Marc deleted his comment and so my comment is an orphan. Anyway, this " http://en.wikipedia.org/wiki/Entity-attribute-value_model " is what I'm after. XML is indeed an alternative, but for now, i'm going to go with the regular EAV tables. — coder net, Jun 19 '12 at 18:56

score 3 · Accepted Answer · answered Jun 18 '12 at 20:00

First off you can simplify your current query a bit.

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

To do what you want will not be easy or fast. You need to get a list of all name/value pairs in your 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)

Use that in a pivot query and join to 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

To do this with a dynamic number of columns returned you need to build this pivot query dynamically.
Store the Name/Value pairs in temp table, use that table to figure out the columns you need and to build your query.

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

Thank you for the answer. I may go with the EAV table instead. But you answered for what I was asking. — coder net, Jun 19 '12 at 19:22

score 0 · Answer 2 · answered Jan 11 '17 at 10:50

I hope it will help you

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

convert xml string in a sql table to dynamic columns

2 Answers2