CODEWITHSUNDEEP
sqlsql-serverxml
Vladimir
Vladimir

Reputation: 45

How to delete duplicate XML data with specific attribute

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

Upvotes: 1

Views: 1148

Answers (1)

har07
har07

Reputation: 89285

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

Upvotes: 1

Related Questions