XPath to fetch SQL XML value

Question

Here is my problem: from the following XML that is within a column, I want to know if the value of a variable with the name 'Enabled' is equal to 'Yes' given a step Id and a component Id.

'<xml>   <box stepId="1">     <components>       <component id="2">         <variables>           <variable id="3" nom="Server" valeur="DEV1" />           <variable id="4" nom="Enabled" valeur="Yes" />         </variables>       </component>       <component id="3">         <variables>           <variable id="3" nom="Server" valeur="DEV1" />           <variable id="4" nom="Enabled" valeur="No" />         </variables>       </component>     </components>   </box>   <box stepId="2">     <components>       <component id="2">         <variables>           <variable id="3" nom="Server" valeur="DEV2" />           <variable id="4" nom="Enabled" valeur="Yes" />         </variables>       </component>       <component id="3">         <variables>           <variable id="3" nom="Server" valeur="DEV2" />           <variable id="4" nom="Enabled" valeur="No" />         </variables>       </component>     </components>   </box> </xml>' 

Answer 1

• XQuery Against the xml Data Type
• General XQuery Use Cases
• XQueries Involving Hierarchy

• XQueries Involving Order

• Anything in Michael Rys blog

Update

My recomendation would be to shred the XML into relations and do searches and joins on the resulted relation, in a set oriented fashion, rather than the procedural fashion of searching specific nodes in the XML. Here is a simple XML query that shreds out the nodes and attributes of interest:

select x.value(N'../../../../@stepId', N'int') as StepID   , x.value(N'../../@id', N'int') as ComponentID   , x.value(N'@nom',N'nvarchar(100)') as Nom   , x.value(N'@valeur', N'nvarchar(100)') as Valeur from @x.nodes(N'/xml/box/components/component/variables/variable') t(x) 

However, if you must use an XPath that retrieves exactly the value of interest:

select x.value(N'@valeur', N'nvarchar(100)') as Valeur from @x.nodes(N'/xml/box[@stepId=sql:variable("@stepID")]/     components/component[@id = sql:variable("@componentID")]/        variables/variable[@nom="Enabled"]') t(x) 

If the stepID and component ID are columns, not variables, the you should use sql:column() instead of sql:variable in the XPath filters. See Binding Relational Data Inside XML Data.

And finaly if all you need is to check for existance you can use the exist() XML method:

select @x.exist(   N'/xml/box[@stepId=sql:variable("@stepID")]/     components/component[@id = sql:variable("@componentID")]/       variables/variable[@nom="Enabled" and @valeur="Yes"]')  

Answer 2

I always go back to this article SQL Server 2005 XQuery and XML-DML - Part 1 to know how to use the XML features in SQL Server 2005.

For basic XPath know-how, I'd recommend the W3Schools tutorial.