Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a TSQL query that I use to generate a SOAP document. I am using two namespaces. Namely: - 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

<soapenv:Envelope xmlns="soapenv" xmlns:soapenv="" xmlns:pas="foobar">
    <soapenv:Header xmlns="soapenv" xmlns:soapenv="" xmlns:pas="foobar" />
            <soapenv:Body xmlns="soapenv" xmlns:soapenv="" xmlns:pas="foobar">
                    <pas:SomeObject xmlns="soapenv" xmlns:soapenv="" xmlns:pas="foobar">

and I want it to look like

<soapenv:Envelope xmlns="soapenv" xmlns:soapenv="" xmlns:pas="foobar">

In the query I am currently using

;WITH XMLNAMESPACES ( 'http://foobar' as pas,
      '' as soapenv
SELECT (SELECT '' FOR XML PATH('soapenv:Header'),type ),
FOR XML PATH('soapenv:Envelope'), ELEMENTS
share|improve this question

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,
       '' 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,
from DummyTable       
for xml explicit


<soapenv:Envelope xmlns:soapenv="" xmlns:pas="http://foobar">
  <pas:Child pas:ID="1" />
  <pas:Child pas:ID="2" />
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.