The XML Task in SSIS allows you to parse through an XML file and read the nodes in the XML. In a previous blog I showed how to use a web service task to get the city and state when entering a zip code.
The results we got back from the web service were in XML format and we saved them into a variable named Results. This image shows the value in the variable
We need to get the city and the state out of this XML and save them each into a variable. You will need to create an XML task in the Control Flow of a package. Set the operation type to XPATH. The source is going to be the results variable. The destination will be the city variable. The second operand will be the node you want to be read, “//CITY” in this case. Last we set the XPATH Operation to values because you want the value of the city node.
The XML Task that retrieves the State value will be identical except for the Second Operand and the Destination variable.
After all of this is complete the package with the web service task will come before the XML Tasks. I placed a break point on the last XML Task and I am showing the results of the variables in the watch window. You can see that the results contain the XML, the City, and State variables contain the values from their respective nodes.
