title: SQL Server SELECT FOR XML How to get two elements at the same level
body: <p>I have two different pieces of XML to put together.</p>
  
  <p>For example, SQL for the first piece looks like this:</p>
  
  <pre><code>SELECT
    *
  FROM
    (
      SELECT 1 AS OrdNum, 'Abc'       AS Name
    ) a
  FOR XML
    AUTO,
    TYPE
  </code></pre>
  
  <p>Once executed, you'll get this:</p>
  
  <pre><code>&lt;a OrdNum="1" Name="Abc" /&gt;
  </code></pre>
  
  <p>The second one is here:</p>
  
  <pre><code>SELECT
    *
  FROM
    (
       SELECT 4  AS Age, 'M'  AS Sex,   'John'      AS FirstName
    ) b
  FOR XML
    AUTO,
    TYPE
  </code></pre>
  
  <p>You'll get this:</p>
  
  <pre><code>&lt;b Age="4" Sex="M" FirstName="John" /&gt;
  </code></pre>
  
  <p>Now I'll put the two pieces together:</p>
  
  <pre><code>SELECT
    *
  FROM
    (
  
      SELECT
        (
          SELECT
            *
          FROM
            (
              SELECT 1 AS OrdNum, 'Abc'       AS Name
            ) a
          FOR XML
            AUTO,
            TYPE
        ) AS aa
  
        ,
  
        (
          SELECT
            *
          FROM
            (
               SELECT 4  AS Age, 'M'  AS Sex,   'John'      AS FirstName
            ) b
          FOR XML
            AUTO,
            TYPE
        ) AS bb
  
    ) Data
  FOR XML
    AUTO,
    ELEMENTS
  </code></pre>
  
  <p>The result is as follows:</p>
  
  <pre><code>&lt;Data&gt;
    &lt;aa&gt;
      &lt;a OrdNum="1" Name="Abc" /&gt;
    &lt;/aa&gt;
    &lt;bb&gt;
      &lt;b Age="4" Sex="M" FirstName="John" /&gt;
    &lt;/bb&gt;
  &lt;/Data&gt;
  </code></pre>
  
  <p>But I do not want to have the elements "aa" and "bb" there. I'd love to get this:</p>
  
  <pre><code>&lt;Data&gt;
    &lt;a OrdNum="1" Name="Abc" /&gt;
    &lt;b Age="4" Sex="M" FirstName="John" /&gt;
  &lt;/Data&gt;
  </code></pre>
  
  <p>But I have no idea how to achieve that.</p>
  
  <p>Any hints?</p>