Limit xml-namespaces to only the main root

Question

I have this query

 WITH XMLNAMESPACES(DEFAULT 'https://tribunet.hacienda.go.cr /docs/esquemas/2017/v4.2/facturaElectronica'
                      ,'http://www.w3.org/2001/XMLSchema' AS xsd
                      ,'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT 1 AS [id]
        ,0 AS [pass]
        (
         /*Others*/
        SELECT 
        OT.OTH_MESSAGE as Others
        FROM [crdx_COREDev1].[dbo].[OTH_OTHERS] as OT
        where 
        OT.OTH_ID=E.OTH_ID
        fOR XML PATH ('Others'), type

       )
      ,0 AS [CONSECUTIVE]

      FOR XML PATH('FE');

This generates this XML

<FE xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 xmlns="https://tribunet.hacienda.go.cr/docs/esquemas/2017/v4.2    /facturaElectronica"> <- CHANGE 2
 <id>1</id>
 <pass>0</pass>
 <CONSECUTIVE>0</CONSECUTIVE>
 <Others xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 xmlns="https://tribunet.hacienda.go.cr/docs/esquemas/2017/v4.2 /facturaElectronica">
 <MESSAGE>MESSAGE</MESSAGE>       
</Others> 
 </FE>

Now my question: I would like only <FE> to show the namespaces, but - as you see in the xml - that declarations appear also in <Others>. How can I limit this to <FE>?

Answer 1

This is an annoying and well known issue and occurs whenever you use namespaces in connection with nested sub-queries in FOR XML queries...

There has been a connect issue for more than 10 years - until it disappaered recently.

It is important to mention, that these repeated namespace declarations are not wrong, just bloating your XML. And it can collide with (to) strict schema validations.

No good solution, just workarounds:

• Create the inner XML without the namespace and add the wrapping node on string base, or
• Create the namespaces as normal attributes (but not named xmlns) and use REPLACE to change the names.

Both workarounds need a conversion to NVARCHAR(MAX) and back to XML.

I really have no idea, why this was implemented this way...

Find some related examples

• here
• and here
• and here
• and here

Attention:

xmlns="https://tribunet.hacienda.go.cr/docs/esquemas/2017/v4.2    /facturaElectronica">

You are using namespace URLs with blanks. This is not allowed...