Assign the siblings to its right parents by using XML PATH mode

Question

I was generating XML file in SQL Server using PATH mode, but was unable to assign the siblings to its right parents.

Here is my reproducible example:

CREATE TABLE Conference(
ID CHAR(1),
ConferenceTitle CHAR(20),
Host CHAR(20)
)

INSERT INTO Conference
VALUES
('1','Fruit','Amy')


CREATE TABLE Presentation(
ConferenceID CHAR(1),
ID CHAR(1),
PresentationTitle CHAR(20),
Author CHAR(20)
)

INSERT INTO Presentation
VALUES
('1','1','apple','a1'),
('1','2','banana','a2'),
('1','3','carrot','a3'),
('1','4','durian','a4')


CREATE TABLE PresentationImage(
PresentationID CHAR(1),
ImageID CHAR(1),
ImageDescription CHAR(20)
)

INSERT INTO PresentationImage
VALUES
('1','1','apple1'),
('1','2','apple2'),
('2','1','banana1'),
('3','1','carrot1'),
('3','2','carrot2'),
('3','3','carrot3'),
('4','1','durian1'),
('4','2','durian2')

And below is the similar code I've tried:

SELECT p.ID AS "@ID", c.ConferenceTitle as 'ConTitle', p.PresentationTitle as 'PresTitle', 
       p.Author as 'Author',
    (SELECT pi.ImageID AS "@imgID", CAST(pi.ImageDescription AS nvarchar(max)) AS 'ImageData'
    FROM PresentationImage as pi
    INNER JOIN Presentation AS p ON pi.PresentationID = p.ID
    FOR  XML PATH ('Image'),
    ROOT ('Images'), TYPE)
FROM Conference c
INNER join Presentation as p ON  c.ID = p.ConferenceID
FOR XML PATH ('Presentation'),
ROOT ('Conference')

The result I get is:

<Conference>
<Presentation ID="1">
<ConTitle>Fruit               </ConTitle>
<PresTitle>apple               </PresTitle>
<Author>a1                  </Author>
<Images>
<Image imgID="1"><ImageData>apple1              </ImageData></Image>
<Image imgID="2"><ImageData>apple2              </ImageData></Image>
<Image imgID="1"><ImageData>banana1             </ImageData></Image>
<Image imgID="1"><ImageData>carrot1             </ImageData></Image>
<Image imgID="2"><ImageData>carrot2             </ImageData></Image>
<Image imgID="3"><ImageData>carrot3             </ImageData></Image>
<Image imgID="1"><ImageData>durian1             </ImageData></Image>
<Image imgID="2"><ImageData>durian2             </ImageData></Image>
</Images>
</Presentation>

<Presentation ID="2">
<ConTitle>Fruit               </ConTitle>
<PresTitle>banana              </PresTitle>
<Author>a2                  </Author>
<Images>
<Image imgID="1"><ImageData>apple1              </ImageData></Image>
<Image imgID="2"><ImageData>apple2              </ImageData></Image>
<Image imgID="1"><ImageData>banana1             </ImageData></Image>
<Image imgID="1"><ImageData>carrot1             </ImageData></Image>
<Image imgID="2"><ImageData>carrot2             </ImageData></Image>
<Image imgID="3"><ImageData>carrot3             </ImageData></Image>
<Image imgID="1"><ImageData>durian1             </ImageData></Image>
<Image imgID="2"><ImageData>durian2             </ImageData></Image>
</Images>
</Presentation>

<Presentation ID="3">
<ConTitle>Fruit               </ConTitle>
<PresTitle>carrot              </PresTitle>
<Author>a3                  </Author>
<Images>
<Image imgID="1"><ImageData>apple1              </ImageData></Image>
<Image imgID="2"><ImageData>apple2              </ImageData></Image>
<Image imgID="1"><ImageData>banana1             </ImageData></Image>
<Image imgID="1"><ImageData>carrot1             </ImageData></Image>
<Image imgID="2"><ImageData>carrot2             </ImageData></Image>
<Image imgID="3"><ImageData>carrot3             </ImageData></Image>
<Image imgID="1"><ImageData>durian1             </ImageData></Image>
<Image imgID="2"><ImageData>durian2             </ImageData></Image>
</Images>
</Presentation>

<Presentation ID="4">
<ConTitle>Fruit               </ConTitle>
<PresTitle>durian              </PresTitle>
<Author>a4                  </Author>
<Images>
<Image imgID="1"><ImageData>apple1              </ImageData></Image>
<Image imgID="2"><ImageData>apple2              </ImageData></Image>
<Image imgID="1"><ImageData>banana1             </ImageData></Image>
<Image imgID="1"><ImageData>carrot1             </ImageData></Image>
<Image imgID="2"><ImageData>carrot2             </ImageData></Image>
<Image imgID="3"><ImageData>carrot3             </ImageData></Image>
<Image imgID="1"><ImageData>durian1             </ImageData></Image>
<Image imgID="2"><ImageData>durian2             </ImageData></Image>
</Images>
</Presentation>

</Conference>

What I was trying to achieve is that each Image is assigned correctly to its corresponding presentations like below:

<Conference>
<Presentation ID="1">
<ConTitle>Fruit               </ConTitle>
<PresTitle>apple               </PresTitle>
<Author>a1                  </Author>
<Images>
<Image imgID="1"><ImageData>apple1              </ImageData></Image>
<Image imgID="2"><ImageData>apple2              </ImageData></Image>
</Images>
</Presentation>

<Presentation ID="2">
<ConTitle>Fruit               </ConTitle>
<PresTitle>banana              </PresTitle>
<Author>a2                  </Author>
<Images>
<Image imgID="1"><ImageData>banana1             </ImageData></Image>
</Images>
</Presentation>

<Presentation ID="3">
<ConTitle>Fruit               </ConTitle>
<PresTitle>carrot              </PresTitle>
<Author>a3                  </Author>
<Images>
<Image imgID="1"><ImageData>carrot1             </ImageData></Image>
<Image imgID="2"><ImageData>carrot2             </ImageData></Image>
<Image imgID="3"><ImageData>carrot3             </ImageData></Image>
</Images>
</Presentation>

<Presentation ID="4">
<ConTitle>Fruit               </ConTitle>
<PresTitle>durian              </PresTitle>
<Author>a4                  </Author>
<Images>
<Image imgID="1"><ImageData>durian1             </ImageData></Image>
<Image imgID="2"><ImageData>durian2             </ImageData></Image>
</Images>
</Presentation>

</Conference>

Can someone help me out on this? Thanks everyone

Answer 1

Need not write another inner join with Presentation table as it will pull all the matching data from PresentationImage and Presentation table as is happening in the your case. Simply correlate the subquery as:

SELECT p.ID AS "@ID", c.ConferenceTitle as 'ConTitle', p.PresentationTitle as 'PresTitle', 
       p.Author as 'Author',
    (SELECT pi.ImageID AS "@imgID", CAST(pi.ImageDescription AS nvarchar(max)) AS 'ImageData'
    FROM PresentationImage as pi
    --INNER JOIN Presentation AS p ON pi.PresentationID = p.ID
    where pi.PresentationID = p.ID
    FOR  XML PATH ('Image'),
    ROOT ('Images'), TYPE)
FROM Conference c
INNER join Presentation as p ON  c.ID = p.ConferenceID
FOR XML PATH ('Presentation'),
ROOT ('Conference')