Using a variable in a SQL Server 2005 stored procedure with XQuery

I'm working with the following XML

<AEDControl ControlGU="17fed98c-8128-4c6b-9b50-3dbe73889b9d" 
            ControlLabel="Posting Status" 
            TypeGU="6b4d08b1-6340-450c-beae-517b7d84e717" 
            ControlDescription="">
  <Elements>
    <Element ElementGU="2da346d1-2e05-4aa3-9bae-5aa9b3b75d5c" 
             Label="Active" 
             IsDefault="false"/>
    <Element ElementGU="fa8966fc-c796-4482-9ee1-f619910dc86e" 
             Label="Closed" 
             IsDefault="false"/>
    <Element ElementGU="d701a7d7-c3bd-496b-8d4b-b854a6937c3a" 
             Label="Filled" 
             IsDefault="false"/>
    <Element ElementGU="75af1941-f14f-4b7e-9f1e-5b6852c4a4f7" 
             Label="New" 
             IsDefault="false"/>
    <Element ElementGU="aa54e387-608e-4758-b4f2-c1dc485a5576" 
             Label="Pending" 
             IsDefault="true"/>
    <Element ElementGU="210aef5c-e4cf-4987-815f-0e4274b45e08" 
             Label="Scratch" 
             IsDefault="false"/>
 </Elements>

I'm trying to query from a stored procedure t开发者_如何学JAVAo pull back a label on the element that has a specific ElementGU

My stored procedure looks like this:

SELECT 
   CAST(CONTROL_XML.query('data(/AEDControl/Elements/Element/@Label)') as varchar(100)) as ControlLabel 
FROM 
   Control
WHERE 
   CONTROL_XML.exist('/AEDControl/Elements/Element[@ElementGU = sql:variable("@SelectedValueGU")]') = 1

where ElementGU is a passed in uniqueidentifier field.

I appear to be having no luck with this. I've read that you can't do this kind of dynamic query with XQuery, but at the same time, the slq:variable() call is part of XQuery, so is there anyone out there that can clear this up to me?

I'm still fairly new on the XQuery front.

You need to approach this a bit differently: since you have a list of <Element> nodes, I would suggest you create a list of nodes and then pick the right one from that list - something like this:

SELECT 
    AED.Element.value('(@Label)[1]', 'varchar(100)') as ControlLabel 
FROM
    Control
CROSS APPLY
    Control_XML.nodes('/AEDControl/Elements/Element') AS AED(Element)
WHERE
AED.Element.value('(@ElementGU)[1]', 'uniqueidentifier') = @SelectedValueGU

I don't know how you want to select from your base table - whether you want to have a WHERE clause or something - but the CROSS APPLY basically takes the XML field and creates a "pseudo-table" called AED.Element from the nodes given in the XPath expression, and cross-applies those to the base table. So now, for each entry in Control and each <Element> node in those rows, you get one row of data.

In that row, you can now pick out those rows where the @ElementGU value corresponds to the value you passed in, and for those XML nodes where this is the case, you then select the value of the @Label attribute

I think this XPath (with sql:variable() extension function) should work:

/AEDControl
 /Elements
  /Element[@ElementGU = sql:variable("@SelectedValueGU")]
   /@Label