80.802
Extract and Load Large XML to Database

Introduction

Structure of XML

<?xml version="1.0" encoding="UTF-8"?>
<PubmedArticleSet>
  <PubmedArticle PMID="1">
    <Article>
      <Journal>
        <ISSN IssnType="Print">0006-2944</ISSN>
        <JournalIssue CitedMedium="Print">
          <Volume>13</Volume>
          <Issue>2</Issue>
          <PubDate>
            <Year>1975</Year>
            <Month>Jun</Month>
          </PubDate>
        </JournalIssue>
        <Title>Biochemical medicine</Title>
        <ISOAbbreviation>Biochem Med</ISOAbbreviation>
      </Journal>
      <Language>eng</Language>
      <ArticleTitle>Formate assay in body fluids</ArticleTitle>
      <AuthorList CompleteYN="Y">
        <Author ValidYN="Y">
          <LastName>Makar</LastName>
          <ForeName>A B</ForeName>
          <Initials>AB</Initials>
        </Author>
        <Author ValidYN="Y">
          <LastName>McMartin</LastName>
          <ForeName>K E</ForeName>
          <Initials>KE</Initials>
        </Author>
        ...
      </AuthorList>
    </Article>
  </PubmedArticle>
  ...
</PubmedArticleSet>

Load XML

library(XML)

xmlURL <- "http://artificium.us/lessons/06.r/l-6-183-extractxml-data-in-r/pubmed-xml-tfm/pubmed22n0001-tf.xml"

system.time(
  xmlDOM <- xmlParse(xmlURL, validate=F)
)

##    user  system elapsed 
##   0.454   0.191  52.589

xmlFile <- "pubmed-v3.xml"

system.time(
  xmlDOM <- xmlParse(xmlFile, validate=F)
)

##    user  system elapsed 
##   0.432   0.083   0.524

root <- xmlRoot(xmlDOM)
numArticles <- xmlSize(root)

cat("Articles to process: ", numArticles)

## Articles to process:  30000

system.time(
for (a in 1:numArticles) {
  anArticle <- root[[a]]
}
)

##    user  system elapsed 
##   9.780   0.026   9.819

Get an article’s title

system.time(
for (a in 1:numArticles) {
  anArticle <- root[[a]]
}
)

##    user  system elapsed 
##  10.352   0.032  10.472

Summary

This lesson provided several specific recommendations and programming practices for extracting data from a large XML and transforming it to a relational schema.

---

Files & Resources

All Files for Lesson 80.802

---

References

No references.

Errata

None collected yet. Let us know.