Output nested XML in SQL Server 2008

Question

Trying to write a SQL Server 2008 XML query. I've simplified my example here, but given this data:

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

I need to output XML that looks like this:

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

This query does that:

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

However, I have problems when one of the parents has an extra row in childinfo table. Add this row to the table:

insert #childinfo values ('joe', '<child>chucky</child><child>pebbles</child>')

And my query blows up saying that subquery returns more than 1 value. I need to handle this scenario. Does anyone know how to resolve?

Thanks.

Why is your #childinfo table holding an XML fragment already?? It would be much easier if you had just a regular child table with some fields - and not an XML already.... — marc_s
– marc_s, Commented May 24, 2011 at 16:30

marc_s · Accepted Answer · 2011-05-24 16:32:42Z

10

If you had a "regular" child table - with just a name for the child - and not an XML fragment, you could use this query to get exactly what you're looking for:

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

The resulting output is:

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

answered May 24, 2011 at 16:32

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ca9163d9 Over a year ago

What if I need to nest the whole output inside <Output>....<Output, can it be done without create another layer of select ... from (.....) t for xml path ('Output'), type?

Mikael Eriksson · Accepted Answer · 2011-05-24 16:55:58Z

6

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

Result:

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

answered May 24, 2011 at 16:55

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

Output nested XML in SQL Server 2008

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related