CODEWITHSUNDEEP
sql-serverxmlxpathxqueryxquery-sql
Tigran
Tigran

Reputation: 1057

XPath on XML in SQLServer

What is wrong with this??? My task is next: I have a XML file and I need to show all cities wheare are no Mick. !!! Cities are not unique. So, for example, I has Mick-London, Tom-London, Charles-Paris. The right answer is Paris only.

Query is for SQL Server 2008. Thank you for help.

This is XML:

declare @x xml
set @x = '<database>
  <persons>
    <person fio="Mick" id="1" />
    <work city="London" size="450" />
    <state>United Kingdom      </state>
  </persons>
  <persons>
    <person fio="Tom" id="8" />
    <work city="London" size="500" />
    <state>UK</state>
  </persons>
  <persons>
    <person fio="Charles" id="9" />
    <work city="Paris" size="450000" />
    <state>Frace</state>
  </persons>
  <persons>
    <person fio="Some_name1" id="10" />
    <work city="Brussels" size="30000" />
    <state>Belgium</state>
  </persons>
  <persons>
    <person fio="Some_name2" id="11" />
    <work city="Munich" size="30000" />
    <state>Germany</state>
  </persons>
</database>'

My current script is:

select @x.query('//work[not(//person[@fio="Mick" and @city = this/../work/@city])]');

Upvotes: 0

Views: 156

Answers (1)

Szymon
Szymon

Reputation: 43023

You can use that query. It has an inner query as the cities are not unique, so it first builds the cities for Mick in the inner query and then gets all other cities in the outer.

select 
    y.work.value('(@city)[1]', 'nvarchar(100)')
from @x.nodes('//persons/work') y(work)
where y.work.value('(@city)[1]', 'nvarchar(100)') not in
    (select 
        x.work.value('(@city)[1]', 'nvarchar(100)')
    from @x.nodes('//persons[person[@fio="Mick"]]/work') x(work))

As requested, this query will use only query:

select @x.query('//work[not(@city = (//persons[person[@fio="Mick"]]/work/@city))]');

Upvotes: 1

Related Questions