Using xml with SQL to evaluate a dynamic variable

Question

This is a unique problem..I think. So my goal is to input a variable and get a row from my column. Let me explain a little with the code im doing.

 SELECT
  pref.query('Database/text()') as PersonSkills,
  pref.query('FillQuery/text()') as PersonSkills,
  pref.query('TabText/text()') as PersonSkills,
  pref.query('TooltipText/text()') as PersonSkills
  FROM  table CROSS APPLY
  Tag.nodes('/Root/Configuration/TaskSelectorControl/QueueSelector') AS People(pref)

this works fine. However what I need to do is pass in the last part, the queue selector as a variables.

 DECLARE @Xml XML
 DECLARE @AttributeName VARCHAR(MAX) = 'QueueSelector'
 SELECT
 pref.query('Database/text()') as PersonSkills,
  pref.query('FillQuery/text()') as PersonSkills,
  pref.query('TabText/text()') as PersonSkills,
  pref.query('TooltipText/text()') as PersonSkills
  FROM  table CROSS APPLY
Tag.nodes('/Root/Configuration/TaskSelectorControl[@Name=sql:variable("@AttributeName")]
 ') AS People(pref)

this doesnt work, any ideas why?

Well, I kinda lied. the bottom works, however it returns an empty dataset

wst · Accepted Answer · 2014-03-18 19:14:14Z

1

/Root/Configuration/TaskSelectorControl/QueueSelector

is not equivalent to:

/Root/Configuration/TaskSelectorControl[@Name='QueueSelector']

The above XPath selects <TaskSelectorControl Name="QueueSelector">, not <QueueSelector> children of <TaskSelectorControl>.

You could either do this in XPath:

/Root/Configuration/TaskSelectorControl/*[local-name(.)=sql:variable("@AttributeName")]

Or it might be simpler to concat prior to evaluating:

'/Root/Configuration/TaskSelectorControl/' + @AttributeName

edited Mar 18, 2014 at 19:14

answered Mar 18, 2014 at 19:07

wst

11.8k1 gold badge27 silver badges41 bronze badges

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

Collectives™ on Stack Overflow

Using xml with SQL to evaluate a dynamic variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related