Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select element from XML

Given the following:

declare @samplexml as xml
set @samplexml = '<root><someelement><another /><somethingElse>test</somethingElse></someelement></root>'

select
  @samplexml.value('/root[1]','nvarchar(max)')

I get the result:

test

But I want the result:

<root><someelement><another /><somethingElse>test</somethingElse></someelement></root>

How can I select the actual XML element? I also tried:

select
  @samplexml.value('/root[1]','XML')

But I got the error The data type 'XML' used in the VALUE method is invalid..

like image 657
Kyle Avatar asked Feb 18 '23 11:02

Kyle


1 Answers

Just use the .query() method instead of .value() :

SELECT @samplexml.query('/root[1]')

or

SELECT @samplexml.query('.')

This returns the element (and its contents) that matches that XPath expression given, and it's returned as XML type

like image 105
marc_s Avatar answered Feb 26 '23 20:02

marc_s