sql - 如何联合 xml 路径。数据库

Question

我有一个具有以下结构的查询

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')

我想将它与另一个查询合并，以便结构如下:

    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')

我该怎么做？简单的 union 返回错误。

我尝试将两个查询联合到一个 xml 中，我希望 xml 如下所示:

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

Answer 1

您可以尝试使用 2 个 XML 变量和另一个 FOR XML 来组合它们:

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