sql - 将 XML 节点附加到 SQL 中的现有树

Question

我有一个将结果生成为 XML 列类型的 SQL 查询。我想更改该查询，以便通过将其附加到标题/开头来生成以下输出：

预期输出：

<Root>
   <Header>
      <MessageID>044</MessageID>
      <TotalNumberOfTransactionsDeclared>1047</TotalNumberOfTransactionsDeclared>
      <TotalAmountWithoutVAT>25771.79</TotalAmountWithoutVAT>
   </Header>
   <Entities>
      <Entity>
         <CustomerVATID>100050301900003</CustomerVATID>
         <Leases>
            <Lease>
               <LeaseIdentifier>4374</LeaseIdentifier>
               <Invoices>
                  <Invoice>
                    ...
                  </Invoice>
               </Invoices>
            </Lease>
         </Leases>
      </Entity>
   </Entities>
</Root>

现在无论发票之后的节点如何，我都能得到结果。<Invoices>但是，我无法在标记之前添加根节点。我怎样才能做到这一点？

标签： sqlsql-serverxmlsql-server-2012

Answer 1

如果您想完全控制您的 XML 输出，那么您可以使用显式和元素选项。这是很多工作，但您可以定义每个标签和值。

如何EXPLICIT和ELEMENT工作

样本数据

create table MyCustomers
(
  CustomerId int,
  Name nvarchar(10)
);

insert into MyCustomers (CustomerId, Name) values
(1, 'Alfred'),
(2, 'Batman');

create table MyInvoices
(
  CustomerId int,
  OrderId int,
  Amount money
);

insert into MyInvoices (CustomerId, OrderId, Amount) values
(1, 101, 12.34),
(1, 102, 56.78),
(2, 201, 90.12);

解决方案

即使对于这个简单的示例，这很快也会变得很长。

-- customer 1 invoice XML
declare @CustomerId int = 1;

--root
select 1 as Tag,
       0 as Parent,
       null as [Root!1],
       null as [Header!2],
       null as [Customer!3!Id],
       null as [Customer!3!Name!ELEMENT],
       null as [Customer!3!InvoiceCount!ELEMENT],
       null as [Invoices!4],
       null as [Invoice!5!OrderId!ELEMENT],
       null as [Invoice!5!Amount!ELEMENT]

  union all

--root/header
select 2,
       1,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null

  union all

--root/header/customer
select 3,
       2,
       null,
       null,
       mc.CustomerId,
       mc.Name,
       count(1) as InvoiceCount,
       null,
       null,
       null
from MyCustomers mc
left join MyInvoices mi
  on mi.CustomerId = mc.CustomerId
where mc.CustomerId = @CustomerId
group by mc.CustomerId, mc.Name

  union all

--root/invoices
select 4,
       1,
       null,
       null,
       null,
       null,
       null,
       null,
       null,
       null

  union all

--root/invoices/invoice
select 5,
       4,
       null,
       null,
       mi.CustomerId,
       null,
       null,
       null,
       mi.OrderId,
       mi.Amount
from MyInvoices mi
where mi.CustomerId = @CustomerId

for xml explicit;

结果

<Root><Header><Customer Id="1"><Name>Alfred</Name><InvoiceCount>2</InvoiceCount></Customer></Header><Invoices><Invoice><OrderId>101</OrderId><Amount>12.3400</Amount></Invoice><Invoice><OrderId>102</OrderId><Amount>56.7800</Amount></Invoice></Invoices></Root>

或者使用一些格式

<Root>
   <Header>
      <Customer Id="1">
         <Name>Alfred</Name>
         <InvoiceCount>2</InvoiceCount>
      </Customer>
   </Header>
   <Invoices>
      <Invoice>
         <OrderId>101</OrderId>
         <Amount>12.3400</Amount>
      </Invoice>
      <Invoice>
         <OrderId>102</OrderId>
         <Amount>56.7800</Amount>
      </Invoice>
   </Invoices>
</Root>

在行动中看到它：小提琴。

---

构建预期结果

解决方案

此解决方案示例重现了您的大部分预期输出。我使用所有常量值来保持简单。还有其他注释可以突出显示结构。

--root
select 1 as Tag, -- 1 = root
       0 as Parent,
       null as [Root!1], -- 1
       null as [Header!2!MessageId!ELEMENT],
       null as [Header!2!TotalNumberOfTransactionsDeclared!ELEMENT],
       null as [Entities!3],
       null as [Entity!4!CustomerVATId!ELEMENT],
       null as [Leases!5],
       null as [Lease!6!LeaseIdentifier!ELEMENT],
       null as [Invoices!7],
       null as [Invoice!8!OrderId!ELEMENT]

  union all

--root/header
select 2, -- 2 = header
       1,
       null,
       '044',  -- 2!MessageID
       '1047', -- 2!TotalNumberOfTransactionsDeclared
       null, null, null, null, null, null

  union all

--root/entities
select 3, -- 3 = entities
       1,
       null, null, null,
       null, -- 3
       null, null, null, null, null

  union all

--root/entities/entity
select 4, -- 4 = entity
       3,
       null, null, null, null,
       '100050301900003', -- 4!CustomerVATId
       null, null, null, null

  union all

--root/entities/entity/leases
select 5, -- 5 = leases
       4,
       null, null, null, null, null,
       null, -- 5
       null, null, null

  union all

--root/entities/entity/leases/lease
select 6, -- 6 = lease
       5,
       null, null, null, null, null, null,
       '4374', -- 6!LeaseIdentifier
       null, null

  union all

--root/entities/entity/leases/invoices
select 7, -- 7 = invoices
       6,
       null, null, null, null, null, null, null,
       null, -- 7
       null
       
  union all

--root/entities/entity/leases/invoices/invoice
select 8, -- 8 = invoices
       7,
       null, null, null, null, null, null, null, null,
       'INV0001' -- 8!OrderId

for xml explicit;

结果

<Root><Header><MessageId>044</MessageId><TotalNumberOfTransactionsDeclared>1047</TotalNumberOfTransactionsDeclared></Header><Entities><Entity><CustomerVATId>100050301900003</CustomerVATId><Leases><Lease><LeaseIdentifier>4374</LeaseIdentifier><Invoices><Invoice><OrderId>INV0001</OrderId></Invoice></Invoices></Lease></Leases></Entity></Entities></Root>

格式化后：

<Root>
   <Header>
      <MessageId>044</MessageId>
      <TotalNumberOfTransactionsDeclared>1047</TotalNumberOfTransactionsDeclared>
   </Header>
   <Entities>
      <Entity>
         <CustomerVATId>100050301900003</CustomerVATId>
         <Leases>
            <Lease>
               <LeaseIdentifier>4374</LeaseIdentifier>
               <Invoices>
                  <Invoice>
                     <OrderId>INV0001</OrderId>
                  </Invoice>
               </Invoices>
            </Lease>
         </Leases>
      </Entity>
   </Entities>
</Root>