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

Mikael Eriksson · Accepted Answer · 2011-05-11 13:48:18Z

6

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

answered May 11, 2011 at 13:48

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dav.id Over a year ago

Hey, my goodness! there are some clever brains out there ! :) thank you for responding, it worked perfectly.. really need to read up more on xquery I think! you wouldn't happen to know any good resources would you? Thanks again !!

Mikael Eriksson Over a year ago

You can have a look here. msdn.microsoft.com/en-us/library/ms345122%28v=SQL.90%29.aspx

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related