FOR XML - getting a column into a root element attribute

Question

Post reply

FOR XML - getting a column into a root element attribute

Torin

SSChasing Mays

Points: 635
More actions
August 18, 2017 at 10:49 am

#324981

Greetings!
I have a 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
That I need to export regularly via SSIS and SFTP.
The receiver of the export specifies this format:
<?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>
This tsql:
SELECT AccountNo,
    PaymentDoneOn,
    PaymentDoneBy,
    InvoiceNumber,
    AmountPaid,
    Comments
FROM xdMOBOthPay
FOR XML PATH ('Payment'), root ('Payments'), ELEMENTS
results in:
<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>
...
Which is very close, but I need to get the data into the Root element (Payments). The column in the table ClientID needs to be included in the root element as the ClientID attribute. The ClientID will be consistent on all rows for the Table .
I have explored several approaches but I am not getting to format required.
thanks
Torin

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Torin SSChasing Mays Points: 635 More actions · Answer 1

the folks at Stack Overflow helped me. Here is the solution:

DECLARE @cMOBId varchar(50);
SELECT @cMOBId = cMOBClientID FROM xdMOBOthPay GROUP BY cMOBClientID;

    WITH XMLNAMESPACES(DEFAULT 'http://someURL.com/core/xml/payment')
    SELECT
        RTRIM(@cMOBId) AS '@MOBId',
        (
            SELECT
                AccountNo,
                PaymentDoneOn,
                PaymentDoneBy,
                InvoiceNumber,
                AmountPaid,
                RTRIM(Comments) AS Comments
            FROM
                xdMOBOthPay AS i
            --WHERE
             -- i.cMOBClientID=@cMOBId        filter not needed should not be more than one ClientID
            FOR
                XML PATH ('Payment'), TYPE
        )
    FOR
        XML PATH ('Payments'), ELEMENTS;

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

Thanks for posting the solution!

I have not worked with using XML Name Spaces inside SQL Server, but now I do know how to do it.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.