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

score 4 · Accepted Answer · answered Aug 16 '13 at 10:07

4

DEMO

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

answered Aug 16 '13 at 10:07

MarcinJuraszek

124,003
15
196
263

Thank you, I wasn't wrapping the XPath in data() – StuartO Aug 16 '13 at 10:12
1

one small note, this would be type xml and not varchar – Roman Pekar Aug 16 '13 at 10:13
1

What if the XML tag appears multiple times and I would like to select as many times as it appears? http://stackoverflow.com/questions/26426412/how-to-ensure-the-sql-is-able-to-read-all-xml-tag-data – SearchForKnowledge Oct 17 '14 at 13:45

score 1 · Answer 2 · answered Aug 16 '13 at 10:12

1

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

sql fiddle demo

answered Aug 16 '13 at 10:12

Roman Pekar

107,110
28
195
197

score 0 · Answer 3 · answered Aug 16 '13 at 10:08

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.

Using SQL to query an XML data column

3 Answers3