Forum Discussion
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
- rodgerkongIron 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
- panos2024bCopper Contributor
Yes thank you. works!