sql variable in xml.query path

I have xml data in an xml column I need to return as a row set.  The data looks like this
<NewDataSet>
  <table1>
     <idPerson>1</idPerson>
     <idAccount>1</idAccount>
  </table1>
<table1>
     <idPerson>2</idPerson>
     <idAccount>4</idAccount>
  </table1>
</NewDataSet>


I am trying to write a function that returns a table of the ids contained in the xml data.  The catch is that I want the column to be dynamically set.  Here is what I have tried.

DECLARE @ids TABLE (id int)
DECLARE @data xml
DECLARE @col as varchar(100)
SET @col = 'idPerson'  --This would be passed as a parameter
SELECT @data = DATA from tbStoredGroups WHERE idStoredGroup = 32
SELECT ids.id.value('.', 'nvarchar(max)') from @data.nodes('/NewDataSet/Table1/{sql:variable("col")}') as ids(id)

This however returns the following error.
XQuery [nodes()]: Syntax error near '{', expected a step expression.

I'm using ms sql 2008
Is this possible?
dbashley1Asked:
Who is Participating?
 
gothamiteCommented:
I asked Microsoft this very same question and they said sadly xpath queries always have to be literal strings so you can't do this.

You also need to write the variable with the @ sign inside sql:variable so it should be sql:variable ("@col")

I think you have to do this as dynamic SQL
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.