Forum Discussion

panos2024b's avatar
panos2024b
Copper Contributor
Nov 07, 2024
Solved

duplicate nodes for XML Auto

Hi, 

I have this xml in SQL Server 2022.

select cust.cust_Id,    id,    timestamp,    uid,    [user],    visible,    [version],    changeset,    [action] ,
  (SELECT distinct ref  FROM [nd] as nd where nd.cust_Id = cust.cust_Id and tag.cust_Id  = ND.cust_Id FOR XML raw('nd'), type ),
 k , v  
from cust as cust
join [dbo].[nd] as nd
on ND.cust_Id = cust.cust_Id
join cust_tags as tag
on tag.cust_Id = cust.cust_Id
where cust.cust_Id=23165
for xml   auto

and it gives me this result. As you can see it get duplicates. Apart from the dups in <tag> it''s fine.

<cust cust_Id="23165" id="1132454103" timestamp="2023-01-16T14:25:54" uid="5612131" user="k" visible="1" version="1" changeset="131338301">
  <nd ref="10239832277" />
  <nd ref="10559433751" />
  <nd ref="10559433752" />
  <nd ref="10559433753" />
  <tag k="highcust" v="residential" />
  <tag k="highcust" v="residential" />
  <tag k="highcust" v="residential" />
  <tag k="highcust" v="residential" />
  <tag k="name" v="Artemis" />
  <tag k="name" v="Artemis" />
  <tag k="name" v="Artemis" />
  <tag k="name" v="Artemis" />
  <tag k="source" v="maxar" />
  <tag k="source" v="maxar" />
  <tag k="source" v="maxar" />
  <tag k="source" v="maxar" />
</cust>

 

Now I have modified it like this:

select cust.cust_Id,    id,    timestamp,    uid,    [user],    visible,    [version],    changeset,    [action], 
(SELECT ref  FROM [nd] as nd where nd.cust_Id = cust.cust_Id FOR XML raw('nd'), type
 )    ,
 (SELECT k , v  FROM cust_tags as tag where tag.cust_Id = cust.cust_Id FOR XML raw('tag'), TYPE
 )
from cust as cust
join cust_tags as tag
on tag.cust_Id = cust.cust_Id
join [GIS].[dbo].[nd] as nd
on ND.cust_Id = cust.cust_Id
for xml auto

 

This gives me the structure I want but the node  appears multiple times as a duplicate , 5+ times due to the joins

 

<cust cust_Id="23165" id="1132454103" timestamp="2023-01-16T14:25:54" uid="5612131" user="k" visible="1" version="1" changeset="131338301">
  <nd ref="10239832277" />
  <nd ref="10559433751" />
  <nd ref="10559433752" />
  <nd ref="10559433753" />
  <tag k="highcust" v="residential" />
  <tag k="name" v="Artemis" />
  <tag k="source" v="maxar" />
</cust>
<cust cust_Id="23165" id="1132454103" timestamp="2023-01-16T14:25:54" uid="5612131" user="k" visible="1" version="1" changeset="131338301">
  <nd ref="10239832277" />
  <nd ref="10559433751" />
  <nd ref="10559433752" />
  <nd ref="10559433753" />
  <tag k="highcust" v="residential" />
  <tag k="name" v="Artemis" />
  <tag k="source" v="maxar" />
</cust>

<cust cust_Id="23165" id="1132454103" timestamp="2023-01-16T14:25:54" uid="5612131" user="k" visible="1" version="1" changeset="131338301">
  <nd ref="10239832277" />
  <nd ref="10559433751" />
  <nd ref="10559433752" />
  <nd ref="10559433753" />
  <tag k="highcust" v="residential" />
  <tag k="name" v="Artemis" />
  <tag k="source" v="maxar" />
</cust>

Any idea how i can get rid of the duplicates. Either in the fist sql or the second. Thank you!

  • It's no need to use join when you using in-column sub-queries. 

    The first query could like this:

    select cust.cust_Id,    id,    timestamp,    uid,    [user],    visible,    [version],    changeset,    [action] ,
      (SELECT distinct ref  FROM [nd] as nd where nd.cust_Id = cust.cust_Id and tag.cust_Id  = ND.cust_Id FOR XML raw('nd'), type ),
     k , v  
    from cust as cust
    --join [dbo].[nd] as nd
    --on ND.cust_Id = cust.cust_Id
    join cust_tags as tag
    on tag.cust_Id = cust.cust_Id
    where cust.cust_Id=23165
    for xml   auto

    and the second one would be this

    select cust.cust_Id,    id,    timestamp,    uid,    [user],    visible,    [version],    changeset,    [action], 
    (SELECT ref  FROM [nd] as nd where nd.cust_Id = cust.cust_Id FOR XML raw('nd'), type
     )    ,
     (SELECT k , v  FROM cust_tags as tag where tag.cust_Id = cust.cust_Id FOR XML raw('tag'), TYPE
     )
    from cust as cust
    --join cust_tags as tag
    --on tag.cust_Id = cust.cust_Id
    --join [GIS].[dbo].[nd] as nd
    --on ND.cust_Id = cust.cust_Id
    for xml auto

     

2 Replies

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor

    It's no need to use join when you using in-column sub-queries. 

    The first query could like this:

    select cust.cust_Id,    id,    timestamp,    uid,    [user],    visible,    [version],    changeset,    [action] ,
      (SELECT distinct ref  FROM [nd] as nd where nd.cust_Id = cust.cust_Id and tag.cust_Id  = ND.cust_Id FOR XML raw('nd'), type ),
     k , v  
    from cust as cust
    --join [dbo].[nd] as nd
    --on ND.cust_Id = cust.cust_Id
    join cust_tags as tag
    on tag.cust_Id = cust.cust_Id
    where cust.cust_Id=23165
    for xml   auto

    and the second one would be this

    select cust.cust_Id,    id,    timestamp,    uid,    [user],    visible,    [version],    changeset,    [action], 
    (SELECT ref  FROM [nd] as nd where nd.cust_Id = cust.cust_Id FOR XML raw('nd'), type
     )    ,
     (SELECT k , v  FROM cust_tags as tag where tag.cust_Id = cust.cust_Id FOR XML raw('tag'), TYPE
     )
    from cust as cust
    --join cust_tags as tag
    --on tag.cust_Id = cust.cust_Id
    --join [GIS].[dbo].[nd] as nd
    --on ND.cust_Id = cust.cust_Id
    for xml auto

     

Resources