Using SQL to query an XML data column

sql xml sql-server-2012 sqlxml
StuartO picture StuartO · Aug 16, 2013 · Viewed 19.4k times · Source

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

Answer

MarcinJuraszek picture MarcinJuraszek · Aug 16, 2013

DEMO

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

Related questions

How can I query a value in SQL Server XML column

I have following XML stored in a XML column (called Roles) in a SQL Server database. <root> <role>Alpha</role> <role>Beta</role> <role>Gamma</role> &…

Read More
Convert Xml to Table SQL Server

I wonder how can i read a xml data and transform it to a table in TSQL? For example: <row> <IdInvernadero>8</IdInvernadero> <IdProducto>3</IdProducto> <IdCaracteristica1>8</IdCaracteristica1> &…

Read More
Use a LIKE statement on SQL Server XML Datatype

If you have a varchar field you can easily do SELECT * FROM TABLE WHERE ColumnA LIKE '%Test%' to see if that column contains a certain string. How do you do that for XML Type? I have the following …

Read More