在SQL Server 2008中输出嵌套的XML

Question

尝试编写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>') 

而我的查询爆炸说子查询返回多个值。我需要处理这种情况。有谁知道如何解决？

谢谢。

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

Answer 2

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>