How to remove xmlns from child elements with FOR XML

I have a TSQL query that I use to generate a SOAP document. I am using two namespaces. Namely:

• http://schemas.xmlsoap.org/soap/envelope/ for "packaging" the soap document
• A custom one that describes the data contained within the body

Currently every single element including those in the body contain the soapnamespace reference which I don't need and want in there I only want it in the root element.

So my XML now looks like

and I want it to look like

In the query I am currently using

;WITH XMLNAMESPACES ( 'http://foobar' as pas,
      'http://schemas.xmlsoap.org/soap/envelope/' as soapenv
      )
SELECT (SELECT '' FOR XML PATH('soapenv:Header'),type ),
etc...
FOR XML PATH('soapenv:Envelope'), ELEMENTS

Vote on this connect item for a change of behavior.

You can use for xml explicit to build your XML instead.

Something like this:

;with DummyTable(id) as
(
select 1 union all
select 2
)

select 1 as tag,
0 as parent,
'http://schemas.xmlsoap.org/soap/envelope/' as [soapenv:Envelope!1!xmlns:soapenv],
'http://foobar' as [soapenv:Envelope!1!xmlns:pas],
null as [pas:Child!2!pas:ID]
union all
select 2,
1,
null,
null,
id
from DummyTable
for xml explicit


Result:







StackExchange Database Administrators Q#23624, answer score: 5