0

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

Dimitre Novatchev
  • 240,661
  • 26
  • 293
  • 431
dnkira
  • 362
  • 3
  • 8

1 Answers1

2

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%'
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 2
    And to help with your performance, you should try to avoid the `//Language` XPath expression - try using a more specific expression with the `//` in it! Something like : `/Data/Language` – marc_s Apr 09 '11 at 20:19