extracting data from xml - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

extracting data from xml

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-12-29 : 04:36:42

Hi All,

Iam having the xml with the data as shown below,

declare @x xml
set @x = '<SinterklaasWishlists>
<child>
<name>Tim</name>
<wishlist>
<article>
<artno>21491269</artno>
<description>Crane</description>
<price>12.50</price>
</article>
<article>
<artno>21499517</artno>
<description>Keyboard</description>
<price>10</price>
</article>
<article>
<artno>21521591</artno>
<description>Crime Investigation Game</description>
<price>9.95</price>
</article>
</wishlist>
</child>
<child>
<name>Tim2</name>
<wishlist>
<article>
<artno>3145678</artno>
<description>Mouse</description>
<price>12.50</price>
</article>
</wishlist>
</child>
</SinterklaasWishlists>'

I want to extract the elements of xml using sql query and insert the data into the table as shown below:

CHILDNAME ARTICLE_NUMBER DESCRIPTION PRICE
--------------- -------------------- ----------------------------------- -----------
Tim1 21491269 Crane 12.50
Tim1 21499517 Keyboard 10
Tim1 21521591 Crime Investigation Game 9.95
Tim2 3145678 Mouse 12.50

Kindly help me on the same

rams

mohawk
Starting Member

1 Post

Posted - 2011-12-29 : 06:40:56

Hi Jimooba,

Take a look on this tutorial http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/0e1081bd-93eb-44fd-b17d-5d016cf9c732
I am sure you gonna get your answer here.

www.mohawkarmynavy.com/categories/Carhartt/
www.mohawkarmynavy.com/categories/Military/

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-12-29 : 06:42:45

No dude,

No luck :(

rams

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-29 : 06:58:25

You can shred it like this:

SELECT
	c1.value('name[1]','VARCHAR(255)') AS ChildName,
	c2.value('artno[1]','BIGINT') AS Article_Number,
	c2.value('description[1]','varchar(255)') AS [Description],
	c2.value('price[1]','varchar(255)') AS Price
FROM
	@x.nodes('//child') T1(c1)
	CROSS APPLY c1.nodes('wishlist/article') T2(c2);

If your data is in a table rather than a variable, you will need to use one more level of cross apply against the column in the table.

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-12-29 : 07:05:16

Thanks a lot Sunita, i spent almost 2 hours on this , it works for me.

HAPPY NEW YEAR IN ADVANCE :) HAVE A BLAST

Thanks,
RAM

rams

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-29 : 07:07:54

very welcome; happy new year to you too!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources