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.