XPath to fetch SQL XML value

sql-server tsql xpath

108,957

Solution 1

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"]')

Solution 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.

Solution 3

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.

108,957

Author by

joerage

Software Developer working on the InRelease software since its inception four years ago. InRelease has been purchased by Microsoft and is now called Release Management.

Updated on July 30, 2022

Comments

joerage almost 2 years

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>'

joerage over 14 years

Thanks, that helped. How can I return a true or false if a match is found or not? Or return a 0 or 1.
Mike Cialowicz over 14 years

I'm not sure. You could try something like this: IF count(<your xpath query>) >= 1 SELECT 1 ELSE SELECT 0
mistertodd over 11 years

What is @x? (padding comment to 15 characters)
Robin Bennett almost 11 years

You can use the EXIST method. Field.EXIST('xpath')
sirdank over 10 years

I suspect he had DECLARE @x XML = (xml from question); above his statements in the SQL Server query window in order to test using that XML but removed it from his answer because it would have been redundant. All that to say I suspect it is a variable containing the XML from the question.
Lachlan Ennis about 6 years

Both of these links are dead. I suggest looking at the language reference at: docs.microsoft.com/en-us/sql/xquery/…
user482745 almost 6 years

Links are dead.
marc_s almost 6 years

@user482745: the first link was working just fine - updated the second link, works again, too