Introduction

Let's begin with the inventory of pets at Rosie's. A big change is in the works: a merger between this pet shop and the one managed by Lizzie. An investigation finds that their inventories are in different XML formats. As you have seen before, Rosie's inventory is laid out as in Listing 1. Lizzie, however, uses the very different scheme seen in Listing 9.

There are quite a few differences between the two schemes.

• Generally speaking, Rosie represents as subelements what Lizzie keeps as attributes
• Lizzie's stock consists of <item> subelements; the corresponding nodes in Rosie's documents are named after the type of pet: <dog>, <cat>, etc.
• Rosie has names for all of her stock; Lizzie doesn't
• Rosie stores prices but Lizzie stores costs
• Lizzie's inventory lists ages while Rosie's lists dates of birth ('dob')
• Lizzie has a location for each item, Rosie doesn't
• Rosie has owners for dogs but no other pets; Lizzie doesn't have owners at all

You will convert Lizzie's age and Rosie's European-style dates of birth into a common format, but this won't happen immediately. First you will convert ages to dates in the current format, and then as in a later step involving Perl, convert the dates to a standard YYYY-MM-DD format.

After some study you settle on an SQL table specification for storing the data in a data base management system (DBMS). You can see the SQL for creating the database table in Listing 6. The consensus is that this most easily maps to Lizzie's XML data representation, where columns are mapped to attributes. You decide to convert the two different XML files to a common layout, merge them by storing them in an SQL database, and then generate a common stock file, an XML document, from the database.

Back to top

Getting started

As in the case of the first two articles in this series, you will need some additional Perl modules to be able to run the code in this article. UNIX® or Linux™ users can get these from the cpan repository; Windows® users will generally get them using ppm (see Resources for links). The modules concerned are:

• DBI -- Relational database interface
• XML::LibXSLT -- For style sheet processing

Back to top

Stylesheets and the DOM

You decide to use a XSLT stylesheet (rosie.xsl; see Listing 2) to convert Rosie's inventory towards the common format. This necessitates parsing both the XML document (the inventory in Listing 1) and the stylesheet, then using XSLT to generate the new document.

<pets>
  <cat><name>Madness</name>
    <dob>1 February 2004</dob><price>150</price></cat>
  <dog><name>Maggie</name>
    <dob>12 October 
2002</dob><price>75</price><owner>Rosie</owner></dog>
  <cat><name>Little</name>
    <dob>23 June 2006</dob><price>25</price></cat>
</pets>

The style sheet converts each subelement into an <item> element, creating a new attribute type whose value is what used to be the name of the element, its tag. It also converts all of the lower-level subelements to attributes and their contents to attribute values (see Listing 2).

<xsl:stylesheet version="1.0">
  <xsl:template match="/pets">
    <xsl:element name="stock">
      <xsl:for-each select="*">
        <xsl:element name="item">
          <xsl:attribute name="type">
            <xsl:value-of select="name()"/>
          </xsl:attribute>
          <xsl:for-each select="*">
            <xsl:attribute name="{name()}">
              <xsl:apply-templates/>
            </xsl:attribute>
          </xsl:for-each> 
          <xsl:attribute name="cost">0.00</xsl:attribute>
          <xsl:attribute name="location">Rosies</xsl:attribute>
        </xsl:element>
      </xsl:for-each>
    </xsl:element><
  </xsl:template>
</xsl:stylesheet>

You also add cost and location attributes using the stylesheet, because it is more convenient to do so here, where each costs a single line of code, rather than doing so later in a SAX handler, where it seems likely to involve more work.

Because you make this transformation more than once (here and in the next section), you put the common code in a Perl module, RosieStock.pm (see Listing 3).

package RosieStock;
use strict;
use XML::LibXML;
use XML::LibXSLT;
use XML::LibXML::SAX::Generator;

sub new { 
    my $class = shift;
    my $parser = XML::LibXML->new;
    my $xslt   = XML::LibXSLT->new;
    my $style  = $parser->parse_file('rosie.xsl');
    my $self = { 
        PARSER      => $parser,
        STYLESHEET  => $xslt->parse_stylesheet($style),
    };
    bless $self, $class;
}
sub parseAndStyle {
    my ($self, $inFile) = @_;
    $self->{DOC}       = $self->{PARSER}->parse_file($inFile);
    $self->{STYLED}    = $self->{STYLESHEET}->transform($self->{DOC});
    return $self;
}
sub toString {
    my ($self) = shift;
    $self->{STYLESHEET}->output_string( $self->{STYLED} );
}

Make the transformation with the snippet of Perl in Listing 4. This parses the inventory and stylesheet, applies the stylesheet, and prints out the results (Listing 5), so that you can see your progress.

#!/usr/bin/perl -w
use strict;
use DBI;
use RosieStock;   

my $fixRosie = RosieStock->new;
print $fixRosie->parseAndStyle('pets.xml')->toString;

As you can see, the subelements in the original document are now attributes. The actual content of the inventory has not changed except that cost and location attributes are added (see Listing 5).

<?xml version="1.0"?>
<stock>
    <item type="cat" name="Madness" dob="1 February 2004" price="150" 
    cost="0.00" location="Rosies"/>
  <item type="dog" name="Maggie" dob="12 October 2002" price="75" 
    owner="Rosie" cost="0.00" location="Rosies"/>
  <item type="cat" name="Little" dob="23 June 2006" price="25" 
    cost="0.00" location="Rosies"/>
</stock>

Back to top

SAX pipelines

As you saw in Part 2 (see Resources), while tree parses and therefore XSLT transformations are limited to XML documents that can fit in memory and/or have some definite length, SAX can handle XML streams of arbitrary size and length. Plus you can have the best of both worlds: you can parse, navigate, and transform XML documents as trees, and then casually walk those trees to generate events that are fed into SAX pipelines. Since you have Rosie's stock in tree form, you can do that right now. You will take the parsed and transformed tree and feed that into a SAX handler on its way to the database.

Back to top

Walking a DOM tree

The first SAX pipeline you build consists of two parts: a generator which walks the DOM tree to generate SAX events and a handler which captures those events and writes them to a database. The generator is a method added to the RosieStock package; see Listing 6. The handler, which writes rows to a PETS.STOCK table in a pre-existing database is shown in Listing 7. The Perl program that creates and runs the SAX pipeline is in Listing 8.

sub generate {
    my ($self, $handler) = @_;
    my $generator 
        = XML::LibXML::SAX::Generator->new(Handler=>$handler);
    $generator->generate( $self->{STYLED} );
}
1;

The addition to RosieStock.pm is a XML::LibXML::SAX::Generator, which will walk the DOM tree, the styled XML document created earlier in the module, and generate stream of SAX events. These are passed to the handler.

package Stock2DB;
use XML::SAX::Base;
@ISA = ('XML::SAX::Base');
use strict;
use DBI;
use constant TYPE0 => 0;
use constant TYPE1 => 1;
use constant TYPE2 => 2;

sub new {
    my $class = shift;
    my $self  = { COUNTER => 0, HASHTYPE => TYPE0 };
    return bless {}, $class;
}

my @months = ( 'January', 'February', 'March', 'April', 'May', 'June',
               'July', 'August', 'September', 'October', 'November', 
               'December');
sub _dobFromAge($) {    # precision is not an issue!
    my $age = shift;
    my $tob = time() - $age * ((86400) * 365.25);
    my ($day, $month, $year) = (localtime($tob))[ 3, 4, 5 ];
    $year += 1900;
    return "$day $months[$month] $year";
}
# see Perl SAX 2.1 spec for details regarding $data (hash ref)

