July 3, 2012 at 4:45 am
I'm having trouble with understanding how to handle XML data returned by a web service. I'm struggling to the extent that I can't even think of how to logically approach the problem.
Overview of SSIS package:
Package reads through a flat file, which is then loaded into a table, some of the columns of this table are used to pass information on a row by row basis to two web services. These web services both return a XML data string per row submitted, this XML contains data that will be used to populate empty columns in the existing table. The table is then used to recreate the original file plus these returned values.
My problem is I can't understand how to extract the data from the XML data returned in a way that will allow me to update the table on a row by row basis. I have tried using a Derived Column task which uses expressions to strip the data out of the XML strings but this seems a completely horrible way to approach the problem.
Sorry if the above is vague and rambling but it’s how this problem has made me feel!
July 3, 2012 at 6:00 am
Nimmel (7/3/2012)
I'm having trouble with understanding how to handle XML data returned by a web service. I'm struggling to the extent that I can't even think of how to logically approach the problem.Overview of SSIS package:
Package reads through a flat file, which is then loaded into a table, some of the columns of this table are used to pass information on a row by row basis to two web services. These web services both return a XML data string per row submitted, this XML contains data that will be used to populate empty columns in the existing table. The table is then used to recreate the original file plus these returned values.
My problem is I can't understand how to extract the data from the XML data returned in a way that will allow me to update the table on a row by row basis. I have tried using a Derived Column task which uses expressions to strip the data out of the XML strings but this seems a completely horrible way to approach the problem.
Sorry if the above is vague and rambling but it’s how this problem has made me feel!
I'm no XML expert, but what about something like this:
1) Use a data flow to read from the flat file.
2) Add a script component to the data flow which gets the two extra bits of XML as additional pipeline columns.
3) Add the data and the two columns to the destination table.
4) Run a stored proc on the imported data to unscramble the XML for you and do the required updates. You may need further help withg this bit, but there are lots of clever people here who will be able to assist.
5) Use another dataflow to output the required columns in the updated table, with all the additional data.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 3, 2012 at 7:18 am
Hi Phil, thanks for the reply. Yes that does sound much neater than what I'm already trying and I will certainly going to give that a go this afternoon, how far I get with the SQL to split the XML up is of course another matter! 🙂
July 3, 2012 at 7:57 am
I may be wrong but having looked at BOL I think I could use the OPENXML statement to achieve this. Example from BOL as below.
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
However I can't seem to get this to work with the XML string that the webservice I am using is providing me with, as below:
<AllocateGiftResult xmlns:a="http://schemas.datacontract.org/2004/07/StockControlServices.Models.Response" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.xxx.co.uk/giftmanagement/"><a:AllocationReference i:nil="true" /><a:AmpsReference i:nil="true" /><a:ErrorString>StockRecordNotFound</a:ErrorString><a:ResultCode>Error</a:ResultCode></AllocateGiftResult>
edit: XML as below as posting it as code seems to replace the <> with lt and gt.
<AllocateGiftResult xmlns:a="http://schemas.datacontract.org/2004/07/StockControlServices.Models.Response" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.xxx.co.uk/giftmanagement/"><a:AllocationReference i:nil="true" /><a:AmpsReference i:nil="true" /><a:ErrorString>StockRecordNotFound</a:ErrorString><a:ResultCode>Error</a:ResultCode></AllocateGiftResult>
Hopefully it is just a matter of getting the OPENXML statement correct but I have tried a fair few variations and either retrieve nulls or no values at all. Any help on how to proceed would be much appreciated.
July 3, 2012 at 8:02 am
I suggest that you post specific questions about extracting stuff from XML strings as a separate post on one of the T-SQL forums.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply