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 ...
  • rodgerkong's avatar
    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

     

Resources