t-sql: create and add several namespaces to xml

Question

I'm kinda new working with xml in sql and have to build a xml like this My Xml and replace with correct values where (...) appears.

Replacement is not my problem, otherwise creating the several namespaces that appears on the previous image is.

DECLARE @XML XML

SET @XML = (SELECT   
              (SELECT 
                     (SELECT 'http://www.w3.org/2001/10/xmlexcc14n#' "@Algorithm"
                        FOR XML PATH('CanonicalizationMethod'), TYPE),
                     (SELECT 'http://www.w3.org/2001/04/xmldsigmore#rsasha256' "@Algorithm"
                        FOR XML PATH('SignatureMethod'), TYPE),
                     (SELECT '#FATCA' "@URI",
                             (SELECT 
                                     (SELECT 'http://www.w3.org/2001/10/xmlexcc14n#' "@Algorithm"
                                       FOR XML PATH('Transform'), TYPE)
                                 FOR XML PATH('Transforms'), TYPE),
                             (SELECT 'http://www.w3.org/2001/04/xmlenc#sha256' "@Algorithm"
                                 FOR XML PATH('DigestMethod'), TYPE),
                             (SELECT ''
                                 FOR XML PATH('DigestValue'), TYPE)
                         FOR XML PATH('Reference'), TYPE)                            
                 FOR XML PATH('SignedInfo'), TYPE),
              (SELECT ''
                 FOR XML PATH('SignatureValue'), TYPE),
              (SELECT
                     (SELECT
                            (SELECT ''
                               FOR XML PATH('X509SubjectName'), TYPE),
                            (SELECT ''
                               FOR XML PATH('X509Certificate'), TYPE)
                        FOR XML PATH('X509Data'), TYPE)
                 FOR XML PATH('KeyInfo'), TYPE),
              (SELECT 'FATCA' "@Id",
                      (SELECT  
                             (SELECT
                                    (SELECT ''
                                       FOR XML PATH('SendingCompanyIN'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('TransmittingCountry'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('ReceivingCountry'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('MessageType'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('Warning'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('Contact'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('MessageRefId'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('ReportingPeriod'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('Timestamp'), TYPE)
                                FOR XML PATH('MessageSpec'), TYPE),
                             (SELECT ''
                                FOR XML PATH('FATCA'), TYPE)
                         FOR XML PATH('FATCA_OECD'), TYPE)
                 FOR XML PATH('Object'), TYPE)
          FOR XML PATH('Signature'))

SELECT @XML

This is what i have so far and it works perfect.

I've already read this article https://msdn.microsoft.com/en-us/library/ms177400.aspx but doesn't really help me because i need to use namespaces at the middle of xml and can't figure it out how.

Could someone please help me?

Answer 1

You can use WITH XMLNAMESPACES to create the XML you need. Change SET to a SELECT.

Something like this.

DECLARE @XML XML;

WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/2000/09/xmldsig#',
                   'urn:oecd:ties:fatca:v1' as ftc,
                   'urn:oecd:ties:stffatcatypes' as sfa)
SELECT @XML = (SELECT   
              (SELECT 
                     (SELECT 'http://www.w3.org/2001/10/xmlexcc14n#' "@Algorithm"
                        FOR XML PATH('CanonicalizationMethod'), TYPE),
                     (SELECT 'http://www.w3.org/2001/04/xmldsigmore#rsasha256' "@Algorithm"
                        FOR XML PATH('SignatureMethod'), TYPE),
                     (SELECT '#FATCA' "@URI",
                             (SELECT 
                                     (SELECT 'http://www.w3.org/2001/10/xmlexcc14n#' "@Algorithm"
                                       FOR XML PATH('Transform'), TYPE)
                                 FOR XML PATH('Transforms'), TYPE),
                             (SELECT 'http://www.w3.org/2001/04/xmlenc#sha256' "@Algorithm"
                                 FOR XML PATH('DigestMethod'), TYPE),
                             (SELECT ''
                                 FOR XML PATH('DigestValue'), TYPE)
                         FOR XML PATH('Reference'), TYPE)                            
                 FOR XML PATH('SignedInfo'), TYPE),
              (SELECT ''
                 FOR XML PATH('SignatureValue'), TYPE),
              (SELECT
                     (SELECT
                            (SELECT ''
                               FOR XML PATH('X509SubjectName'), TYPE),
                            (SELECT ''
                               FOR XML PATH('X509Certificate'), TYPE)
                        FOR XML PATH('X509Data'), TYPE)
                 FOR XML PATH('KeyInfo'), TYPE),
              (SELECT 'FATCA' "@Id",
                      (SELECT  
                             (SELECT
                                    (SELECT ''
                                       FOR XML PATH('sfa:SendingCompanyIN'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('sfa:TransmittingCountry'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('sfa:ReceivingCountry'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('sfa:MessageType'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('sfa:Warning'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('sfa:Contact'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('sfa:MessageRefId'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('sfa:ReportingPeriod'), TYPE),
                                    (SELECT ''
                                       FOR XML PATH('sfa:Timestamp'), TYPE)
                                FOR XML PATH('ftc:MessageSpec'), TYPE),
                             (SELECT ''
                                FOR XML PATH('ftc:FATCA'), TYPE)
                         FOR XML PATH('ftc:FATCA_OECD'), TYPE)
                 FOR XML PATH('Object'), TYPE)
          FOR XML PATH('Signature'));

SELECT @XML;

It does not look exactly like what you have in your picture but it is the same XML semantically.

Update:

To avoid the bloating of namespaces in the child nodes you can build one level at a time using an XML variable and then use that variable in your main query.

Sample code:

declare @X xml;

with xmlnamespaces(default 'N2',
                   'N3' as n3,
                   'N4' as n4)
select @X = (
            select 2 as Elem2
            for xml path('n4:SubElem'), type
            );

with xmlnamespaces(default 'N1')
select 1 as Elem1,
       @X
for xml path('Root')

Result:

<Root xmlns="N1">
  <Elem1>1</Elem1>
  <n4:SubElem xmlns:n4="N4" xmlns:n3="N3" xmlns="N2">
    <Elem2>2</Elem2>
  </n4:SubElem>
</Root>

You can even create functions for your sub nodes.

create function [dbo].[GetSubElem]() returns xml
as
begin

  declare @X xml;

  with xmlnamespaces(default 'N2',
                     'N3' as n3,
                     'N4' as n4)
  select @X = (
              select 2 as Elem2
              for xml path('n4:SubElem'), type
              );

    return @x
end

And use it like this.

with xmlnamespaces(default 'N1')
select 1 as Elem1,
       dbo.GetSubElem()
for xml path('Root')