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     lt xml gt     lt box stepId  1  gt       lt components gt         lt component id  2  gt           lt variables gt             lt variable id  3  nom  Server  valeur  DEV1    gt             lt variable id  4  nom  Enabled  valeur  Yes    gt           lt  variables gt         lt  component gt         lt component id  3  gt           lt variables gt             lt variable id  3  nom  Server  valeur  DEV1    gt             lt variable id  4  nom  Enabled  valeur  No    gt           lt  variables gt         lt  component gt       lt  components gt     lt  box gt     lt box stepId  2  gt       lt components gt         lt component id  2  gt           lt variables gt             lt variable id  3  nom  Server  valeur  DEV2    gt             lt variable id  4  nom  Enabled  valeur  Yes    gt           lt  variables gt         lt  component gt         lt component id  3  gt           lt variables gt             lt variable id  3  nom  Server  valeur  DEV2    gt             lt variable id  4  nom  Enabled  valeur  No    gt           lt  variables gt         lt  component gt       lt  components gt     lt  box gt   lt  xml gt

User · Answer

I think the xpath query you want goes something like this:

/xml/box[@stepId="$stepId"]/components/component[@id="$componentId"]/variables/variable[@nom="Enabled" and @valeur="Yes"]

This should get you the variables that are named "Enabled" with a value of "Yes" for the specified $stepId and $componentId. This is assuming that your xml starts with an tag like you show, and not

If the SQL Server 2005 XPath stuff is pretty straightforward (I've never used it), then the above query should work. Otherwise, someone else may have to help you with that.

User · Answer

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

User · Answer

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

[sql-server] XPath to fetch SQL XML value

Examples related to sql-server

Examples related to tsql

Examples related to xpath