使用带有 FOR XML AUTO、ELEMENT 的存储过程更改架构

Question

现在我正在

<Header>
  <data1> </data1>
  <Line>
      <data> </data>
      <VLine>
            <data> </data>
            <LUI></LUI>
      </Vline>
  </Line>
</Header>

通过使用以下存储过程

Select * from
EDI834_5010_Header Header
join EDI834_5010_2000 Line on Header.BGN02__TransactionSetIdentifierCode = Line.Id_BGN02__TransactionSetIdentifierCode
left join EDI834_5010_2300_DTPLoop VLine on Line.REF02_MemberSupplementalIdentifier = VLine.Id_REF02__SubscriberIdentifier and Header.BGN02__TransactionSetIdentifierCode = VLine.Id_BGN02__TransactionSetIdentifierCode
left join EDI834_5010_2300_LUILoop LUI on LUI.Id_BGN02__TransactionSetIdentifierCode=Header.BGN02__TransactionSetIdentifierCode and LUI.Id_REF02__SubscriberIdentifier=Line.REF02_MemberSupplementalIdentifier
for xml auto,ELEMENTS

END

获取像这样的 XML 格式的架构但是我需要在 xml 中获取像这样的架构

<Header>
    <data1> </data1>
    <Line>
       <data> </data>
       <VLine>
          <data> </data>
       </Vline>
       <LUI>
             <data> </data>
       </LUI> 
    </Line>
</Header>

我应该如何更改上面的存储过程以获得这样的架构？

Answer 1

试试这个，没有经过我测试，所以里面可能有很多错别字。

select *,
       (select *
        from EDI834_5010_2300_DTPLoop VLine
        where Line.REF02_MemberSupplementalIdentifier = VLine.Id_REF02__SubscriberIdentifier and Header.BGN02__TransactionSetIdentifierCode = VLine.Id_BGN02__TransactionSetIdentifierCode
        for xml auto, elements, type),
       (select *
        from EDI834_5010_2300_LUILoop LUI
        where LUI.Id_BGN02__TransactionSetIdentifierCode=Header.BGN02__TransactionSetIdentifierCode and LUI.Id_REF02__SubscriberIdentifier=Line.REF02_MemberSupplementalIdentifier 
        for xml auto, elements, type)
from EDI834_5010_Header Header
  join EDI834_5010_2000 Line 
    on Header.BGN02__TransactionSetIdentifierCode = Line.Id_BGN02__TransactionSetIdentifierCode
for xml auto, elements

用它来测试子查询是否有效，看起来确实有效。

declare @t1 table (id int)
declare @t2 table (id int)
declare @t3 table (id int)

insert into @t1 values (1),(2)
insert into @t2 values (1),(2)
insert into @t3 values (1),(2)

select *,
       (select *
        from @t2 as t2
        where t1.id = t2.id
        for xml auto, elements, type),
       (select *
        from @t3 as t3
        where t1.id = t3.id
        for xml auto, elements, type)
from @t1 as t1
for xml auto, elements

结果：

<t1>
  <id>1</id>
  <t2>
    <id>1</id>
  </t2>
  <t3>
    <id>1</id>
  </t3>
</t1>
<t1>
  <id>2</id>
  <t2>
    <id>2</id>
  </t2>
  <t3>
    <id>2</id>
  </t3>
</t1>