sub _getVal($$) {
    my ($hash, $name) = @_;
    return (defined $hash->{$name}) ? ''.$hash->{$name} : '';
}
# expect attribute names to appear in hash
sub _getAttrsTYPE1($) {
    my $attrs = shift;
    return (
        _getVal($attrs, 'type'),   _getVal($attrs, 'name'),  
        _getVal($attrs, 'cost'),   _getVal($attrs, 'price'), 
        _getVal($attrs, 'dob'),    _getVal($attrs, 'location'),
        _getVal($attrs, 'age'),
    ); 
}
# expect prefixed attribute names to key secondary hash
sub _getFromSecondary ($$) {
    my ($hash, $name) = @_;
    my $value;
    my $ref = $hash->{ "{}$name" };
    if (defined $ref) {
        $value = $ref->{Value};
    }
    return $value;
}
sub _getAttrsTYPE2($) {
    my $attrs = shift;
    my ($type, $name, $cost, $price, $dob, $location, $age) = (
        _getFromSecondary ($attrs, 'type'),     
        _getFromSecondary ($attrs, 'name'),     
        _getFromSecondary ($attrs, 'cost'),     
        _getFromSecondary ($attrs, 'price'),     
        _getFromSecondary ($attrs, 'dob'),     
        _getFromSecondary ($attrs, 'location'),     
        _getFromSecondary ($attrs, 'age'),     
    ); 
    return ($type, $name, $cost, $price, $dob, $location, $age);
}
sub start_element {
    my ($self, $data) = @_;
    my $elmName = $data->{Name};
    if ($elmName eq 'item') {
        my $attrs = $data->{Attributes};
        my $hashType = $self->{HASHTYPE};
        $hashType = TYPE0 if !defined($hashType);
        if ($hashType == TYPE0) {
            my @keyList = keys %$attrs;
            my $attr0 = $keyList[0];
            if ($attr0 =~ /^{}/) {
                $hashType = TYPE2;
            } else {
                $hashType = TYPE1;
            }
            $self->{HASHTYPE} = $hashType;
            $self->{COUNTER}  = 0;
        }
        my ($type, $name, $cost, $price, $dob, $location, $age);
        if ($hashType == TYPE1) {
            ($type, $name, $cost, $price, $dob, $location, $age) = 
                _getAttrsTYPE1( $attrs );
        } else {
            ($type, $name, $cost, $price, $dob, $location, $age) = 
                _getAttrsTYPE2( $attrs );
        }
        # do any necessary fixups
        if (!defined($name) or $name eq '') {
            $name = "$type-" . $self->{COUNTER}++;
        }
        if (!defined($cost) or $cost eq '' or ($cost eq '0.00') ) { 
            $cost =  0.6 * $price;
        }
        if (!defined($price) or $price eq '') {
            $price = 1.667 * $cost;
        } 
        if (!defined($dob) or $dob eq '') {
            $dob = _dobFromAge($age);
        }

        my $insert = 
            'INSERT INTO STOCK (type, name, cost, price, dob, location) '
            . "VALUES( \"$type\",  \"$name\", \"$cost\", "
            .         "\"$price\", \"$dob\",  \"$location\" )";
            
        $self->{DBH}->do ( $insert );
    }
    $self->SUPER::start_element($data);
}
sub start_document {
    my ($self, $data) = @_;
    $self->{DBH} = DBI->connect('DBI:mysql:PETS', 'genny', 'joshsgirl')
          or die "couldn't connect to PETS: " . DBI->errstr;
    $self->SUPER::start_document($data);
}
sub end_document {
    my ($self, $data) = @_;
    $self->SUPER::end_document($data);
    $self->{DBH}->disconnect;
}
1;

The handler is an instance of Stock2DB, your first SAX handler. It extends XML::SAX::Base, and relies on that package to deal with (that is, ignore) any events that it doesn't handle.

In this case, the only events that you need to handle are the beginning and ending of the document, and the start of an element. At start_document you open the database that you write to. At end_document you close it. At the beginning of an element, you look at its name. You are only interested in <item> elements. When you see one of these, you extract the attributes from the hash passed as a parameter and use these to build the SQL INSERT statement, which you then pass to DBI for execution.

