How to find xml node value in sql server? [Resolved]

Posted by Anonymus under Sql Server on 8/30/2012 | Points: 10 | Views : 10583 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hello,

I have following xml

- <Orders>
    <Order OrdID="123"> </Order>
  </Orders>

I want to write a query to fetch OrdID = 123 in sql server..how to do that??? Please help

Regards,
Anonymus

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 8/30/2012 [Member] [MVP] Silver | Points: 50

0	Check It Out! Using Table data Declare @XMLNode Table ( XMLData XML ) Insert @XMLNode Values('<Orders><Order OrdID="123"></Order></Orders>') Insert @XMLNode Values('<Orders><Order OrdID="456"></Order></Orders>') Select XMLData.value('(Orders/Order/@OrdID)[1]', 'varchar(10)') OrdID from @XMLNode Go Using variable Declare @XMLNode XML Select @XMLNode = '<Orders><Order OrdID="123"></Order></Orders>' Select @XMLNode.value('(Orders/Order/@OrdID)[1]', 'varchar(10)') OrdID Go Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Anonymus, if this helps please login to Mark As Answer. \| Alert Moderator

Check It Out!

Using Table data

Declare @XMLNode Table 

(

XMLData	XML

)



Insert @XMLNode Values('<Orders><Order OrdID="123"></Order></Orders>')

Insert @XMLNode Values('<Orders><Order OrdID="456"></Order></Orders>')



Select XMLData.value('(Orders/Order/@OrdID)[1]', 'varchar(10)') OrdID from @XMLNode

Go

Using variable

Declare @XMLNode XML

Select @XMLNode = '<Orders><Order OrdID="123"></Order></Orders>'

Select @XMLNode.value('(Orders/Order/@OrdID)[1]', 'varchar(10)') OrdID 

Go

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Anonymus, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sheonarayan on: 8/30/2012 [Administrator] HonoraryPlatinum | Points: 25

0	Good job Pandians, Keep it up! Regards, Sheo Narayan http://www.dotnetfunda.com Anonymus, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts