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

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”>
<author>
<first-name>Joe</first-name>
<last-name>Bob</last-name>
<award>Trenton Literary Review Honorable Mention</award>
</author>
<price>12</price>
</book>
<book style=”textbook”>
<author>
<first-name>Mary</first-name>
<last-name>Bob</last-name>
<publication>Selected Short Stories of
<first-name>Mary</first-name>
<last-name>Bob</last-name>
</publication>
</author>
<editor>
<first-name>Britney</first-name>
<last-name>Bob</last-name>
</editor>
<price>55</price>
</book>
<magazine style=”glossy” frequency=”monthly”>
<price>2.50</price>
<subscription price=”24″ per=”year”/>
</magazine>
<book style=”novel” id=”myfave”>
<author>
<first-name>Toni</first-name>
<last-name>Bob</last-name>
<degree from=”Trenton U”>B.A.</degree>
<degree from=”Harvard”>Ph.D.</degree>
<award>Pulitzer</award>
<publication>Still in Trenton</publication>
<publication>Trenton Forever</publication>
</author>
<price intl=”Canada” exchange=”0.7″>6.50</price>
<excerpt>
<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>
<definition-list>
<term>Trenton</term>
<definition>misery</definition>
</definition-list>
</excerpt>
</book>
<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>
</my:book>
</bookstore>

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:

    <first-name>Joe</first-name>
    <first-name>Mary</first-name>
    <first-name>Toni</first-name>

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


    <ResultRootNode>
    <first-name>Joe</first-name>
    <first-name>Mary</first-name>
    <first-name>Toni</first-name>
    </ResultRootNode>

  • 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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles