July 23, 2005 at 11:10 am
I have been trying out several publicly available web services using the SSIS Web Service Task. Some of them return a value, for example the conversion rate between two currencies or the size of kayak paddle you should get based on your height. The XML returned by using the Web Service Task will look something like this:
<string>194-198cm</string>
I am having a hard time trying to figure out how to get the value. If I use an XML Source object and generate an XSD file, when I click "Columns" the list of columns is missing. Futher attempts to configure the object give a message stating that there is potential metadata corruption.
OK, next attempt, use the XML Task object along with the XPATH operation type. I have to confess, I am probably doing something wrong here, not being too familiar with XPATH. I set the properties as follows:
When I execute the task I get the following error:
Error: 0x0 at XML Task, Xml Task: Property Second Operand (XPath source) has no source XML text. The XML text is not valid, null, or an empty string.
Task failed: XML Task
I have verified that the connection is pointing to the correct XML file.
Any help is highly appreciated. I have a huge deadline coming up in a couple of days and have to get this working.
Thanks,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
July 24, 2005 at 1:24 pm
I have another idea to try, throw the XML into an XML field and query it in an execute SQL task. Wish I had thought of this sooner. I'll post what I end up doing.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
July 24, 2005 at 2:54 pm
Thought I would try it in the query window first to see if this approach will work. The only way I can work with it in the query window is by removing the codepage info:
<string>194-198cm</string>
The XML task still doesn't like it. It generates a nice looking XSD file, but the XML task never shows the columns and then gets corrupted.
I could parse out the answer, but I can't do anything kludgey for this project.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
July 24, 2005 at 6:58 pm
I finally got the XML Task and XPATH to work to return the answer. If I save it in a variable, it has an extra character, a row delimiter I guess. I am using the script task to chop off that last character and now have something that works. Not as elegant as I would like.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
June 12, 2006 at 10:21 am
PS: Your 'Help from Aunt Kathi' Link seems to be broken....
June 12, 2006 at 10:21 am
I realize this was posted a long time ago - please forgive me for digging it up, but it is one of the few matches I've had while trying to find information of Merging with XML Task for SSIS.
I'm trying to understand what the requirement is for merging 2 XML documents - I'm finding the BOL and MS Online help poor/sparse examples (or perhaps I'm too ignorant of XML/Xpath to process what they give me).
I have an XML file - call it the product file (I will use -- to denote levels, since tags don't display properly on this board
RECORDS
--RECORD
----PROP NAME="P_WineID"
------PVAL 3 /PVAL
----/PROP
----PROP NAME="Description"
------PVAL The description /PVAL
----/PROP
... there are several other attributes to the record (are these called attributes, or elements?
--/RECORD
... there are going to be many RECORD nodes - up to 6000 or so
/RECORDS
I want to merge into this an Availability File I've built up to look like:
RECORDS
--RECORD
----PROP NAME="P_WineID"
------PVAL 3 /PVAL
----PROP
----PROP NAME="Availability"
------PVAL Available /Pval
----/PROP
--/RECORD
... there would be one of these for most/all of the RECORD nodes in the other file
I want to merge, based on P_WineID from the Product File = P_WineID from Availability, it should add the Availability Element, and it's value, to the Product File, and then write out the final, merged file.
Looking at the MERGE action on the XML task, it seems like this is the right place to do it. I have the Soruce (Product File), SECOND OPERAND (The Availability File), OutPut set to SAVEREULTOUTPUT=TRUE, OPERATIONRESULT.DESTINATION set to a new file location. Now I just need to understand what I need to do with the XPathStringSourceType and XPathStringSource to do the merge. Unfortunately, I don't have any knowledge of XPath, and haven't found a decent book/tutorial/example of it being used (especially in this context) to figure out what to do next.
I didn't find too much explanation in the book you helpted to write (I'm on chapter 10 now). The section in chapter 3 was pretty brief (+-1 page).
Any help?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply