使用XML路径模式将兄弟姐妹分配给其正确的父级。

Question

我使用PATH模式在Server中生成XML文件，但无法将兄弟关系分配给其正确的父级。

以下是我的可复制的例子：

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')

下面是我尝试过的类似代码：

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')

我得到的结果是：

<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>

我试图实现的是，每个图像都被正确地分配到相应的演示文稿中，如下所示：

<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>

有人能帮我吗？谢谢大家

Answer 1

不必使用inner join表编写另一个Presentation，因为它将从PresentationImage和Presentation表中提取所有的matching data，就像在您的情况下所发生的那样。简单地说，correlate the subquery是：

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')