CODEWITHSUNDEEP
sql-serverxml
Saif
Saif

Reputation: 13

LEFT JOIN with FOR XML AUTO, ELEMENTS

I have 3 tables

Master {MasterID, Desc},
Detail {MasterID, DetailID, ItemID},
Items  {ItemID, ItemDesc})

I want to select Master, Detail (as SubElement in Master), Item (in same element of Detail)

SELECT Master.MasterID, Master.Desc, Detail.DetailID, Detail.ItemID, Items.ItemDesc
FROM Master
    LEFT JOIN Detail 
        LEFT JOIN Items 
            ON Detail.ItemID = Items.ItemID 
    ON Master.MasterID = Detail.MasterID
FOR XML AUTO, ELEMENTS

it gives result below:

<Master>
 <MasterID>1</MasterID>
 <Desc>Master1</Desc>
 <Detail>
  <DetailID>1</DetailID>
  <ItemID>1</ItemID>
  <Items><ItemDesc>ItemDesc1</ItemDesc></Items>
 </Detail>
</Master>

But my target is 

<Master>
 <MasterID>1</MasterID>
 <Desc>Master1</Desc>
 <Detail>
  <DetailID>1</DetailID>
  <ItemID>1</ItemID>
  <ItemDesc>ItemDesc1</ItemDesc>
 </Detail>
</Master>'

How can I do that with best practice way?

Upvotes: 2

Views: 4479

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 139000

You can use for xml path like this.

select m.MasterID,
       m.[Desc],
       (select d.DetailID,
               d.ItemID,
               i.ItemDesc
        from Detail as d
          inner join Items as i
            on d.ItemID = i.ItemID
        where d.MasterID = m.MasterID
        for xml path('Detail'), type)
from Master as m
for xml path('Master')

Upvotes: 0

Eric
Eric

Reputation: 95183

Make the detail and item description come back from a single query, like so:

SELECT 
    Master.MasterID, 
    Master.Desc, 
    Detail.DetailID, 
    Detail.ItemID, 
    Detail.ItemDesc 
FROM 
    Master 
    LEFT JOIN (
        select
            d.MasterID,
            d.DetailID,
            d.ItemID,
            i.ItemDesc
        from
            Detail d
            left join items i on
                d.itemid = i.itemid
    ) Detail ON
        Master.MasterID = Detail.MasterID
FOR XML AUTO, ELEMENTS

Upvotes: 3

Related Questions