XML how to remove namespace from all node except root?

Question

I have following query:

WITH XMLNAMESPACES ('https://www.my.namespace.com' as ns) 
SELECT 
    Q.DocType AS [@typeP],
    Q.RefNo AS [@idP],
    Q.Id AS [@kode],
    D.DocId AS [@idDoc],
    N.NotId AS [@idNot],
    CONVERT(char(10), N.CreDate, 126) AS [@date],
    'Text' AS [TN/@Z],
    @T2 AS [TN/T]
    ,(select Adr AS [Adr] from ##TEMP  FOR XML PATH (''), type)
FROM
    [DB].[dbo].[Q] AS Q 
    LEFT JOIN [DB].[dbo].[D] AS D ON Q.Id=D.Id
    LEFT JOIN [DB].[dbo].[N] AS N ON D.DocId=N.DocuId
WHERE 
    Q.InboxId= Cast(@InboxId as varchar(15))
FOR XML PATH ('Not');

As result I get XML:

<Not xmlns:ns="https://www.my.namespace.com" 
             typeP="4532" 
             idP="90210" 
             kode="YCV06N1L-FMDA-YPXZ-5H4F-BLA75C6G86KI" 
             idDoc="49" 
             idNot="456" 
             date="2021-07-19">
  <TN Text="T">
    <T>some text</T>
  </TN>
    <Adr xmlns:ns="https://www.my.namespace.com">10800234</Adr>
    <Adr xmlns:ns="https://www.my.namespace.com">24900005</Adr>
    <Adr xmlns:ns="https://www.my.namespace.com">24900004</Adr>
    <Adr xmlns:ns="https://www.my.namespace.com">10201026</Adr>
    <Adr xmlns:ns="https://www.my.namespace.com">66600019</Adr>
    <Adr xmlns:ns="https://www.my.namespace.com">14042243</Adr>
</Not>

How to remove namespace from Adr node? I would like to get:

        <Adr>10800234</Adr>
...        
   <Adr>14042243</Adr>

Please advise what I'm doing here incorrectly.

I would be appreciate any solution.

Then I will get : Invalid object name 'TYPE'. If also I remove 'type' then: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. — zawier, Jul 22 '21 at 11:21
I can see `xmlns:ns` getting defined - but what references it? Why is it needed? — AlwaysLearning, Jul 22 '21 at 11:39
That namespace (of course here is changed just for simplification) is included into document specification as required. So has to be included only at root node. — zawier, Jul 22 '21 at 11:41
Look at here https://stackoverflow.com/questions/3242070/how-do-i-remove-redundant-namespace-in-nested-query-when-using-for-xml-path — Alexey Chuksin, Jul 22 '21 at 12:00

score 1 · Accepted Answer · answered Jul 22 '21 at 11:58

Perhaps inelegant, but the most obvious solution is:

declare @Adr xml = (
  select Adr AS [Adr]
  from ##TEMP
  FOR XML PATH (''), type
);

WITH WITH XMLNAMESPACES ('https://www.my.namespace.com' as ns) 
SELECT 
    Q.DocType AS [@typeP],
    Q.RefNo AS [@idP],
    Q.Id AS [@kode],
    D.DocId AS [@idDoc],
    N.NotId AS [@idNot],
    CONVERT(char(10), N.CreDate, 126) AS [@date],
    'Text' AS [TN/@Z],
    @T2 AS [TN/T],
    @Adr
FROM
    [DB].[dbo].[Q] AS Q 
    LEFT JOIN [DB].[dbo].[D] AS D ON Q.Id=D.Id
    LEFT JOIN [DB].[dbo].[N] AS N ON D.DocId=N.DocuId
WHERE 
    Q.InboxId= Cast(@InboxId as varchar(15))
FOR XML PATH ('Not');

Which yields...

<Not xmlns:ns="https://www.my.namespace.com"
  typeP="4532"
  idP="90210"
  kode="YCV06N1L-FMDA-YPXZ-5H4F-BLA75C6G86KI"
  idDoc="49"
  idNot="456"
  date="2021-07-19">
    <TN Z="Text">
        <T>some text</T>
    </TN>
    <Adr>10800234</Adr>
    <Adr>24900005</Adr>
    <Adr>24900004</Adr>
    <Adr>10201026</Adr>
    <Adr>66600019</Adr>
    <Adr>14042243</Adr>
</Not>

XML how to remove namespace from all node except root?

1 Answers1