Click here to monitor SSC
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
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 936
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 (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 1777
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
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 936
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
NickDBA
NickDBA
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 936
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 (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 1777
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45047 Visits: 39898
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
NickDBA
NickDBA
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 936
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