asked on

SQL: shread xml to table where attributes not known a priori

Folks,

lets say that I have the following table.
Name XmlData
------- ---------
bob <root><Column name="haircolor">blue</Column><Column name="title">Boss</Column></root>
jane <root><Column name="haircolor">green</Column><Column name="title">peon</Column></root>

Lets also say that a priori I don't know the actual values of the "name" attribute. Here I show "haircolor" and "title" but it could be others.

what SQL query will convert this into a table:
Name haircolor title
------- ---------- -------
bob blue boss
jane green peon

again, I don't know which name attributes will exist. I DO know that the same named attributes will exist in the table. So if bob has "haircolor" and "title", then everyone else will have the same attributes.

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sfun28

ASKER

WOW! perfect. Its rare on EE to get a perfect code-sample the first time. I'm looking forward to studying your solution to understand it better!

sfun28

ASKER

PERFECT!!! thanks so much!!!

sfun28

ASKER

Not to push my luck here, but is there a solution that would work if the attributes aren't guaranteed to exist for all rows? So if Jane had <Column name="age">10</Column> but Bob doesn't?

Should I post another question for this?

cyberkiwi

Hi there,

This should work

Cheers

declare @sql nvarchar(max)
set @sql = ''
select @sql = @sql + ',xmldata.value(''(/root/Column[@name='''''+ nodename +
'''''])[1]'', ''varchar(100)'') '+nodename
from
(
	select distinct nodename = names.n.value('.','varchar(100)')
	from t3 cross apply xmldata.nodes('//@name') names(n)
) X
set @sql = 'select Name ' + @sql + ' from t3';
--print @sql
exec (@sql)

Open in new window

sfun28

ASKER

PERFECT!!! Thanks so much!!!