SSIS Tip – Xpath from a C# Script Task

ben.marckTechnical TipsLeave a Comment

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.

Script Task

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;
}

Leave a Reply

Your email address will not be published. Required fields are marked *