Dav.id Dav.id - 1 year ago 119
SQL Question

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

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 Source

This might do what you want.

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

select distinct
    'data(/root/*[local-name(.) = sql:variable("@cf")])') as varchar(20)) as cloth
from designs
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download