CODEWITHSUNDEEP
sqlsql-server-2005xpathxqueryxquery-sql
Adam
Adam

Reputation: 818

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 to 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.

Upvotes: 1

Views: 475

Answers (2)

user357812
user357812

Reputation:

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

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

Upvotes: 0

marc_s
marc_s

Reputation: 755531

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

Upvotes: 1

Related Questions