Web Service Control

Question

Web Service Control

ErickTreeTops

Ten Centuries

Points: 1253
More actions
April 14, 2009 at 12:13 am

#131274

I've had lots of trouble getting my scheduler to run an SSIS package which reads xml data from a web services into a file and then loads that file back into a table. I've found one of the causes but my xml is not great so i'm not sure how to sort it ...here are the problems.
both SSIS and the scheduler create an xml file using the web service task as the source. The first few lines look like this
etc etc
The xml source doesn't like this. It wants this format
Notice the xmlns="LGPSR" before each element in the first example wich doesnt work with the second.
The XML source will also accept this;
<ArrayOfPhonePerson xmlns="LGPSR"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="LGPSR GetPhoneListShort.xsd"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
I have an xsd file stitting there but don't know who to get a reference into the xml file either.
is there any way i can create the second or 3rd option through SSIS. I believe there is an XML Task in SSIS but is this what I can use and how do I use it.?
Erick

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

vishal.gamji SSCertifiable Points: 6126 More actions · Answer 1

I am sorry, but your question was confusing... are you trying to retrieve data from an XML format and shred it into relational format?

ErickTreeTops Ten Centuries Points: 1253 More actions · Answer 2

Sorry I just noticed my embeded xml didn't get through so the message become nonsense.

Here is the story

I have a web services which returns data and stores it in an xml file.

The XML source then reads it into the package.

However, the xml source reads zero rows because of the way the xml is formatted .

See attachement "Doesn't work xml header.txt" which the xml source can't read

The other two formats the XML source control reads fine;

"Does work xml header.txt" which has the xmlns= part once at the top and not repeated against each element.

And by placing a reference in the xml file to the xsd file "does work by pointing at xsd.txt"

Is there a way, and if there is can you show me, of converting the first xml format to the second or the third example. My thoughts were that the XML Task control in SSIS might be used for this but I have never used this control before and my xml skills are slight to say the least.

regards

Erick