CODEWITHSUNDEEP
sql-serverxmlt-sql
zawier
zawier

Reputation: 31

XML how to remove namespace from all node except root?

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.

Upvotes: 0

Views: 831

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8829

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>

Upvotes: 1

Related Questions