SQL XML Xquery data query using a variable in the node selection?

Question

I am obviously missing something right in front of me, however I have this SQL 2008 XML query as follows:

select distinct cast(customFields_xml.query('data(/root/cf_item_type)') as varchar) as c1
from designs

.. what I am actually trying to achieve is to make the "cf_item_type" a variable, because I want to pass in the node as a param to a proc..

So in reality, I am trying to end up with something like:

(@cf would be passed as a param, but declaring for example use)

declare @cf varchar
set @cf='cf_item_type'
select distinct cast(customFields_xml.query('data(/root/@cf)') as varchar) as cloth from designs

.. So you can see I am trying to use the @cf variable within the xquery statement..

Any pointers/help would be great!!

Answer 1

This might do what you want.

declare @cf varchar(20)
set @cf='cf_item_type'

select distinct
  cast(customFields_xml.query(
    'data(/root/*[local-name(.) = sql:variable("@cf")])') as varchar(20)) as cloth
from designs