如果您总是想返回根节点 CommitteeSurveryItem
,即使表为空或没有与 where
子句匹配的行,您也必须将代码重新排列为 XML 子查询,如下所示:
SELECT
(
SELECT
[CommitteeSurveryid],[GroupId],[SurveryTitle],[SurveryDate],[FileManagerid]
FROM [CommitteSurvery] as cs
WHERE (cs.GroupId = 620)
FOR XML PATH('Items'),TYPE
)
FOR XML PATH('CommitteeSurveryItem');
哪个返回...
XML_F52E2B61-18A1-11d1-B105-00805F49916B |
|
插入一些匹配的行数据后,您将看到如预期的 Items
子节点...
insert dbo.CommitteSurvery (CommitteeSurveryid,SurveryTitle,FileManagerid,GroupId) values
(1,'Foo','2021-01-01',1,620),(2,'Bar','2021-01-02',620);
<CommitteeSurveryItem>
<Items>
<CommitteeSurveryid>1</CommitteeSurveryid>
<GroupId>620</GroupId>
<SurveryTitle>Foo</SurveryTitle>
<SurveryDate>2021-01-01</SurveryDate>
<FileManagerid>1</FileManagerid>
</Items>
<Items>
<CommitteeSurveryid>2</CommitteeSurveryid>
<GroupId>620</GroupId>
<SurveryTitle>Bar</SurveryTitle>
<SurveryDate>2021-01-02</SurveryDate>
<FileManagerid>1</FileManagerid>
</Items>
</CommitteeSurveryItem>
本文链接:https://www.f2er.com/3159015.html