Take the 2-minute tour ×
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.

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

<soapenv:Envelope xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">
    <soapenv:Header xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar" />
            <soapenv:Body xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">
                    <pas:SomeObject xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">

and I want it to look like

<soapenv:Envelope xmlns="soapenv" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="foobar">
    <soapenv:Header/>
            <soapenv:Body>
                    <pas:SomeObject>

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
share|improve this question

1 Answer 1

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:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="http://foobar">
  <pas:Child pas:ID="1" />
  <pas:Child pas:ID="2" />
</soapenv:Envelope>
share|improve this answer

Your Answer

 
discard

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.