title: Output nested XML in SQL Server 2008
body: <p>Trying to write a SQL Server 2008 XML query. I've simplified my example here, but given this data:</p>
  
  <pre><code>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', '&lt;child&gt;mary&lt;/child&gt;&lt;child&gt;ben&lt;/child&gt;')
  insert #childinfo values ('sue', '&lt;child&gt;sally&lt;/child&gt;&lt;child&gt;roger&lt;/child&gt;')
  </code></pre>
  
  <p>I need to output XML that looks like this:</p>
  
  <pre><code>&lt;parentinfo&gt;
  &lt;parent&gt;
      &lt;name&gt;joe&lt;/name&gt;
      &lt;city&gt;yonkers&lt;/city&gt;
      &lt;state&gt;ny&lt;/state&gt;
      &lt;children&gt;
          &lt;child&gt;mary&lt;/child&gt;
          &lt;child&gt;ben&lt;/child&gt;
      &lt;/children&gt;
  &lt;/parent&gt;
  &lt;parent&gt;
      &lt;name&gt;sue&lt;/name&gt;
      &lt;city&gt;sacremento&lt;/city&gt;
      &lt;state&gt;ca&lt;/state&gt;
      &lt;children&gt;
          &lt;child&gt;sally&lt;/child&gt;
          &lt;child&gt;roger&lt;/child&gt;
      &lt;/children&gt;
  &lt;/parent&gt;
  &lt;/parentinfo&gt;
  </code></pre>
  
  <p>This query does that:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>However, I have problems when one of the parents has an extra row in childinfo table. Add this row to the table:</p>
  
  <pre><code>insert #childinfo values ('joe', '&lt;child&gt;chucky&lt;/child&gt;&lt;child&gt;pebbles&lt;/child&gt;')
  </code></pre>
  
  <p>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?</p>
  
  <p>Thanks.</p>