read xml node elements using sql query

ramprasadkin · #1 (**permalink**) 12-29-11, 06:52

Hi All,

Iam having the example 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

Thanks,
Ram

homerow · #2 (**permalink**) 01-01-12, 07:14

The following SQL is a possible solution for you:

Code:

select
  a.value('(../../name)[1]','varchar(128)') as [CHILDNAME]
 ,a.value('artno[1]','varchar(32)') as [ARTICLE_NUMBER]
 ,a.value('description[1]','varchar(1024)') as [DESCRIPTION]
 ,a.value('price[1]','money') as [PRICE]
from @x.nodes('//article') X(A)

To discover more about XQuery and XPath, click here.

ramprasadkin · #3 (**permalink**) 01-03-12, 07:24

Thanks dude..i got it worked

Internet Services

Web Development

Digital Marketing

Consumer Tech