我有一张 table :

CREATE TABLE [dbo].[xdOthPay]
(
    AccountNo char(10) NOT NULL,
    PaymentDoneOn datetime NULL,
    PaymentDoneBy char(30) NULL,
    InvoiceNumber char(10) NULL,
    AmountPaid decimal(12,3) NULL,
    Comments char(254) NULL,
    ClientID char(50) NULL,
    InstallID char(50) NULL,
    BatchID char(14) NOT NULL 
)

我需要通过 SSIS 和 SFTP 定期导出。

导出的接收方指定此格式:

<?xml version="1.0" encoding="utf-8"?>
<Payments ClientId="10865" xmlns="http://someUrl.com/core/xml/payment">
    <Payment>
        <AccountNo>VALUE</AccountNo>
        <PaymentDoneOn>VALUE</PaymentDoneOn>
        <PaymentDoneBy>VALUE</PaymentDoneBy>
        <InvoiceNumber>VALUE</InvoiceNumber>
        <AmountPaid>VALUE</AmountPaid>
        <Comments>VALUE</Comments>
    </Payment>
</Payments>

这是我的 T-SQL 语句:

SELECT 
    AccountNo,
    PaymentDoneOn,
    PaymentDoneBy,
    InvoiceNumber,
    AmountPaid,
    Comments
FROM 
    xdMOBOthPay
FOR XML PATH ('Payment'), ROOT ('Payments'), ELEMENTS

这导致:

<Payments>
    <Payment>
        <AccountNo> 972140</AccountNo>
        <PaymentDoneOn>2017-06-08T00:00:00</PaymentDoneOn>
        <PaymentDoneBy>R Entry BA13177 </PaymentDoneBy>
        <InvoiceNumber> 3804</InvoiceNumber>
        <AmountPaid>468.000</AmountPaid>
        <Comments>Non-Onl Payment </Comments>
    </Payment>
...

这非常接近，但我需要将数据放入根元素(付款)。表 ClientID 中的列需要作为 ClientID 属性包含在根元素中。 ClientID 将在表的所有行上保持一致。

我已经探索了几种方法，但我没有达到要求的格式。

谢谢 都灵

最佳答案

我建议您不要使用固定宽度的 char 列，而应使用 varchar 列。该示例添加了 namespace 并使 XML 略有不同，以根据需要获取所有内容。该示例针对一个特定的 ClientID 执行此操作，我想这就是您所需要的。

结果有一件不幸的事情，命名空间也应用于 Payment 元素。如果这 Not Acceptable ，则有一种变通方法，您可以在没有 XML 命名空间的情况下生成，并且可以应用字符串函数来插入命名空间属性。

CREATE TABLE #xdOthPay(
    AccountNo char(10) NOT NULL,
    PaymentDoneOn datetime NULL,
    PaymentDoneBy char(30) NULL,
    InvoiceNumber char(10) NULL,
    AmountPaid decimal(12,3) NULL,
    Comments char(254) NULL,
    ClientID char(50) NULL,
    InstallID char(50) NULL,
    BatchID char(14) NOT NULL );

INSERT INTO #xdOthPay(AccountNo,AmountPaid,BatchID,ClientID,Comments,InstallID,InvoiceNumber,PaymentDoneBy,PaymentDoneOn)
VALUES(972140,468,'bath-id',10865,'comments','install-id','invoicenr','paymentdoneby','20170101'),
      (972141,468,'bath-id',10865,'comments','install-id','invoicenr','paymentdoneby','20170101');

WITH XMLNAMESPACES(DEFAULT 'http://someUrl.com/core/xml/payment')
SELECT 
    10865 AS '@clientId',
    (
        SELECT 
            AccountNo,
            PaymentDoneOn,
            PaymentDoneBy,
            InvoiceNumber,
            AmountPaid,
            Comments
        FROM 
            #xdOthPay AS i
        WHERE
            i.ClientID=10865
        FOR 
            XML PATH ('Payment'), TYPE
    )
FOR 
    XML PATH ('Payments'), ELEMENTS;

DROP TABLE #xdOthPay;

结果:

<Payments xmlns="http://someUrl.com/core/xml/payment" clientId="10865">
  <Payment xmlns="http://someUrl.com/core/xml/payment">
    <AccountNo>972140    </AccountNo>
    <PaymentDoneOn>2017-01-01T00:00:00</PaymentDoneOn>
    <PaymentDoneBy>paymentdoneby                 </PaymentDoneBy>
    <InvoiceNumber>invoicenr </InvoiceNumber>
    <AmountPaid>468.000</AmountPaid>
    <Comments>comments                                                                                                                                                                                                                                                      </Comments>
  </Payment>
  <Payment xmlns="http://someUrl.com/core/xml/payment">
    <AccountNo>972141    </AccountNo>
    <PaymentDoneOn>2017-01-01T00:00:00</PaymentDoneOn>
    <PaymentDoneBy>paymentdoneby                 </PaymentDoneBy>
    <InvoiceNumber>invoicenr </InvoiceNumber>
    <AmountPaid>468.000</AmountPaid>
    <Comments>comments                                                                                                                                                                                                                                                      </Comments>
  </Payment>
</Payments>

关于sql-server - TSQL - FOR XML 添加数据到根节点，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/45784641/