At some point you may need to use Xpath from within a script task rather than using Integration Services built in XML Task’s Xpath functionality. Here’s how I implemented it. The primary limitation is that it will only return a single value, but this can be adjusted to return multiple values easily.
Chances are you’ll already be using a script task, but incase you don’t, start with a standard Script Task (this can be either a Control Flow Script Task or a Data Flow Script Task). Don’t forget to set your ReadOnly / ReadWrite variables.
Here’s the boilerplate code for using Xpath in a script task
XmlDocument doc = new XmlDocument();
public void Main()
{
sampleUrl = http://www.example.com/sample.xml;
doc.Load(sampleUrl);
int id = 5;
string roll = Xpathquery("/people[@id=”+id+”]/@roll"); // This will find the @roll attribute where the id attribute is 5.
//TODO: Write the string to a SSIS variable or do with it what you’d like.
Dts.TaskResult = (int)ScriptResults.Success;
}
public string Xpathquery(string xpath)
{
XPathNavigator nav = doc.CreateNavigator(); // Compile a standard XPath expression
XPathExpression expr;
expr = nav.Compile(xpath);
XPathNodeIterator iterator = nav.Select(expr);
//Iterate on the node set
try{
while (iterator.MoveNext())
{
XPathNavigator nav2 = iterator.Current.Clone();
return nav2.Value;
}
}
catch (Exception ex) {
Console.WriteLine(ex.Message);
return null;
}
return null;
}