I came across a question about this the other day, and thought I’d share the answer here, as there doesn’t seem to be much information out there about it. The questioner wanted to know how to read a portion of an XML document into a variable. Fortunately, the XML Task allows you to do this by running an XPath query. To configure the task, follow these steps:
- Set the operation type to XPATH.
- Specify a source for the XML (file, variable, or directly input).
- Set the OperationResult to save the results in a variable.
- Set the SecondOperandType to Direct Input.
- Specify the XPath query in the SecondOperand.
- Set the PutResultInOneNode option to False.
- Set the XPathOperation to Node list.
If, after configuring this, the variable is empty, it’s likely that the XPath isn’t correct. This can be caused by a number of things. A good way to test your XPath is this web page: http://www.whitebeam.org/library/guide/TechNotes/xpathtestbed.rhtm It allows you to upload your XML file, and then run your XPath query against it.
Another common problem occurs when the XML file uses namespaces. If it does, you can try using XPath like this: //*[local-name()=’Setup’ and namespace-uri()=’http://test.org’]
Hopefully this helps, if you are working with XPath in SSIS.