Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 9, 2007

SQL Server 2005 Integration Services - Part 42 - XML Task continued

By Marcin Policht

In the previous article of our series dedicated to SQL Server 2005 Integration Services, we started discussing various types of actions that can be performed using XML Control Flow task, covering its Validate and XSLT options. We will continue this subject by focusing our attention on the next operation type called XPATH. As mentioned earlier, we encourage you to explore sources of XML-specific information for more in-depth analysis of these topics (we will be referencing some of them throughout our presentation).

We have briefly explained basic purpose and characteristics of XPath (XML Path Language) in our previous article (this was necessary in order to describe some of the XSLT-related concepts), as well as provided some basic examples demonstrating its use (to refresh your memory, XPath is a notation used for designating the location of components within XML documents). The XPATH option of SSIS Control Flow XML Task leverages this functionality by retrieving a portion of an XML document (pointed to by the task's Source property) identified by an XPath expression (specified in the form of task's SecondOperand property).

In order to familiarize yourself with configuration parameters of this option, create a new project of Integration Services type in SQL Server 2005 Business Intelligence Development Studio. Next, drag the XML Task icon from the Toolbox onto its Designer interface. By right clicking on the newly added task, you will be able to select Edit from the context sensitive menu, which will display the Task Editor window. From here, select XPATH as the value of OperationType property. As before, for simplicity sake, we will use Direct input as the SourceType (other possibilities include a variable and File connection) and set the Source based on the example listed at Sample XML File for XPath Syntax (inventory.xml) posting:

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="myfile.xsl" ?>
<bookstore specialty="novel">
  <book style="autobiography">
      <award>Trenton Literary Review Honorable Mention</award>
  <book style="textbook">
      <publication>Selected Short Stories of
  <magazine style="glossy" frequency="monthly">
    <subscription price="24" per="year"/>
  <book style="novel" id="myfave">
      <degree from="Trenton U">B.A.</degree>
      <degree from="Harvard">Ph.D.</degree>
      <publication>Still in Trenton</publication>
      <publication>Trenton Forever</publication>
    <price intl="Canada" exchange="0.7">6.50</price>
      <p>It was a dark and stormy night.</p>
      <p>But then all nights in Trenton seem dark and
      stormy to someone who has gone through what
      <emph>I</emph> have.</p>
  <my:book xmlns:my="uri:mynamespace" style="leather" price="29.50">
    <my:title>Who's Who in Trenton</my:title>
    <my:author>Robert Bob</my:author>

for which you also have a readily available list of XPath Examples. Make sure to assign True value to SaveOperationResults property and create a File Connection for Destination pointing to a file (its location and name are not relevant), which content you can subsequently review. SecondOperand (also configured via Direct input) will consist of the XPath query that will extract an arbitrarily chosen portion of the sample XML document. Note that the output of the task execution will depend on the values of PutResultInOneNode and XPathOperation properties grouped in the XPath Options section of the task editor window. The first property dictates (depending on whether it is set to True or False) whether the results will be presented in the XML format, with generic <ResultRootNode> as the root. The second one can be assigned one of the following three values:

  • Evaluation - useful when dealing with XPath-based functions (for their complete listing, refer to the MSDN Web site). For example, setting PutResultsInOneNode to True and XPathOperation to sum(/bookstore/book/price) will yield the total price of all books in our inventory in the format <ResultRootNode>73.5</ResultRootNode>, while keeping PutResultsInOneNode as False, would output the result as 73.5.
  • Node list - returns nodes pointed to by the SecondOperand property as an XML fragment. For example, let's assume we want to retrieve first names of all book authors. This can be accomplished by assigning /bookstore/book/author/first-name to the SecondOperand. With False PutResultInOneNode, the outcome of the task execution would be:

    If you switch PutResultInOneNode to True, the output will take the form:

  • Values - allow you to retrieve the text value of all nodes pointed to by XPath expression, concatenated into a single character string. For example, to extract all information about the author Toni Bob, enter //author[first-name="Toni" and last-name="Bob"] as the SecondOperand, which should return (with PutResultInOneNode set to False) ToniBobB.A.Ph.D.PulitzerStill in TrentonTrenton Forever (or <ResultRootNode>ToniBobB.A.Ph.D.PulitzerStill in TrentonTrenton Forever</ResultRootNode> with PutResultInOneNode assigned True value).

Note that you can test results independently by taking advantage of other applications capable of processing XML documents with XPath, such as Microsft's XML Notepad 2007 (requires .NET Framework 2.0) or third party XML Editors such as freely downloadable Exchanger XML Lite Editor. If you have Microsoft Visual Studio, you also can install the Visual XPath free add-in, which allows you to evaluate your XPath statements against corresponding XML documents.

In our next article, we will complete the overview of the XML Control Flow task by describing in more details three remaining operation types - merge, diff, and patch.

» See All Articles by Columnist Marcin Policht

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM