CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008
Senjuti Mahapatra
Senjuti Mahapatra

Reputation: 2590

Query an XML based on a column value in SQL

I want to query an XML based on a column value in SQL.

My XML is as follows

declare @xmlValue xml = 
'<Accounts>
 <Account>
    <ID>1</ID>
    <Name>Alex</Name>
    <Age>10</Age>
  </Account>
  <Account>
    <ID>2</ID>
    <Name>Richard</Name>
    <Age>12</Age>
  </Account>
  <Account>
    <ID>3</ID>
    <Name>Shane</Name>
    <Age>15</Age>
  </Account>
</Accounts>';

Now I know I can query the XML on Id, if I have a constant Id like this:

select @xmlValue.query('/Accounts[1]/Account[./ID/text()="2"]');

Also, I can use sql:variable like below, in case I have a variable:

declare @ID int
set @ID = 1
select @xmlValue.query('/Accounts[1]/Account[./ID/text()=sql:variable("@ID")]');

Now, I have a table #tmpTable with an xml column (named xmlValue). I want to query the #tmpTable with join to Account table and get the AccountId from there, somewhat like this:

-- This does not work
select xmlValue.query('/Accounts[1]/Account[./ID/text()=a.AccountId]') as XMLResult 
from #tmpTable tmp
inner join Accounts a on tmp.Id = a.Id
where a.Active = 1

How can I achieve this? Any help will be appreciated.

Upvotes: 1

Views: 62

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Try it like this:

select xmlValue.query('/Accounts[1]/Account[./ID/text()=sql:column("a.AccountId")]') as XMLResult 
from #tmpTable tmp
inner join Accounts a on tmp.Id = a.Id
where a.Active = 1

sql:column() is - similar to sql:variable() - the appropriate way to introduce a column's value into an XQuery

Upvotes: 2

Related Questions