Some complications arise from changes in the Perl SAX binding. To deal with the two storage variants for attribute values, the code detects the type (called TYPE1 or TYPE2 here) and then extracts attribute values appropriately. Here the attributes are stored in $data->{Attributes} by name.

This is essentially due to the fact that the XML::LibXML::SAX::Generator package is old. Later, when you use the thoroughly up to date XML::SAX::ExpatXS, you need to deal with the second way to store attributes, in which the attribute name, such as type, is used to construct a key {}type that points to another hash containing further information about the attribute (its value, prefix, and so on).

All of this is orchestrated in part3b.pl, in Listing 8. The script first makes a clean database table. Then it parses and transforms the inventory file, pets.xml. You will walk the DOM tree that this creates. The end of the SAX pipeline will be the handler, so you create that as an instance of Stock2DB. This allows you to instantiate the beginning of the pipeline, the generator that walks the DOM tree. When you invoke that, it writes the contents of the tree to the database table just created.

#!/usr/bin/perl -w
use strict;
use DBI;
use RosieStock;     
use Stock2DB;

# create a clean database table 
my $dbh = DBI->connect('DBI:mysql:PETS', 'genny', 'joshsgirl')
          or die "couldn't connect to PETS: " . DBI->errstr;

$dbh->do('USE PETS');
$dbh->do('DROP TABLE IF EXISTS PETS.STOCK');
my $create = 'CREATE TABLE STOCK (           '
    . 'id       INT NOT NULL AUTO_INCREMENT, '
    . 'type     VARCHAR(64)     NOT NULL,    '
    . 'name     VARCHAR(64),  '
    . 'cost     DECIMAL(4,2), '
    . 'price    DECIMAL(4,2)    NOT NULL,    '
    . 'dob      VARCHAR(32),  ' # should be DATE, YYYY-MM-DD
    . 'location VARCHAR(64),  ' 
    . 'PRIMARY KEY (id) )     ';

$dbh->do($create)   
    or die "can't create table PETS.STOCK: " . $dbh->errstr;
$dbh->disconnect;

my $fixRosie = RosieStock->new;
$fixRosie->parseAndStyle('pets.xml');
my $dbWriter = Stock2DB->new;
$fixRosie->generate( $dbWriter );

So what do you have at this point? A database containing only the items in Rosie's original inventory, converted to your standard format.

If you look carefully at the code in Listing 7, you will see that Stock2DB also looks for missing or zero costs and guesstimates their value from prices, and similarly calculates missing prices from costs by applying a fixed markup.

Back to top

Writing XML to a database

Your next step is to load the database with Lizzie's inventory, shown in Listing 9.

<stock>
<item type="iguana" cost="124.42" location="stockroom" age="1"/>
<item type="pig" cost="15" location="floor" age="0.5"/>
<item type="parrot" cost="700" location="cage" age="6"/>
<item type="pig" cost="117.50" location="floor" age="3.2"/>
</stock>

Given the machinery available in Stock2DB.pm (see Listing 7), to load Lizzie's inventory into the database, you only need to initialize an instance of Stock2DB, which will write anything passed to it to your database, and then start the parser (see Listing 10).

#!/usr/bin/perl -w
#use strict;
use XML::SAX::ParserFactory;
use Stock2DB;
$XML::SAX::ParserPackage = "XML::SAX::ExpatXS";

my $parser = XML::SAX::ParserFactory->parser(Handler => Stock2DB->new);
eval { $parser->parse_file('pets2.xml') };
die "can't parse Lizzie's stock file: $@"   if $@;

You now have loaded both stock files into the database. If you query the database, you get the results in Listing 11. Notice that all pets are now named, costs and prices have been calculated where missing, ages have been converted to approximate dates of birth, and all items now have a location.

