• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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.
0
sfun28
Asked:
sfun28
  • 4
  • 2
1 Solution
 
cyberkiwiCommented:
Hi,

This will help you
/* sample table
create table t3 (
name varchar(5),
xmldata xml);
insert t3 (name, xmldata) values
('bob','<root><Column name="haircolor">blue</Column><Column name="title">Boss</Column></root>');
insert t3 (name, xmldata) values
('jane','<root><Column name="haircolor">green</Column><Column name="title">peon</Column></root>');
*/

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

Open in new window

0
 
sfun28Author Commented:
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!
0
 
sfun28Author Commented:
PERFECT!!!  thanks so much!!!
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
sfun28Author Commented:
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?
0
 
cyberkiwiCommented:
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

0
 
sfun28Author Commented:
PERFECT!!!  Thanks so much!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now