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>'
This question is related to
sql-server
tsql
xpath
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"]')
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.
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.
Source: Stackoverflow.com