How to union xml path. Sql

Question

I've a query with following structure

select t1.Col1
      ,t1.Col2
      ,(
         select t2.Col1
               ,t2.Col2
           from #t2 t2
          where t1.Col1 = t2.Col1
            for xml path ('Path1'), root('RootPath1'),Type
        )
  from #t1 t1
   for xml path ('Path2')

I want to union it with another query so that the structure would be as following:

    select t1.Col1
          ,t1.Col2
          ,(
             select t2.Col1
                   ,t2.Col2
               from #t2 t2
              where t1.Col1 = t2.Col1
                for xml path ('Path1'), root('RootPath1'),Type
            )
      from #t1 t1
       for xml path ('Path2')
union
    select t1.Col11
          ,t1.Col22
          ,(
             select t22.Col11
                   ,t22.Col22
               from #t22 t22
              where t11.Col11 = t22.Col11
                for xml path ('Path11'), root('RootPath11'),Type
            )
      from #t11 t11
       for xml path ('Path22')

How can I do this? Simple union returns error.

I try to union two queries into one xml and I expect xml to be as follows:

<Path2>
  <RootPath1>
    <Path1>
      <Col1></Col1>
      <Col2></Col2>
    </Path1>
  </RootPath1>
</Path2>
<Path22>
  <RootPath11>
    <Path11>
      <Col11></Col11>
      <Col22></Col22>
    </Path11>
  </RootPath11>
</Path22>

What error does "simple `union`" return? Does `union all` return the same error? — Gordon Linoff, May 18 '15 at 12:02
@GordonLinoff, `union all` returns error either. error is `Incorrect syntax near the keyword 'union'.` — rakamakafo, May 18 '15 at 12:04

har07 · Accepted Answer · 2015-05-18T12:55:22.170

1

You can try using 2 XML variables and another FOR XML to combine them like so :

declare @path2 XML = (select t1.Col1
          ,t1.Col2
          ,(
             select t2.Col1
                   ,t2.Col2
               from #t2 t2
              where t1.Col1 = t2.Col1
                for xml path ('Path1'), root('RootPath1'),Type
            )
      from #t1 t1
       for xml path ('Path2'))

declare @path22 XML = (select t1.Col11
          ,t1.Col22
          ,(
             select t22.Col11
                   ,t22.Col22
               from #t22 t22
              where t11.Col11 = t22.Col11
                for xml path ('Path11'), root('RootPath11'),Type
            )
      from #t11 t11
       for xml path ('Path22'))

select @path2, @path22
for xml path('')

edited May 18 '15 at 12:55

answered May 18 '15 at 12:48

har07

88,338
12
84
137

Thanks, exactly what I want. Can you please help me to add beginning tags to this xml: `` – rakamakafo May 18 '15 at 12:58
@SherzodMuminov You're welcome. Don't know, but you can always search : [link1](http://stackoverflow.com/questions/9002403/how-to-add-xml-encoding-xml-version-1-0-encoding-utf-8-to-xml-output-in), [link2](http://stackoverflow.com/questions/4184163/sql-server-2008-add-xml-declaration-to-xml-output) – har07 May 18 '15 at 13:04

How to union xml path. Sql

1 Answers1