Problem with XML content into SQL

Question

Post reply

Problem with XML content into SQL

grecci1077

SSC Enthusiast

Points: 149
More actions
March 23, 2012 at 6:48 am

#253081

If I have the following:
1 Mike Blue
2 Mike Red
3 Joe Green
4 Joe Black
5 Joe Red
And I want
1 Mike Blue Red
2 Joe Green Black Red
What would be the easiest way to manipulate the data being mindful that i am really working with an XML file that I have imported into memory:
<Automobile>
<Car name=Corvette>
<Properties color=Red ID=12345>
</Properties
<PropertiesGroup color=Green>
</Properties>
</Car>
</Automobile>
So When I select the data from memory I get:
Corvette 12345 Red
Corvette 12345 Green
But I want:
Corvette 12345 Red Green
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86913 More actions · Answer 1

It would probably help if your XML was properly formatted (corrected below).

Does this give you what you're looking for?

DECLARE @data XML

SET @data = CAST(

'<Automobile>

<Car name="Corvette">

<Properties color="Red" ID="12345"></Properties>

<PropertiesGroup color="Green">

</PropertiesGroup>

</Car>

</Automobile>' AS XML)

SELECT x.name.value('@name[1]', 'VARCHAR(10)') AS name

,y.name.value('@ID[1]', 'INT') as ID

,y.name.value('@color[1]', 'VARCHAR(10)') as Color1

,z.name.value('@color[1]', 'VARCHAR(10)') as Color2

FROM @data.nodes('/Automobile/Car') x(name)

,@data.nodes('/Automobile/Car/Properties') y(name)

,@data.nodes('/Automobile/Car/PropertiesGroup') z(name)

Results:

nameIDColor1Color2

Corvette12345RedGreen

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

grecci1077 SSC Enthusiast Points: 149 More actions · Answer 2

Thanks Dwain.c for looking into it. Your answer as laid out works great but then I run into more problems as I work my way down the XML file.

DECLARE @data XML

SET @data = CAST(

'<Automobile>

</Properties>

</PropertiesGroup>

</Car>

</Properties>

</PropertiesGroup>

</Car>

</Properties>

</PropertiesGroup>

</Car>

</Automobile>' AS XML)

SELECT x.name.value('@name[1]', 'VARCHAR(10)') AS name

,y.name.value('@ID[1]', 'INT') as ID

,y.name.value('@color[1]', 'VARCHAR(10)') as Color1

,z.name.value('@color[1]', 'VARCHAR(10)') as Color2

FROM @data.nodes('/Automobile/Car') x(name)

,@data.nodes('/Automobile/Car/Properties') y(name)

,@data.nodes('/Automobile/Car/PropertiesGroup') z(name)

nameIDColor1Color2

Corvette12345RedGreen

Corvette12345RedBlack

Corvette12345RedNULL

Viper12345RedGreen

Viper12345RedBlack

Viper12345RedNULL

Camry12345RedGreen

Camry12345RedBlack

Camry12345RedNULL

Corvette22345BlueGreen

Corvette22345BlueBlack

Corvette22345BlueNULL

Viper22345BlueGreen

Viper22345BlueBlack

Viper22345BlueNULL

Camry22345BlueGreen

Camry22345BlueBlack

Camry22345BlueNULL

Corvette62345RedGreen

Corvette62345RedBlack

Corvette62345RedNULL

Viper62345RedGreen

Viper62345RedBlack

Viper62345RedNULL

Camry62345RedGreen

Camry62345RedBlack

Camry62345RedNULL

Mark Cowne One Orange Chip Points: 26865 More actions · Answer 3

Use OUTER APPLY

SELECT x.name.value('@name[1]', 'VARCHAR(10)') AS name

,y.name.value('@ID[1]', 'INT') as ID

,y.name.value('@color[1]', 'VARCHAR(10)') as Color1

,z.name.value('@color[1]', 'VARCHAR(10)') as Color2

FROM @data.nodes('/Automobile/Car') x(name)

OUTER APPLY x.name.nodes('Properties') y(name)

OUTER APPLY x.name.nodes('PropertiesGroup') z(name)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Dwain Camps SSC Guru Points: 86913 More actions · Answer 4

Grecci,

I'm glad my post helped to jump start the discussion.

I suspected it might have issues with more XML but I was lazy to build it myself. Mark's is spot on.

It just goes to show that when you want a good answer, posting good test data is vital.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Diyas SSCarpal Tunnel Points: 4845 More actions · Answer 5

you can learn from http://qa.sqlservercentral.com/articles/XML/87685/

grecci1077 SSC Enthusiast Points: 149 More actions · Answer 6

Thank you to both of you guys. You have saved me so much time. By the way I am not all that verse with SQL. Was the method that you guys helped me with considered advanced? Thanks again!

Dwain Camps SSC Guru Points: 86913 More actions · Answer 7

grecci1077 (5/17/2012)
Thank you to both of you guys. You have saved me so much time. By the way I am not all that verse with SQL. Was the method that you guys helped me with considered advanced? Thanks again!

Probably not that advanced but I consider it a pain in the @** (sorry neck)!

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St