1
declare @xmlVal xml
set @xmlVal = 
'<user>
    <token><id>ABC123</id><endDate>2013-06-16 18:48:50.111</endDate></token>
    <token><id>XYX456</id><endDate>2014-01-01 18:48:50.111</endDate></token>
</user>'

I need the entire token node as xml where id=ABC123 so the out put will be :

<token>
    <id>ABC123</id>
    <endDate>2013-06-16 18:48:50.111</endDate>
</token>
LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114
Tony Thomas
  • 398
  • 7
  • 20

2 Answers2

2
select @xmlVal.query('/user/token[id="ABC123"]')
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
1
SELECT  
a.b.value('id[1]','nvarchar(max)') id,
a.b.value('endDate[1]','datetime') endDate
 FROM    @xmlval.nodes('//user/token[id="ABC123"]') a(b)
 FOR XML PATH('token')
Dalex
  • 3,585
  • 19
  • 25