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

Solution 1:

You could use this query:

DECLARE @x XML=N'
<root><sectionatr="A"><codeval="5" /></section><sectionatr="B"><codeval="6" /></section><sectionatr="C"><codeval="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
------------------------------------------- ----------
<sectionatr="A"><codeval="5" /></section> A
<sectionatr="C"><codeval="5" /></section> C

Solution 2:

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

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