2

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

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
Dav.id
  • 2,757
  • 3
  • 45
  • 57

1 Answers1

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
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 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 !! – Dav.id May 11 '11 at 22:47
  • 1
    You can have a look here. http://msdn.microsoft.com/en-us/library/ms345122%28v=SQL.90%29.aspx – Mikael Eriksson May 12 '11 at 05:15