0

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])]');
Tigran
  • 1,049
  • 3
  • 15
  • 31

1 Answers1

1

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))]');
Szymon
  • 42,577
  • 16
  • 96
  • 114