How to delete duplicate XML data with specific attribute

Question

I have this SQL query

SELECT  r.Address.value('(//City)[1]','VARCHAR(MAX)') "City/@Title" ,   
        (SELECT  r1.FirstName , r1.SecondName 
         FROM Reader r1
         where r.Address.value('(//City)[1]','VARCHAR(MAX)') = r1.Address.value('(//City)[1]','VARCHAR(MAX)')
         FOR XML RAW('Reader'),TYPE) AS "City/Readers"
FROM Reader r
FOR XML PATH(''),ROOT('Cities');

This query produces such data:

<Cities>
  <City Title="New York">
    <Readers>
      <Reader FirstName="JON" SecondName="SHOW" />
      <Reader FirstName="Poll" SecondName="Aeron" />
    </Readers>
  </City>
  <City Title="New York">
    <Readers>
      <Reader FirstName="JON" SecondName="SHOW" />
      <Reader FirstName="Poll" SecondName="Aeron" />
    </Readers>
  </City>
  <City Title="Kharkiv">
    <Readers>
      <Reader FirstName="Slavik" SecondName="Romanov" />
    </Readers>
  </City>
  <City Title="Boca Juniors">
    <Readers>
      <Reader FirstName="Julio " SecondName="Pedro" />
    </Readers>
  </City>
  <City Title="London">
    <Readers>
      <Reader FirstName="Johnny " SecondName="Smith" />
    </Readers>
  </City>
</Cities>

I want to delete duplicate elements, exactly the second node. The query must generate data without the duplicates

You already provided current output of your query. What's missing now is sample input data, can you provide it? Sample in sqlfiddle.com would be perfect — har07
– har07, Commented May 21, 2015 at 23:46
This is my data sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1/1177 — Vladimir
– Vladimir, Commented May 22, 2015 at 8:17

har07 · Accepted Answer · 2015-05-22 09:14:47Z

1

Selecting distinct City before constructing the XML would be more efficient. This is one possible way using Common Table Expression (CTE) :

;WITH Cities
AS(
    SELECT DISTINCT r.Address.value('(//City)[1]','VARCHAR(MAX)') As City
    FROM Reader r
)

SELECT 
    c.City "City/@Title",
    (SELECT  r1.FirstName , r1.SecondName 
     FROM Reader r1
     where c.City = r1.Address.value('(//City)[1]','VARCHAR(MAX)')
     FOR XML RAW('Reader'),TYPE) AS "City/Readers"
FROM Cities c
FOR XML PATH(''),ROOT('Cities');

Fiddle Demo

answered May 22, 2015 at 9:14

har07

89.5k12 gold badges87 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vladimir Over a year ago

and how this query in Oracle?

har07 Over a year ago

That's a whole different question which I don't know the answer. Even if I know, this thread isn't the place to properly answer that question. Try to figure it out your self, then if you get stuck don't hesitate to open a new question. Good luck!

Collectives™ on Stack Overflow

How to delete duplicate XML data with specific attribute

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related