CODEWITHSUNDEEP
sql-serverxmlxpathxquery
Andy 1
Andy 1

Reputation: 53

How do you get a node that contains a child node with a given attribute value in an XML document using sql server?

I'm working on parsing XML documents using SQL Server 2008. I'm a complete noob and I was wondering if I can get help from you guys.

I have an XML document like the one below and I want to get the "section" node where the "code" node has val=5.

<root>
  <section>
    <code val=6 />
    ...
  </section>
  <section>
    <code val=5 />
    ...
  </section>
  <section>
    <code val=4 />
    ...
  </section>
</root>

So the result should be: <section> <code val=5 /> ... </section>

I tried doing this, but it didn't work:

select @xml.query('/root/section') where @xml.value('/root/section/code/@val,'int')= '5'

I also tried this: select @xml.query('/root/section') where @xml.exist('/root[1]/section[1]/code[@val="1"])= '1'

Any ideas? Thanks in advance.

Upvotes: 0

Views: 1093

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

You could use this query:

DECLARE @x XML=N'
<root>
  <section atr="A">
    <code val="5" />
  </section>
  <section atr="B">
    <code val="6" />
  </section>
  <section atr="C">
    <code val="5" />
  </section>
</root>';

SELECT  a.b.query('.') AS SectionAsXmlElement,
        a.b.value('@atr','NVARCHAR(50)') AS SectionAtr
FROM    @x.nodes('/root/section[code/@val="5"]') a(b);

Results:

SectionAsXmlElement                         SectionAtr
------------------------------------------- ----------
<section atr="A"><code val="5" /></section> A
<section atr="C"><code val="5" /></section> C

Upvotes: 1

wst
wst

Reputation: 11771

Instead of where you can apply the constraint in an XPath predicate:

@xml.query('/root/section[code/@val=5]')

Upvotes: 1

Related Questions