Duplication of XML namespaces in SQL

Question

I have the following problem, I need to remove the "cfdi_" for "cfdi:"

I used the namespaces to solve that but they are duplicated by each node and I can not eliminate them, If you could help me I would appreciate it

declare @Mydoc xml;

set @Mydoc = (SELECT 
                  '' as importe,
                  (SELECT Importe, TasaCuota, TipoFactor, Impuesto, Base
                   FROM CDFIDet
                   FOR XML RAW('cfdi_traslado'), TYPE, ROOT('cfdi_traslados'))
              FROM
                  CFDIENC
              FOR XML RAW('cfdi_gatito'),type)

SELECT @Mydoc;

Result1:

<cfdi:gatito importe="">
  <cfdi:traslados>
    <cfdi:traslado Importe="1920" TasaCuota="0" TipoFactor="Tasa" Impuesto="16" Base="240" />
    <cfdi:traslado Importe="2202" TasaCuota="0" TipoFactor="TASA" Impuesto="16" Base="450" />
  </cfdi:traslados>
</cfdi:gatito>

Second attempt:

declare @Mydoc xml;

WITH xmlnamespaces ('uri' as cfdi)
    SELECT @Mydoc = (SELECT 
                         '' AS importe,
                         (SELECT Importe, TasaCuota, TipoFactor, Impuesto, Base
                          FROM CDFIDet
                          FOR XML RAW('cfdi:traslado'), TYPE, ROOT('cfdi:traslados'))
                     FROM CFDIENC
                     FOR XML RAW('cfdi:gatito'), TYPE)

SELECT @Mydoc;

Result:

<cfdi:gatito xmlns:cfdi="uri" importe="">
  <cfdi:traslados xmlns:cfdi="uri">
    <cfdi:traslado Importe="1920" TasaCuota="0" TipoFactor="Tasa" Impuesto="16" Base="240" />
    <cfdi:traslado Importe="2202" TasaCuota="0" TipoFactor="TASA" Impuesto="16" Base="450" />
  </cfdi:traslados>
</cfdi:gatito>

My code is bigger and it is repeated too many times and I do not know how to eliminate them

XML support is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Dec 01 '17 at 18:12
it's sql server but I will not let you put that tag because I'm still new — carlitus212, Dec 01 '17 at 18:24

score 2 · Answer 1 · answered Dec 03 '17 at 16:00

It is annoying but valid output... Each sub-select withing a FOR XML statement will add its own set of namespace declarations. There is a very old - yet ignored! - Microsoft Connect issue Go there and vote for it.

There is no real workaround, just some hacks. Most people end up with some ugly tricks on string level. In this case you create the XML without any namespace and change it with string methods when you are finished.

In your other question you had the idea to add the prefix to your aliases like cfdi_Element and change this to cfdi:Element later.

This is exactly what I'm talking about...

But once again: It is not wrong, just annoying and bloating your output...

Try this: declare @Mydoc xml;

WITH xmlnamespaces ('uri' as cfdi)
SELECT @Mydoc = (SELECT 
                        'SomeValue' AS OuterElement,
                        (SELECT 'OtherValue' AS InnerElement
                        FOR XML RAW('cfdi:traslado'), TYPE)
                    FOR XML RAW('cfdi:gatito'), TYPE)

--The namespace declaration is replicated

SELECT @Mydoc;

<cfdi:gatito xmlns:cfdi="uri" OuterElement="SomeValue">
  <cfdi:traslado xmlns:cfdi="uri" InnerElement="OtherValue" />
</cfdi:gatito>

--But here it "works"

SELECT @Mydoc = (SELECT 
                        'SomeValue' AS OuterElement,
                        (SELECT 'OtherValue' AS InnerElement
                        FOR XML RAW('cfdi_traslado'), TYPE)
                    FOR XML RAW('cfdi_gatito'), TYPE)

--A cast to NVARCHAR(MAX), the ns-decl into the root node and some replaces:

SELECT CAST(REPLACE(REPLACE(CAST(@Mydoc AS NVARCHAR(MAX)),'<cfdi_gatito','<cfdi_gatito xmlns:cfdi="uri"'),'cfdi_','cfdi:') AS XML);

<cfdi:gatito xmlns:cfdi="uri" OuterElement="SomeValue">
  <cfdi:traslado InnerElement="OtherValue" />
</cfdi:gatito>

This is ugly, but often the only chance...

YOU ARE MY HERO OF ECHO THIS IS MUCH MORE EASY AND IT ADDES NODES THAT MISSED ME THE TRUTH YOU THANK IT VERY MUCH, YOU ARE MY HERO — carlitus212, Dec 04 '17 at 00:55

score 0 · Accepted Answer · answered Dec 01 '17 at 20:08

From XML prospective second result is self-sufficient XML element, while first result can be only part of other element where namespace for cfdi prefix is defined. So, I guess all works as you defined.

Sub-select

(SELECT Importe, TasaCuota, TipoFactor, Impuesto, Base
                      FROM CDFIDet
                      FOR XML RAW('cfdi:traslado'), TYPE, ROOT('cfdi:traslados'))

produces self-sufficient result as

<cfdi:traslados xmlns:cfdi="uri">
   <cfdi:traslado Importe="1920" TasaCuota="0" TipoFactor="Tasa" Impuesto="16" Base="240" />
   <cfdi:traslado Importe="2202" TasaCuota="0" TipoFactor="TASA" Impuesto="16" Base="450" />
</cfdi:traslados>

because you defined ROOT('cfdi:traslados') - you have namespace in it, and nested elements cfdi:traslado are in the same namespace - there is no separate namespace declaration.

Then top select:

 SELECT 
   '' AS importe,
   (SELECT Importe, TasaCuota, TipoFactor, Impuesto, Base
      FROM CDFIDet
           FOR XML RAW('cfdi:traslado'), TYPE, ROOT('cfdi:traslados'))
 FROM CFDIENC
   FOR XML RAW('cfdi:gatito'), TYPE)

defines a root element for cfdi:traslados as cfdi:gatito it has its own namespace declaration for cfdi prefix.

I'm not familiar with sql-server XML, but what will happen if you remove ROOT('cfdi:traslados') from sub-select? Will it remove xmlns:cfdi="uri" from <cfdi:traslados xmlns:cfdi="uri"> element?

the problem that the general one xml and more for one billing needs a specific format and if I declare the name spaces I have publicity for each node and I need to put everything as above but only replacing "cfdi_" with "cfdi:" — carlitus212, Dec 01 '17 at 20:13
Sorry question still not clear to me... technically speaking both results are the same if in Result1 root element (nearest one) on top of `` declares namespace for `cfdi` prefix as `xmlns:cfdi="uri"`. — Vadim, Dec 01 '17 at 20:22
is easy brother, look I need xsi to come out: schemaLocation and xmlns: cfdi but with the namespaces I can not declare a prefix xsi since the xmlns is overlapped and apart from that I generate that in each node the namespaces are put and I can not eliminate — carlitus212, Dec 03 '17 at 02:47

Duplication of XML namespaces in SQL

2 Answers2

Linked