mysql > select * from PETS.STOCK;
+----+--------+----------+--------+--------+------------------+-----------+
| id | type   | name     | cost   | price  | dob              | location  |
+----+--------+----------+--------+--------+------------------+-----------+
|  1 | cat    | Madness  |  90.00 | 150.00 | 1 February 2004  | Rosies    |
|  2 | dog    | Maggie   |  45.00 |  75.00 | 12 October 2002  | Rosies    |
|  3 | cat    | Little   |  15.00 |  25.00 | 23 June 2006     | Rosies    |
|  4 | iguana | iguana-0 | 124.42 | 207.40 | 16 November 2005 | stockroom |
|  5 | pig    | pig-1    |  15.00 |  25.00 | 18 May 2006      | floor     |
|  6 | parrot | parrot-2 | 700.00 | 999.99 | 16 November 2000 | cage      |
|  7 | pig    | pig-3    | 117.50 | 195.87 | 5 September 2003 | floor     |
+----+--------+----------+--------+--------+------------------+-----------+
7 rows in set (0.00 sec)
mysql>

Back to top

Database driving a SAX pipeline

Your final goal is to dump the database that you just created back to XML format. The dates are in a form that is less suitable for SQL databases, but leave the conversion of these to MySQL's preferred YYYY-MM-DD form using Date::Calc to the reader.

You use XML::Generator::DBI to drive the SAX pipeline. Notice that you have to provide names for the root element and subelements in the XML document, because these are necessarily lost on storage to an SQL table. Also, by default XML::Generator::DBI outputs column values as subelements. You want them to be attributes in the XML document, so set the AsAttributes option to 1. Listing 12 does the job, producing the XML output in Listing 13.

#!/usr/bin/perl -w
use strict;
use DBI;
use XML::Generator::DBI;
use XML::SAX::Writer;

my $dbh = DBI->connect('DBI:mysql:PETS', 'genny', 'joshsgirl')
          or die "couldn't connect to PETS: " . DBI->errstr;
my $select = qq ( SELECT * FROM STOCK );
my $writer = XML::SAX::Writer->new(Output => 'inventory.xml');
my $generator = XML::Generator::DBI->new(
                            dbh          => $dbh,
                            Handler      => $writer, 
                            
                );
$generator->execute($select, undef,
                           AsAttributes => 1,
                           QueryElement => undef,
                           RootElement  => 'stock',
                           RowElement   => 'item',
);
$dbh->disconnect;

The output in Listing 13 was pretty-printed to make it more readable. The generator has scrambled the order of the attributes. If this were significant -- it isn't in your application -- further processing steps could correct this.

<stock>
  <item cost="90.00" dob="1 February 2004" location="Rosies" 
    name="Madness" price="150.00" id="1" type="cat"/>
  <item cost="45.00" dob="12 October 2002" location="Rosies" 
    name="Maggie" price="75.00" id="2" type="dog"/>
  <item cost="15.00" dob="23 June 2006" location="Rosies" 
    name="Little" price="25.00" id="3" type="cat"/>
  <item cost="124.42" dob="16 November 2005" location="stockroom" 
    name="iguana-0" price="207.40" id="4" type="iguana"/>
  <item cost="15.00" dob="18 May 2006" location="floor" 
    name="pig-1" price="25.00" id="5" type="pig"/>
  <item cost="700.00" dob="16 November 2000" location="cage" 
    name="parrot-2" price="999.99" id="6" type="parrot"/>
  <item cost="117.50" dob="5 September 2003" location="floor" 
    name="pig-3" price="195.87" id="7" type="pig"/>
</stock>

While setting up XML::Generator::DBI was quite straightforward, running the Perl script in Listing 12 produced a number of annoying warning messages. I eliminated these with a small number of fixes to the module; these should be fixed in the next release (you have been working with version 1.0). In the interim, you can get a patch as a diff file from the author.

Back to top

Summary

This series began by showing you that you can convert most XML documents in a single step (using XML::Simple) to and from easily manipulated Perl data structures.

