Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Other SQL Server 2008 Topics
 SOAP dodger

Author  Topic 

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-11-30 : 10:32:57
I've been asked to set up a SOAP/XML API to send data directly into a client's system. Its all kind of new to me so here's the thing.

I've been given the web service details so I don't envisage too many problems with that, but I'm having trouble creating the XML as it contains a SOAP header and I've not come accross this before.

Does anyone know how to write a SQL query to produce an XML result like the one provided below? (I've got all the fields they require in a table, I just need to know how to do the XML)

<?xml version="1.0" encoding="UTF-8"?>
-<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://www.w3.org/2003/05/soap-envelope" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
-<SOAP-ENV:Body>
-<CreateProposal xmlns="http://www.anchor.co.uk/">
<userName>uname</userName>
<password>pass</password>
<account>SWP Users</account>
<proposal>
<Title>Mr</Title>
<Forename>Test</Forename>
<Surname>Test</Surname>
<DOB>1980-08-04T10:44:41</DOB>
<MaritalStatus></MaritalStatus>
<MobilePhoneNumber>0123456789</MobilePhoneNumber>
<PostCode>AB1 2CD</PostCode>
</proposal>
<allowZeroPrincipal>0</allowZeroPrincipal>
</CreateProposal>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>


I'd be most grateful if someone could point me in the right direction. You know, the annoying thing is that I'm leaving this job next week so if they'd left it for a few more days, it would have been someone else's headache. Doh!

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-30 : 11:35:01
I think what most folks have done for this is have the database just return to the web service the data content as a normal sql result set. The web service code would use that content to construct the xml response including the soap header. At least that is the way we do it.

Be One with the Optimizer
TG
Go to Top of Page

Elizabeth B. Darcy
Starting Member

39 Posts

Posted - 2012-11-30 : 12:04:37
Can you also post the schema of your database tables where the data is sourced from?

________________________________________
-- Yes, I am indeed a fictional character.
Go to Top of Page

theboyholty
Posting Yak Master

226 Posts

Posted - 2012-12-03 : 09:49:06
TG - I like the sound of what you're saying there. After many fierce battles of will, I've grown to love web services. I shall give it a go.

Elizabeth, I've dragged the data into a single table to make life simple so it would look something like
SELECT Title, Forename,Surname,DOB,MaritalStatus,MobilePhoneNumber,Postcode FROM AnchorWebSelection


---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum
Go to Top of Page
   

- Advertisement -