0

I am trying to make XML using the query below, but the problem is when I add namespace, it is added to every sub-element, but I need it only on the root element.

Example of XML creation without namespace:

-- Declare variables for message data
DECLARE @MsgId VARCHAR(50) = '123456789';
DECLARE @CreDtTm DATETIME = '2023-07-26T12:34:56';
DECLARE @NbOfTxs INT = 1;
DECLARE @CtrlSum DECIMAL(18, 2) = 1000.00;
DECLARE @InitgPtyNm VARCHAR(100) = 'Sender Company Name';
DECLARE @InstrId VARCHAR(50) = 'Payment123';
DECLARE @EndToEndId VARCHAR(50) = 'EndToEnd123';
DECLARE @Amt DECIMAL(18, 2) = 1000.00;
DECLARE @Ccy VARCHAR(3) = 'EUR';
DECLARE @CdtrAgtBIC VARCHAR(11) = 'AAAABBBB';
DECLARE @CdtrNm VARCHAR(100) = 'Recipient Name';

-- Generate the PACS.008 XML document
SELECT 
    (
    SELECT @MsgId AS 'MsgId',
           @CreDtTm AS 'CreDtTm',
           @NbOfTxs AS 'NbOfTxs',
           @CtrlSum AS 'CtrlSum',
           InitgPty.Nm AS 'InitgPty',
           PmtId.InstrId AS 'PmtId/InstrId',
           PmtId.EndToEndId AS 'PmtId/EndToEndId',
           Amt.InstdAmt AS 'Amt',
           CdtrAgt.BIC AS 'CdtrAgt/FinInstnId/BIC',
           Cdtr.Nm AS 'Cdtr/Nm'
    FROM (SELECT @InitgPtyNm AS Nm) AS InitgPty
    CROSS APPLY (SELECT @InstrId AS InstrId, @EndToEndId AS EndToEndId) AS PmtId
    CROSS APPLY (SELECT @Amt AS InstdAmt, @Ccy AS Ccy ) AS Amt
    CROSS APPLY (SELECT @CdtrAgtBIC AS BIC ) AS CdtrAgt
    CROSS APPLY (SELECT @CdtrNm AS Nm) AS Cdtr
    FOR XML PATH('GrpHdr'), TYPE
    )
FOR XML PATH('FIToFICstmrCdtTrf'), ROOT('Document');

Examle of XML creation with namespace:



-- Generate the PACS.008 XML document with namespace
;WITH XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:pacs.008.001.09')
SELECT
    @MsgId AS 'GrpHdr/MsgId',
    @CreDtTm AS 'GrpHdr/CreDtTm',
    @NbOfTxs AS 'GrpHdr/NbOfTxs',
    @CtrlSum AS 'GrpHdr/CtrlSum',
    (
        SELECT
            @InitgPtyNm AS 'Nm'
    FOR XML PATH('InitgPty'), TYPE
    ),
    (
        SELECT
            @InstrId AS 'InstrId',
            @EndToEndId AS 'EndToEndId'
    FOR XML PATH('PmtId'), TYPE
    ),
    (
        SELECT
            @Amt AS 'InstdAmt'      
        FOR XML PATH('Amt'), TYPE
    ),
    (
        SELECT
            (
                SELECT
                    @CdtrAgtBIC AS 'BIC'
            FOR XML PATH('FinInstnId'), TYPE
            )
    FOR XML PATH('CdtrAgt'), TYPE
    ),
    (
        SELECT
            @CdtrNm AS 'Nm'
    FOR XML PATH('Cdtr'), TYPE
    )
FOR XML PATH('GrpHdr'), ROOT('Document'), TYPE;


Result:
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.008.001.09">
    <GrpHdr>
        <GrpHdr>
            <MsgId>
                123456789
            </MsgId>
            <CreDtTm>
                2023-07-26T12:34:56
            </CreDtTm>
            <NbOfTxs>
                1
            </NbOfTxs>
            <CtrlSum>
                1000.00
            </CtrlSum>
        </GrpHdr>
        <InitgPty xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.008.001.09">
            <Nm>
                Sender Company Name
            </Nm>
        </InitgPty>
        <PmtId xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.008.001.09">
            <InstrId>
                Payment123
            </InstrId>
            <EndToEndId>
                EndToEnd123
            </EndToEndId>
        </PmtId>
        <Amt xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.008.001.09">
            <InstdAmt>
                1000.00
            </InstdAmt>
        </Amt>
        <CdtrAgt xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.008.001.09">
            <FinInstnId xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.008.001.09">
                <BIC>
                    AAAABBBB
                </BIC>
            </FinInstnId>
        </CdtrAgt>
        <Cdtr xmlns="urn:iso:std:iso:20022:tech:xsd:pacs.008.001.09">
            <Nm>
                Recipient Name
            </Nm>
        </Cdtr>
    </GrpHdr>
</Document>

I tried multiple ways, but if I use subquery, namespace is always repeated.

Thom A
  • 88,727
  • 11
  • 45
  • 75
codecoder
  • 21
  • 2

0 Answers0