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
– Gordon Linoff, Commented May 18, 2015 at 12:02
@GordonLinoff, union all returns error either. error is Incorrect syntax near the keyword 'union'. — rakamakafo
– rakamakafo, Commented May 18, 2015 at 12:04

har07 · Accepted Answer · 2015-05-18 12:55:22Z

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, 2015 at 12:55

answered May 18, 2015 at 12:48

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

2 Comments

rakamakafo Over a year ago

Thanks, exactly what I want. Can you please help me to add beginning tags to this xml: <?xml version="1.0" encoding="UTF-8"?>

har07 Over a year ago

@SherzodMuminov You're welcome. Don't know, but you can always search : link1, link2

Collectives™ on Stack Overflow

How to union xml path. Sql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related