CODEWITHSUNDEEP
sqlsql-servert-sqlquery-optimizationxquery-sql
dnkira
dnkira

Reputation: 362

optimizing sql xml select query

XML

<Data>
  <Language Name="ru-Ru">
    <Item Id="1">value</Item>
    <Item Id="2">value2</Item>
  </Language>
</Data>

Is it possible to run it somehow like this, to simply query:

SELECT id, 
       userId, 
       data.query('//Language[@Name="ru-Ru"]') AS myxml
  FROM UserData
 WHERE (myxml.query('//Item[@Id="9"]') like '%v%')

all in all I need parameter comparison to each item and do not want to repeat

'//Language[@Name="ru-Ru"]'

in each condition

Upvotes: 0

Views: 849

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

You can not reuse aliased columns in where clause. You can use cross apply to do what you want. And you need to cast your where clause to be able to use like.

select
  id, 
  userId, 
  myxml
from @T
  cross apply
    (select data.query('//Language[@Name="ru-Ru"]')) as lang(myxml)
where cast(myxml.query('//Item[@Id="9"]') as varchar(max)) like '%v%'

Upvotes: 2

Related Questions