使用特定的 XSD 定义从查询生成 XML

Question

我有以下查询

select A,B,C,D,E,F,G,H from View1 where A = '....'

我想生成以下 XML。是否可以使用Sql Server for XML来执行此操作？或者是否有任何其他方法可以在SQL服务器中实现？

<Root>
  <A>....</A> <!-- Appear only once -->
  <B Id="1">
    <Type C="..." D="...">
      <SubType>
        <Element E="..." F="....">
          <G>...</G>
          <H>...</H>
        </Element>
        .....
      </Subtype>
      ....
    <Type>
    .....
  </B>
  <B>.....

Answer 1

是的

，FOR XML PATH可用于获得所需的结果

declare @test table
(
 A int,
 B int,
 C int,
 D int,
 E int,
 F int,
 G int
)
insert into @test values (1,2,3,4,5,6,7)
insert into @test values (1,3,4,4,5,6,7)
SELECT 
(SELECT top 1 A from @test WHERE A=1 ) as 'A/text()',
( 
      SELECT        
       B as '@id',
       (SELECT C as '@C', D as '@D', 
                E as 'SubType/Element/@E', F as 'SubType/Element/@F',
                G as 'SubType/Element/G/text()',
                F as 'SubType/Element/F/text()' 
         FOR XML PATH('Type'), TYPE
        )
       FROM @test
       WHERE A =1
       FOR XML PATH('B'), TYPE
)
FOR XML PATH(''), ROOT('ROOT')