SQL Generate columns/data based on xml

Question

I have xml like so:

<root>
    <item>
        <Name>Pants</Name>
        <Value>No</Value>
    </item>
    <item>
        <Name>Other</Name>
        <Value>Yes</Value>
    </item>
</root>

<root>
    <item>
        <Name>Pants</Name>
        <Value>Yes</Value>
    </item>
    <item>
        <Name>Other</Name>
        <Value>Yes</Value>
    </item>
</root>

<root>
    <item>
        <Name>a</Name>
        <Value>b</Value>
    </item>
    <item>
        <Name>c</Name>
        <Value>d</Value>
    </item>
</root>

each root element is stored in a column in a sql server database, and I need to create the columns/rows based on it. I'd like the "Name" as the column name and the values populated for that column. There also may not always be 2 <item> elements, there could be more or less.

So when all of the above gets queried I want:

|other columns|Pants|Other|a|c|<-column names
|~~~~~~~~~~~~~|No   |Yes  | | |<-rows
|~~~~~~~~~~~~~|Yes  |Yes  | | |
|~~~~~~~~~~~~~|     |     |b|d|

Is this even possible?

Mikael Eriksson · Accepted Answer · 2013-02-26 14:22:52Z

1

With a dynamic number of columns you need dynamic SQL.

Start with populating a temporary table with the Name/Value list you are interested in. Then create the dynamic SQL statement from the temp table and execute the dynamic query.

select ID,
       X.N.value('(Name/text())[1]', 'varchar(max)') as Name,
       X.N.value('(Value/text())[1]', 'varchar(max)') as Value
into #T
from T
  cross apply T.XMLCol.nodes('root/item') as X(N)

declare @SQL nvarchar(max)
declare @Col nvarchar(max)

select @Col = 
  (
  select distinct ','+quotename(Name)
  from #T
  for xml path(''), type
  ).value('substring(text()[1], 2)', 'nvarchar(max)')

set @SQL = 'select ID,'+@Col+'
            from #T
            pivot (max(Value) for Name in ('+@Col+')) as P'

exec (@SQL)

drop table #T

SQL Fiddle

answered Feb 26, 2013 at 14:22

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chains · Accepted Answer · 2013-02-25 21:35:35Z

1

SELECT 
   XMLColumnName.value('(/root/item/Name)[1]','varchar(max)') AS firstname,
   XMLColumnName.value('(/root/item/Value)[1]','varchar(max)') AS myValue

FROM MyTable

Not sure why you're using the '<root>' tag as you are (usually there is just one begging and ending tag for the entire XML value). Might not make a difference, but you'll have to see.

edited Feb 25, 2013 at 21:35

answered Feb 25, 2013 at 18:19

Chains

13.2k8 gold badges48 silver badges62 bronze badges

2 Comments

Shawn Over a year ago

What " tag are you talking about? If you're referring to the multiple root elements, each root section is stored on a different row in the database.

Chains Over a year ago

@Shawn -- got it -- I thought you had multiple roots in the same record for some reason -- my mistake.

Collectives™ on Stack Overflow

SQL Generate columns/data based on xml

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related