Primer on consuming XML data from a web service and importing into SQL 2008

Question

Can anyone point me to a good example of how to take XML data from a web service (representing one or many order 'objects'), read that into a variable, then parse the data in the string variable out into a form that can then be importing into an appropriate SQL table (to be designed later).

I've created a project that uses the webservice task to return the xml data into a package variable, I've hooked up the wsdl file, and that all works great (I've watched at the variable  as it executes).
I've tried to create an XML task to parse the data out, but it fails for various reasons.

I've got it set to
Operation Type  XPATH
SourceType    Variable
Source             thevariable

Output
SaveOperationResult   true

I've tried saving to a new variable, then to a file, but I don't know what to set the Second OPerand values to.
Do I need to somehow create an xsd file and reference it here?

I'd like to code this 'professionally', but at this point I think I would be Ok with just getting the XML data into a staging table, then writing a stored procedure to perform all of the operations on it, and just calling the stored proc from the SSIS package.

Answer 1

Hi there,

Depending on the type of the operation set on the XML task the second operand semantics will change.
If you're using the XPATH as the operation type then the second operand will be the XPath expression you want to query the XML in the thevariable variable. You can read up more about this topic here: http://www.w3.org/TR/xpath.


Hope it helps.

---

This posting is provided "AS IS" with no warranties, and confers no rights