Parsing PubMed XML to submit to mySQL database (XML::Twig)

Question

I'm new to XML::Twig, and I'm trying to parse a PubMed XML 2.0 esummary final to place into a mySQL database. I've gotten this far:

#!/bin/perl -w
use strict;
use DBI;
use XML::Twig;

my $uid = "";
my $title = "";
my $sortpubdate = "";
my $sortfirstauthor = "";
my $dbh = DBI->connect ("DBI:mysql:medline:localhost:80",
                           "root", "mysql");
my $t= new XML::Twig(   twig_roots => { 'DocumentSummary' => $uid => \&submit },
                        twig_handlers => { 'DocumentSummary/Title' => $title, 'DocumentSummary/SortPubDate' => $sortpubdate, 'DocumentSummary/SortFirstAuthor' => $sortfirstauthor});
$t->parsefile('20112.xml'); 
$dbh->disconnect();
exit;

sub submit
    {   my $insert= $dbh->prepare( "INSERT INTO medline_citation (uid, title, sortpubdate, sortfirstauthor) VALUES (?, ?, ?, ?);");
        $insert->bind_param( 1, $uid);
        $insert->bind_param( 2, $title);
        $insert->bind_param( 3, $sortpubdate);
        $insert->bind_param( 4, $sortfirstauthor);
        $insert->execute();
        $t->purge;
    }

But Perl seems to stall for some reason. Am I doing this right? I'm trying to use twig_roots to decrease the amount of parsing since I'm only interested in a few fields (these are large files).

Here is an example of the XML:

<DocumentSummary uid="22641317">
    <PubDate>2012 Jun 1</PubDate>
    <EPubDate></EPubDate>
    <Source>Clin J Oncol Nurs</Source>
    <Authors>
        <Author>
            <Name>Park SH</Name>
            <AuthType>
                Author
            </AuthType>
            <ClusterID>0</ClusterID>
        </Author>
        <Author>
            <Name>Knobf MT</Name>
            <AuthType>
                Author
            </AuthType>
            <ClusterID>0</ClusterID>
        </Author>
        <Author>
            <Name>Sutton KM</Name>
            <AuthType>
                Author
            </AuthType>
            <ClusterID>0</ClusterID>
        </Author>
    </Authors>
    <LastAuthor>Sutton KM</LastAuthor>
    <Title>Etiology, assessment, and management of aromatase inhibitor-related musculoskeletal symptoms.</Title>
    <SortTitle>etiology assessment and management of aromatase inhibitor related musculoskeletal symptoms </SortTitle>
    <Volume>16</Volume>
    <Issue>3</Issue>
    <Pages>260-6</Pages>
    <Lang>
        <string>eng</string>
    </Lang>
    <NlmUniqueID>9705336</NlmUniqueID>
    <ISSN>1092-1095</ISSN>
    <ESSN>1538-067X</ESSN>
    <PubType>
        <flag>Journal Article</flag>
    </PubType>
    <RecordStatus>
        PubMed - in process
    </RecordStatus>
    <PubStatus>4</PubStatus>
    <ArticleIds>
        <ArticleId>
            <IdType>pii</IdType>
            <IdTypeN>4</IdTypeN>
            <Value>N1750TW804546361</Value>
        </ArticleId>
        <ArticleId>
            <IdType>doi</IdType>
            <IdTypeN>3</IdTypeN>
            <Value>10.1188/12.CJON.260-266</Value>
        </ArticleId>
        <ArticleId>
            <IdType>pubmed</IdType>
            <IdTypeN>1</IdTypeN>
            <Value>22641317</Value>
        </ArticleId>
        <ArticleId>
            <IdType>rid</IdType>
            <IdTypeN>8</IdTypeN>
            <Value>22641317</Value>
        </ArticleId>
        <ArticleId>
            <IdType>eid</IdType>
            <IdTypeN>8</IdTypeN>
            <Value>22641317</Value>
        </ArticleId>
    </ArticleIds>
    <History>
        <PubMedPubDate>
            <PubStatus>entrez</PubStatus>
            <Date>2012/05/30 06:00</Date>
        </PubMedPubDate>
        <PubMedPubDate>
            <PubStatus>pubmed</PubStatus>
            <Date>2012/05/30 06:00</Date>
        </PubMedPubDate>
        <PubMedPubDate>
            <PubStatus>medline</PubStatus>
            <Date>2012/05/30 06:00</Date>
        </PubMedPubDate>
    </History>
    <References>
    </References>
    <Attributes>
        <flag>Has Abstract</flag>
    </Attributes>
    <PmcRefCount>0</PmcRefCount>
    <FullJournalName>Clinical journal of oncology nursing</FullJournalName>
    <ELocationID></ELocationID>
    <ViewCount>0</ViewCount>
    <DocType>citation</DocType>
    <SrcContribList>
    </SrcContribList>
    <BookTitle></BookTitle>
    <Medium></Medium>
    <Edition></Edition>
    <PublisherLocation></PublisherLocation>
    <PublisherName></PublisherName>
    <SrcDate></SrcDate>
    <ReportNumber></ReportNumber>
    <AvailableFromURL></AvailableFromURL>
    <LocationLabel></LocationLabel>
    <DocContribList>
    </DocContribList>
    <DocDate></DocDate>
    <BookName></BookName>
    <Chapter></Chapter>
    <SortPubDate>2012/06/01 00:00</SortPubDate>
    <SortFirstAuthor>Park SH</SortFirstAuthor>
</DocumentSummary>

Thanks!

Answer 1

The way I'd do this is to have a single handler, for the DocumentSummary, that feeds the DB, then purges the record. There is no need to get fancier than this.

Also, I find DBIx::Simple, well, simpler to use than raw DBI, it takes care of preparing and caching statements for me:

#!/bin/perl

use strict;
use warnings;

use DBIx::Simple;
use XML::Twig;

my $db = DBIx::Simple->connect ("dbi:SQLite:dbname=t.db"); # replace by your DSN

my $t= XML::Twig->new(   twig_roots => { DocumentSummary => \&submit },)
                ->parsefile('20112.xml'); 

$db->disconnect();
exit;

sub submit
    { my( $t, $summary)= @_;
      my $insert= $db->query( "INSERT INTO medline_citation (uid, title, sortpubdate, sortfirstauthor) VALUES (?, ?, ?, ?);",
                              $summary->att( 'uid'),
                              map { $summary->field( $_) } ( qw( Title SortPubDate SortFirstAuthor))
                            );
      $t->purge;
    }

If you're wondering about map { $summary->field( $_) } ( qw( Title SortPubDate SortFirstAuthor)), it's just a fancier (and IMHO more maintainable) way to write $summary->field( 'Title'), $summary->field( 'SortPubDate'), $summary->field( 'SortFirstAuthor' )

Answer 2

Your syntax of handlers is wrong. See the documentation for examples:

my $twig=XML::Twig->new(   
    twig_handlers => 
      { title   => sub { $_->set_tag( 'h2') }, # change title tags to h2
        para    => sub { $_->set_tag( 'p')  }, # change para to p
        hidden  => sub { $_->delete;       },  # remove hidden elements
        list    => \&my_list_process,          # process list elements
        div     => sub { $_[0]->flush;     },  # output and free memory
      },
    pretty_print => 'indented',                # output will be nicely formatted
    empty_tags   => 'html',                    # outputs <empty_tag />
                         );