Parsing XML using SQL Server 2008

Question

Post reply

Parsing XML using SQL Server 2008

a4apple

SSCrazy

Points: 2620
More actions
July 19, 2013 at 3:09 pm

#280344

DECLARE @X XML = <Movies>
<Movie Name = "Titanic">
<Genres>
<Genre Name="Romance"/>
<Genre Name="Tragedy"/>
</Genres>
<Rate Value = "10"/>
</Movie>
<Movie Name = "ABC">
<Rate Value="15"/>
</Movie>
</Movies>
My expected output is
Movie Genre Value
Titanic Romance 10
Titanic Tragedy 10
ABC NULL 15
I hope you understood my requirement. I was able to do this when all the XML tags are present. If you have noticed, the GENRE tags for ABC movie are missing and my code is ignoring that movie. Can anyone help me on this please.
Thanks in advance.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

If you post your query, it will be much easier to help.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

DECLARE @X XML = '<Movies>

<Movie Name = "Titanic">

<Genres>

<Genre Name="Romance"/>

<Genre Name="Tragedy"/>

</Genres>

<Rate Value = "10"/>

</Movie>

<Movie Name = "ABC">

<Rate Value="15"/>

</Movie>

</Movies>'

SELECT M.c.value('@Name', 'nvarchar(100)') AS Movie,

G.c.value('@Name', 'nvarchar(100)') AS Genre,

R.c.value('@Value', 'int') AS Rate

FROM @X.nodes('/Movies/Movie') AS M(c)

OUTER APPLY M.c.nodes('Genres/Genre') AS G(c)

OUTER APPLY M.c.nodes('Rate') AS R(c)

I would assume that your error was that you used CROSS APPLY instead of OUTER APPLY. OUTER APPLY maintains the row on the left side, even if there is no row on the right side.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

a4apple SSCrazy Points: 2620 More actions · Answer 3

Erland Sommarskog (7/21/2013)
DECLARE @X XML = '<Movies> <Movie Name = "Titanic"> <Genres> <Genre Name="Romance"/> <Genre Name="Tragedy"/> </Genres> <Rate Value = "10"/> </Movie> <Movie Name = "ABC"> <Rate Value="15"/> </Movie> </Movies>' SELECT M.c.value('@Name', 'nvarchar(100)') AS Movie, G.c.value('@Name', 'nvarchar(100)') AS Genre, R.c.value('@Value', 'int') AS Rate FROM @X.nodes('/Movies/Movie') AS M(c) OUTER APPLY M.c.nodes('Genres/Genre') AS G(c) OUTER APPLY M.c.nodes('Rate') AS R(c)
I would assume that your error was that you used CROSS APPLY instead of OUTER APPLY. OUTER APPLY maintains the row on the left side, even if there is no row on the right side.

Thank you Erland.. Exactly I was using a Cross Apply like you said. Thanks for making me understand.

Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.