Part 2 introduced more powerful tools for parsing XML: DOM-style tree parsers and the SAX event-based parsers. You learned about XML::SAX::Base and how you can use it to build sources, handlers, and sinks of SAX events.

Finally, Part 3 put it all together for you, adding relational databases to the mix. You built a parse tree, transformed it, then walked the transformed tree, generating SAX events that fed it into a database. You then used a SAX pipeline to parse a second XML document, transform it, and add it to the same database, effecting a merge of your two inventories. In the last step, you used the database holding the merged documents to generate another stream of SAX events, which you transformed further in another pipeline to create a final common document.

Resources

Learn

• Read the earlier articles in this series:
  • Part 1: See how to convert most XML documents in a single step (using XML::Simple) to and from easily manipulated Perl data structures.
  • Part 2: Meet more powerful tools for parsing XML: DOM-style tree parsers and the SAX event-based parsers.
  
  
• Tutorial on the use of references in Perl: Study complete documentation about all aspects of Perl references and nested data structures.
  
  
• Perl developers: Fill your XML toolbox (Parand Darugar, developerWorks, June 2001): Explore an overview of some 20 essential tools and libraries for manipulating XML with Perl.
  
  
• Effective XML processing with DOM and XPath in Perl (Parand Darugar, developerWorks, October 2001): Examine how to make effective and efficient use of DOM.
  
  
• High-order perl (Mark Jason Dominus, 2005): Read a book about functional programming techniques in Perl and how to write functions that can modify and manufacture other functions.
  
  
• IBM XML certification: Find out how you can become an IBM-Certified Developer in XML and related technologies.
  
  
• XML technical library: See the developerWorks XML Zone for a wide range of technical articles and tips, tutorials, standards, and IBM Redbooks.
  
  
• developerWorks technical events and webcasts: Stay current with technology in these sessions.
  
  

Get products and technologies

• Document Object Model spec: Get the details on a platform- and language-neutral interface that allows programs and scripts to dynamically access and update the content, structure and style of documents.
  
  
• Perl SAX 2.1 binding: Get a document that describes the version of SAX used by Perl modules.
  
  
• Perl: Get the most recent version and put it in action.
  
  
• The huge CPAN Perl library (with 18 million Google hits!): Visit the Comprehensive Perl Archive Network for links to all of the modules mentioned in this article.
  
  
• PPM, Perl Package Manager for Windows: Get a tool that allows you to install, remove, upgrade, and otherwise manage the use of common Perl CPAN modules (like Tk and DBI) with ActivePerl.
  
  
• Grant McLean's XML::Simple: Try the XML::Simple module for a simple API layer on top of an underlying XML parsing module.
  
  
• XML specification: Explore this complete description of the Extensible Markup Language (XML).
  
  
• Introduction to XML (Doug Tidwell, developerWorks, August 2002): For a gentler introduction to XML, take this tutorial that covers how XML developed, how it's shaping the future of electronic commerce, a variety of XML programming interfaces and standards, and two case studies that show how companies solve business problems with XML.
  
  
• XPath 1.0: Get the specification for a language to navigate the DOM tree.
  
  
• XSLT 1.0 specification: Learn about transforming one XML document into another.
  
  
• Dare to script tree-based XML with Perl: Find out how to work with tree-based document models (Parand Darugar, developerWorks, July 2000): Get a solid introduction to tree-based XML parsing with Perl.
  
  
• IBM trial software: Build your next development project with trial software available for download directly from developerWorks.
  
  

Discuss

• developerWorks XML zone: Share your thoughts: Share your ideas and comments about XML zone content with the Linda Meyer, the XML zone editor.
  
  
• XML zone discussion forums: Participate in any of several XML-centered forums.
  
  
• developerWorks blogs: Get involved in the developerWorks community.
  
  

About the author

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Getting started
• Stylesheets and the DOM
• SAX pipelines
• Walking a DOM tree
• Writing XML to a database
• Database driving a SAX pipeline
• Summary
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into XML on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers