SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Shred to tabular data


XML Shred to tabular data

Author
Message
NickDBA
NickDBA
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 938
Hi, looking for tSQL help on shredding the following xml so that I can see (in this example) all skills per employee
'<employees>
<employee>
<emp_id>1</emp_id>
<emp_name>Bob</emp_name>
<skills>
<skills_id>1</skills_id>
<skills_name>tsql</skills_id>
<skills_id>2</skills_id>
<skills_name>SSRS</skills_id>
<skills_id>3</skills_id>
<skills_name>SSAS</skills_id>
<skills_id>4</skills_id>
<skills_name>SSIS</skills_id>
<skills_id>5</skills_id>
<skills_name>Replication</skills_id>
</skills>
</employee>
<employee>
<emp_id>2</emp_id>
<emp_name>Frank</emp_name>
<skills>
<skills_id>1</skills_id>
<skills_name>tsql</skills_id>
<skills_id>2</skills_id>
<skills_name>SSRS</skills_id>
<skills_id>3</skills_id>
<skills_name>SSAS</skills_id>
</skills>
</employee>
</employees>
'



Thanks

*edited to show skill_id AND skill_name (not just skill_id)
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3606 Visits: 1779
Here is a quick example to shred that xml to a flat table:



DECLARE @xml XML
SET @xml = '<employees>
<employee>
<emp_id>1</emp_id>
<emp_name>Bob</emp_name>
<skills>
<skills_id>1</skills_id>
<skills_id>tsql</skills_id>
<skills_id>2</skills_id>
<skills_id>SSRS</skills_id>
<skills_id>3</skills_id>
<skills_id>SSAS</skills_id>
<skills_id>4</skills_id>
<skills_id>SSIS</skills_id>
<skills_id>5</skills_id>
<skills_id>Replication</skills_id>
</skills>
</employee>
<employee>
<emp_id>2</emp_id>
<emp_name>Frank</emp_name>
<skills>
<skills_id>1</skills_id>
<skills_id>tsql</skills_id>
<skills_id>2</skills_id>
<skills_id>SSRS</skills_id>
<skills_id>3</skills_id>
<skills_id>SSAS</skills_id>
</skills>
</employee>
</employees>
'


SELECT e.c.value('(emp_id)[1]', 'int') AS emp_id
, e.c.value('(emp_name/text())[1]', 'varchar(50)') AS emp_name
, s.c.value('(.)[1]', 'varchar(50)') AS skills_id
FROM @xml.nodes('/employees/employee') e(c)
CROSS APPLY e.c.nodes('skills/skills_id') AS s(c)



Not sure if it is going to be what you are after though looking at that data. Did you mean to have the skills id and the skill name in xml nodes of the same name? as by looking at the data i'm guessing you may would like to have it so the skills id is in a column and the skill name is also in a column and both related to each other rather than both in a single column.
NickDBA
NickDBA
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 938
Hi, thanks for taking time to look at that. Ideally I'd like to see....

Emp_id Skill_ID Skill_Name
1 1 tSQL
1 2 SSRS
1 3 SSAS
1 4 SSIS
1 5 Replication
2 1 tSQL
2 2 SSRS


etc
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213251 Visits: 41977
NickBalaam (3/1/2013)
Hi, thanks for taking time to look at that. Ideally I'd like to see....

Emp_id Skill_ID Skill_Name
1 1 tSQL
1 2 SSRS
1 3 SSAS
1 4 SSIS
1 5 Replication
2 1 tSQL
2 2 SSRS


etc


Just a heads up on that. The XML you were provided is "poorly formed" in that, except by position in the file, there's no logical manner to associate a particular skill ID with the correct name. You can't rely on the position in the file for these sorts of things because it's just not reliable.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
NickDBA
NickDBA
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 938
Thanks for the heads up. Unfortunately this is not our own XML, it's comes to us from a 3rd party and we have no control over the structure. Ideally it would have had something like <skill id=1>tSQL</skill> but I can't really do much about it :-(
Arthur Olcot
Arthur Olcot
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3606 Visits: 1779
As Jeff points out, the xml isn't ideal which is true, but I have come up with something that makes a lot of assumptions!. Mainly around the ordering of the xml which if changes, will break this so I give no assurances to this code :-D


DECLARE @xml XML
SET @xml = '<employees>
<employee>
<emp_id>1</emp_id>
<emp_name>Bob</emp_name>
<skills>
<skills_id>1</skills_id>
<skills_id>tsql</skills_id>
<skills_id>2</skills_id>
<skills_id>SSRS</skills_id>
<skills_id>3</skills_id>
<skills_id>SSAS</skills_id>
<skills_id>4</skills_id>
<skills_id>SSIS</skills_id>
<skills_id>5</skills_id>
<skills_id>Replication</skills_id>
</skills>
</employee>
<employee>
<emp_id>2</emp_id>
<emp_name>Frank</emp_name>
<skills>
<skills_id>1</skills_id>
<skills_id>tsql</skills_id>
<skills_id>2</skills_id>
<skills_id>SSRS</skills_id>
<skills_id>3</skills_id>
<skills_id>SSAS</skills_id>
</skills>
</employee>
</employees>
'

SELECT @xml = @xml.query('
<employees>
{
for $x in //employee
return
<employee emp_name="{$x/emp_name/text()}" emp_id="{$x/emp_id/text()}">
{
for $y in $x/skills/skills_id[number(text()[1]) > 0]
return
<skill id="{data($y)}" value="{data($x/skills/skills_id[. >> $y][1])}"/>
}
</employee>
}
</employees>
')

SELECT e.c.value('@emp_id', 'int') AS emp_id
, e.c.value('@emp_name', 'varchar(50)') AS emp_name
, s.c.value('@id', 'int') AS skills_id
, s.c.value('@value', 'varchar(50)') AS skill
FROM @xml.nodes('/employees/employee') e(c)
CROSS APPLY e.c.nodes('skill') AS s(c)



Returns:


emp_id emp_name skills_id skill
1 Bob 1 tsql
1 Bob 2 SSRS
1 Bob 3 SSAS
1 Bob 4 SSIS
1 Bob 5 Replication
2 Frank 1 tsql
2 Frank 2 SSRS
2 Frank 3 SSAS

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213251 Visits: 41977
NickBalaam (3/1/2013)
Thanks for the heads up. Unfortunately this is not our own XML, it's comes to us from a 3rd party and we have no control over the structure. Ideally it would have had something like <skill id=1>tSQL</skill> but I can't really do much about it :-(



Heh... actually, you can. Invite them to dinner. A nice pork chop dinner. To make it so they understand, tie them to the chair and feed them the porchops... at point blank range with a Wrist Rocket. :-D You'll get your point across.

If these people are providing a "service", they need to provide it correctly. If they're a customer, the need to help you help them. Since the answer is always "No" unless you ask, contact the 3rd part and tell them they're doing it wrong and you want it fixed!

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
NickDBA
NickDBA
SSC Eights!
SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)SSC Eights! (839 reputation)

Group: General Forum Members
Points: 839 Visits: 938
Yes, I like that idea. Haha

Thanks both for your time. I think I can work with that.

Regards
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search