正在尝试编写SQL Server 2008 XML查询。我在这里简化了我的例子，但给出了以下数据：
create table #parentinfo (name varchar(50), city varchar(50), state varchar(50))
insert #parentinfo values ('joe', 'yonkers', 'ny')
insert #parentinfo values ('sue', 'sacremento', 'ca')
-- parentname is foreign key to name field in #parentinfo
create table #childinfo (parentname varchar(50), childxml xml)
insert #childinfo values ('joe', '<child>mary</child><child>ben</child>')
insert #childinfo values ('sue', '<child>sally</child><child>roger</child>')
我需要输出如下所示的XML：
<parentinfo>
<parent>
<name>joe</name>
<city>yonkers</city>
<state>ny</state>
<children>
<child>mary</child>
<child>ben</child>
</children>
</parent>
<parent>
<name>sue</name>
<city>sacremento</city>
<state>ca</state>
<children>
<child>sally</child>
<child>roger</child>
</children>
</parent>
</parentinfo>
此查询的作用是：
select name,
city,
state,
(select childxml
from #childinfo c
where c.parentname = p.name
--for xml auto, type, elements
) as 'children'
from #parentinfo p
group by name, city, state
for xml path('parent')
然而，当其中一个家长在childinfo表中多了一行时，我会遇到问题。将此行添加到表中：
insert #childinfo values ('joe', '<child>chucky</child><child>pebbles</child>')
我的查询失败了，说子查询返回的值超过1。我需要处理这种情况。有人知道如何解决吗？
谢谢。
如果您有一个"常规"子表-只有子表的名称-而不是XML片段，则可以使用此查询来获得您想要的内容：
create table #parentinfo (name varchar(50), city varchar(50), state varchar(50))
insert #parentinfo values ('joe', 'yonkers', 'ny')
insert #parentinfo values ('sue', 'sacremento', 'ca')
-- parentname is foreign key to name field in #parentinfo
create table #childinfo (parentname varchar(50), childname VARCHAR(50))
insert #childinfo values ('joe', 'mary')
insert #childinfo values ('joe', 'ben')
insert #childinfo values ('sue', 'sally')
insert #childinfo values ('sue', 'roger')
insert #childinfo values ('joe', 'chucky')
insert #childinfo values ('joe', 'pebbles')
select name,
city,
state,
(select childname as 'child'
from #childinfo c
where c.parentname = p.name
order by childname
for xml PATH(''), type
) as 'children'
from #parentinfo p
group by name, city, state
for xml path('parent'), root('parentinfo')
结果输出为：
<parentinfo>
<parent>
<name>joe</name>
<city>yonkers</city>
<state>ny</state>
<children>
<child>ben</child>
<child>chucky</child>
<child>mary</child>
<child>pebbles</child>
</children>
</parent>
<parent>
<name>sue</name>
<city>sacremento</city>
<state>ca</state>
<children>
<child>roger</child>
<child>sally</child>
</children>
</parent>
</parentinfo>
select name,
city,
state,
(select childxml as '*'
from #childinfo c
where c.parentname = p.name
for xml path(''), type
) as 'children'
from #parentinfo p
group by name, city, state
for xml path('parent')
结果：
<parent>
<name>joe</name>
<city>yonkers</city>
<state>ny</state>
<children>
<child>mary</child>
<child>ben</child>
<child>chucky</child>
<child>pebbles</child>
</children>
</parent>
<parent>
<name>sue</name>
<city>sacremento</city>
<state>ca</state>
<children>
<child>sally</child>
<child>roger</child>
</children>
</parent>