Sql server xml parse

Question

Please find below the script snippet

declare @xml xml
set @xml = '<Message>

<MessageData>
  <MessageDataSet >
  <id> 1 </id>
  <name>Vasan</name>     
  </MessageDataSet>  
   <MessageDataSet >

    <id> 2 </id>
    <name>Vivek</name>
  </MessageDataSet>  

</MessageData>

</Message>'

SELECT
t.c.value('(id)[1]','varchar(100)')
from
@xml.nodes('/Message/MessageData/MessageDataSet')  AS t(c)

I am getting all the 'id' node values, that is '1' & '2'. My need, I have to directly fetch the value of the second 'id', in this case.it will be '2'. Can anyone let me know the Select query syntax for the same? Thanks.

marc_s · Accepted Answer · 2012-08-16 15:06:08Z

1

How about this:

SELECT
    @xml.value('(/Message/MessageData/MessageDataSet/id)[2]','varchar(100)')

Returns 2 as the value for me. This grabs the exactly second entry inside that XML - if it's not there, you'll get NULL back.

Also: why convert this to a varchar(100) - wouldn't int be a more appropriate type here??

answered Aug 16, 2012 at 15:06

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vasan Over a year ago

@mark_s, sorry missed to answer your other query, the varchar(100) was just a stop gap stuff, you are right, I will be using the appropriate type.

Collectives™ on Stack Overflow

Sql server xml parse

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related