SSIS Tip – Xpath from a C# Script Task

Posted on Feb 28, 2011 in Technical Tips | 0 comments



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;
}
Bookmark and Share

Related Information:

  1. SSIS Tip – Read from Variables to Rows in Data Flow using Script Task
    Reading from variables to augment...
  2. Request XML from URL in SSIS C# Script Task
    Occasionally, I’ve needed to pull...
  3. SSIS Tip – Xpath for Root Level Attributes (XML Tasks)
    For some reason, SSIS’s XML...
  4. SSIS Tip – Save Package to MS SQL Server (Integration Services)
    In order to save your...
  5. Oracle Application Server Startup and Shutdown Script Generator – FREE
    Complete the below to receive...
  6. Strategy to Create a Usable Multi-function Task List (ADF)
    When People Interact with Executable...
  7. Oracle INSTR Function – SQL Syntax Examples
    The Oracle INSTR SQL function...
  8. Oracle SUBSTR Function – SQL Syntax Examples
    The Oracle SUBSTR SQL Function...

Leave a Reply