Using SQL to query an XML data column

Question

I'm having issues querying XML data stored in a SQL Server 2012 database. The node tree I wish to query is in the following format -

<eForm>
   <page id="equalities" visited="true" complete="true">
      <Belief>
         <item selected="True" value="Christian">Christian</item>
         <item selected="False" value="Jewish">Jewish</item>
         ...
      </Belief>
   </page>
</eForm>

What I would like to do is return the value attribute of the item node where the selected attribute is equal to true. I've read several tutorials on querying XML in SQL but can't seem to get the code right.

Thanks Stu

MarcinJuraszek · Accepted Answer · 2013-08-16 10:07:17Z

4

DEMO

SELECT [value].query('data(eForm/page/Belief/item[@selected="True"]/@value)')
FROM test

answered Aug 16, 2013 at 10:07

MarcinJuraszek

126k15 gold badges200 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

StuartO Over a year ago

Thank you, I wasn't wrapping the XPath in data()

roman Over a year ago

one small note, this would be type xml and not varchar

SearchForKnowledge Over a year ago

What if the XML tag appears multiple times and I would like to select as many times as it appears? stackoverflow.com/questions/26426412/…

roman · Accepted Answer · 2013-08-16 10:12:17Z

1

select
    Value.value('(eForm/page/Belief/item[@selected="True"])[1]/@value', 'nvarchar(max)')    
from test

sql fiddle demo

answered Aug 16, 2013 at 10:12

roman

118k30 gold badges205 silver badges209 bronze badges

Comments

GarethD · Accepted Answer · 2013-08-16 10:08:31Z

DECLARE @T TABLE (X XML);
INSERT @T VALUES ('<eForm>
                       <page id="equalities" visited="true" complete="true">
                          <Belief>
                             <item selected="True" value="Christian">Christian</item>
                             <item selected="False" value="Jewish">Jewish</item>
                          </Belief>
                       </page>
                    </eForm>')

SELECT  item.value('item[1]', 'NVARCHAR(50)')       
FROM    @T
        CROSS APPLY X.nodes('eForm/page/Belief') i (item)
WHERE   item.value('(item[1]/@selected)[1]', 'VARCHAR(5)') = 'true';

N.B.

I actually prefer the other approach posted by MarcinJuraszek, however there could be advantages to the below if you require further data to extract. I originally deleted the answer but there could be situations where this approach is useful so in the interest of showing all options I have undeleted.

Collectives™ on Stack Overflow

Using SQL to query an XML data column

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related