How to concatenate xml path. SQL

I have a request with the 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 combine it with another request so that the structure is like this:

    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 an error.

I am trying to combine two requests into one xml and I expect the xml to be like this:

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

      

+3


source to share


1 answer


You can try using 2 XML variables and another one FOR XML

to concatenate 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('')

      

+1


source







All Articles