DB2 10.5 for Linux, UNIX, and Windows

pureXML and Perl

The DBD::DB2 driver supports DB2® pureXML®. Support for pureXML allows more direct access to your data through the DBD::DB2 driver and helps to decrease application logic by providing more transparent communication between your application and database.

With pureXML support, you can directly insert XML documents into your DB2 database. Your application no longer needs to parse XML documents because the pureXML parser is automatically run when you insert XML data into the database. Having document parsing handled outside your application improves application performance and reduces maintenance efforts. Retrieval of XML stored data with the DBD::DB2 driver is easy as well; you can access the data using a BLOB or record.

For information about the DB2 Perl Database Interface and how to download the latest DBD::DB2 driver, see http://search.cpan.org/~ibmtordb2/.

Example

The example is a Perl program that uses pureXML:

#!/usr/bin/perl
use DBI;
use strict ;

# Use DBD:DB2 module:
#   to create a simple DB2 table with an XML column
#   Add one row of data
#   retreive the XML data as a record or a LOB (based on $datatype).
   
# NOTE: the DB2 SAMPLE database must already exist.

my $database='dbi:DB2:sample';
my $user='';
my $password='';

my $datatype = "record" ;
#  $datatype = "LOB" ;

my $dbh = DBI->connect($database, $user, $password)
  or die "Can't connect to $database: $DBI::errstr";

# For LOB datatype, LongReadLen = 0 -- no data is retrieved on initial fetch
$dbh->{LongReadLen} = 0 if $datatype eq "LOB" ; 

# SQL CREATE TABLE to create test table
my $stmt = "CREATE TABLE xmlTest (id INTEGER, data XML)";
my $sth = $dbh->prepare($stmt); 
$sth->execute();

#insert one row of data into table
insertData() ;

# SQL SELECT statement returns home phone element from XML data
$stmt = qq( 
	SELECT XMLQUERY ('
	\$d/*:customerinfo/*:phone[\@type = "home"] ' 
	passing data as "d")
	FROM xmlTest 
) ;


# prepare and execute SELECT statement
$sth = $dbh->prepare($stmt); 
$sth->execute();
	
# Print data returned from select statement
if($datatype eq "LOB") {
    printLOB() ;
}
else {
	printRecord() ;
}

# Drop table
$stmt = "DROP TABLE xmlTest" ;
$sth = $dbh->prepare($stmt); 
$sth->execute();

warn $DBI::errstr if $DBI::err;

$sth->finish;
$dbh->disconnect;


##############


sub printRecord {
	print "output data as as record\n" ;
	
	while( my @row = $sth->fetchrow ) 
	{ 
		print $row[0] . "\n"; 
	}

	warn $DBI::errstr if $DBI::err;
}


sub printLOB {
	print "output as Blob data\n" ;

	my $offset = 0; 
	my $buff=""; 
	$sth->fetch(); 
	while( $buff = $sth->blob_read(1,$offset,1000000)) { 
		print $buff; 
		$offset+=length($buff); 
		$buff=""; 
	}
	warn $DBI::errstr if $DBI::err;
}


sub insertData {
	
	# insert a row of data
	my $xmlInfo = qq(\'
	<customerinfo xmlns="http://posample.org" Cid="1011"> 
	  <name>Bill Jones</name> 
	  <addr country="Canada"> 
	    <street>5 Redwood</street> 
	    <city>Toronto</city> 
	    <prov-state>Ontario</prov-state> 
	    <pcode-zip>M6W 1E9</pcode-zip> 
	  </addr> 
	  <phone type="work">416-555-9911</phone> 
	  <phone type="home">416-555-1212</phone> 
	</customerinfo>
	\') ;
	
	my $catID = 1011 ;
	
	# SQL statement to insert data.
	my $Sql = qq( 
	 INSERT INTO xmlTest (id, data)
	     VALUES($catID, $xmlInfo )
	);
	
	$sth = $dbh->prepare( $Sql )
	  or die "Can't prepare statement: $DBI::errstr";
	
	my $rc = $sth->execute
	  or die "Can't execute statement: $DBI::errstr";
	
	# check for problems 
	warn $DBI::errstr if $DBI::err;
}