Jonas Jonas - 2 months ago 6
SQL Question

I need a XML Element of a XML Element

Lets pretend i have the following XML.

<xml>
...
<tag>
<othertag>
</othertag>
</tag>
...
</xml>


What i want is to "extract" the

<tag>
<othertag>
</othertag>
</tag>


into another Variable.
How can i achieve this?

Answer
DECLARE @xml XML=
'<xml>
 <SomeTag test="x"/>
 <SomeTag test="y"/>
 <tag>
  <othertag>
  </othertag>
 </tag>
 <SomeTag test="z"/>
</xml>';

If possible, be as specific as possible

DECLARE @xml2 XML = @xml.query('(/xml/tag)[1]');

SELECT @xml2;

If not, you might go the lazy way...

DECLARE @xml3 XML = @xml.query('//tag');

SELECT @xml3;