Forum Discussion
panos2024b
Nov 07, 2024Copper Contributor
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 ...
- Nov 07, 2024
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
rodgerkong
Nov 07, 2024Iron 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
- panos2024bNov 08, 2024Copper Contributor
Yes thank you. works!