text: Introduction
  What's new with desktop documents? A great deal, if you follow the Microsoft Windows® community. Microsoft has introduced a new format for MS Office 2007 products that leverages XML internally. You could exchange data between MS Office applications before; but MS Office 2007 and Windows Vista® allow enterprises to tap into islands of data that were historically very difficult to consume and re-purpose (that is, to utilize data intended for one purpose for another purpose). While Microsoft is pushing this concept, OpenOffice applications have implemented ODF for some time and benefit from the XML advantages already identified in the marketplace.
  Let's look at a typical desktop application problem. Consider a company whose marketing department is planning a conference. They ask prospective speakers to submit proposals in a MS Word template document, and the document contents are then used for the following tasks:
  - Reviewing submissions
  - Posting related information on a conference Web site
  - Publishing content through a new feed (ATOM/RSS) for the conference or company ODF and MS Office 2007 documents is easy with IBM DB2 pureXML™ features. Not much code is required, so this solution is easy to implement. Later, you may want to index these Office 2007 documents to take further advantage of the pureXML hybrid storage features.
  Overview of desktop applications and XML
  This article focuses on the OpenOffice default format ODF and MS Office 2007 formats. You should be aware that the concepts presented here are not limited to these formats and vendors. In fact, you'll find ODF in a variety of vendors including Google Applications, KOffice, and StarOffice. It is worth noting that Microsoft has a translator "plug-in" for consuming and importing ODF formats and that there is a compatibility pack that can open and save MS Office 2007 file formats with older versions of Office. Furthermore, Lotus Notes and Corel have plans for the ODF format as well.
  Wikipedia discusses the ODF format and its use in desktop applications as well as providing information about the supporters of ODF.
  Resources required
  While you can download Apache 2.0, PHP Version 5.21 or greater, a the OpenOffice and the new MS Office 2007 format
  These formats are effectively a ZIP file containing resources and a folder for XML documents. The actual text content of a Word document is stored as an XML file. In our sample document, it's found in "word/document.xml" within the archive. This file format allows developers to capture the true data within these documents.
  While OpenOffice has been using this format for sometime, MS Office 2007 just recently introduced their new document format. Microsoft touts that this format is accessible and easily shared. MS Office documents have been proprietary for many years, and it is refreshing that the Office team within Microsoft is opening the doors. See the ZIP file contents provided below, in the Downloads section, for ODF:
  Figure 1. Contents of opendoc.odt within WinZip
  See the Microsoft format below, note that there are some differences but the concept and format is similar:
  Figure 1.1 Contents of submit.docx within WinZip
  Setting up the database
  Of course, you'll need to create a table. You'll store the XML within the new Word format within the XML column rather than in a Character Large Object (CLOB). To do this, we'll need to create an XML-enabled or UTF-8 database.
  Listing 1. Creating an XML-enabled database
  CREATE DATABASE ODF.
  Listing 2. Creating an XML-enabled table to contain our ODF and MS Office 2007 documents:
  - Open the Zend Core Administration Console.
  - Switch to the Configuration tab and Extensions sub-tab.
  - Scroll down to the ZIP extension.
  - Click on the switch icon to turn it on.
  - Click on Save Settings.
  - Restart Apache2.
  - If it's not already running as a red feather in the system tray, start C:\Program Files\Zend\Apache2\bin\ApacheMonitor.exe.
  - Click on it, and restart the Apache2 HTTP Server from the menu.
  If this does not work for you, try editing the file and make sure the following lines are found in php.ini within the "C:\Program Files\Zend\Core for IBM\etc" folder. Add the line in bold below to your php.ini:
  Listing 3. Modifications for php.ini
  extension=php_zip.dll extension=php_pdo.dll extension=php_pdo_ibm.dll extension_dir="c:\program files\Zend\Core for IBM\lib\phpext"
  Consuming ODF and MS Office 2007 documents
  Now, you're ready to consume the opendoc.odt which is an ODF file included in the download. While Figure 2 illustrates a MS Word images of the document, this ODF file would appear similar to Listing 4. The document provided utilizes styles like "Heading 1" and "Heading 2" for formatting. Later, we'll query against the Heading 2 style as it is used in OpenOffice format. Note that in production code you will want to include proper error handling among other things.
  I have also included a document submit.docx file, which uses the MS Office 2007 format in the download. Figure 2 represents what this document looks like when viewed in Word. Like ODF, the document utilizes Word styles like "Heading 1" and "Heading 2" for formatting. Later, we'll query against the Heading 2 style.
  Figure 2. Sample document as shown in OpenOffice or MS Word
  Let's consume this document with the following PHP code, which effectively loops through the entries in the manifest of the ZIP and extracts the XML document with relevant data. Listing 4 provides the code. Save this code under the filename "odfconsume.php" for reference purposes.
  Listing 4. PHP code to consume OpenOffice (ODF) format (odfconsume.php)
  <?php $user = "db2admin"; $password = "secret"; $zip = zip_open("c:\opendoc.od) == 'content.xml' ) { $buf = zip_entry_read($zip_entry, zip_entry_filesize($zip_entry)); $insstr ="INSERT INTO DB2ADMIN.DOCUMENT (ID,OWNER,DOC) VALUES (2,'Linux Office',:buf)"; $stmt = $db->prepare( $insstr ); $stmt->bindParam( ':buf' , $buf , PDO::PARAM_LOB , strlen($buf) ); $stmt->execute(); echo "\nResult: ".$db->errorCode()."\n"; zip_entry_close($zip_entry); } echo "\n"; } zip_close($zip); } ?>
  Run this code with the following command line:
  php odfconsume.php
  This inserts an XML document in the database and prints out an error code. Hopefully, the code will be zero.
  Listing 4.1 is the code for consuming MS Word. Save this code under the filename "msconsume.php" for reference purposes.
  Listing 4.1 PHP code to consume MS Office 2007 format (msconsume.php)
  <?php $user = "db2admin"; $password = "secret"; $zip = zip_open("c:\submit.doc) == ); } ?>
  Like above, run this code with the following command line:
  php msconsume.php
  This inserts an XML document in the database and prints out an error code. Again, hopefully the code will be zero.
  Re-purposing ODF and Word 2007 content
  Now that you have the contents of the OpenOffice and Word 2007 documents in the database, you need to re-purpose this document for your Web site. This is what the Marketing department would have to do in our example. The code for ODF is in Listing 5, which you can save as "odfrepurpose.php" for reference purposes. From the output (an HTML snippet), you can easily see how to reformat this into a news feed or incorporate it into an Ajax application!
  Listing 5. Re-purposing the ODF format (opendoc.od let \$p0 := \$body0/office0:text/text0:p let \$h0 := \$body0/office0:text/text0:h where fn:exists(\$p0) or \$h0:h/@text:style-name ="Heading_20_2" return if ( fn:exists( \$style ) ) then if (\$h0:h/@text:style-name ="Heading_20_2" ) then <h1>{\$txt}</h1> else () else <p>{\$txt}</p>') as VARCHAR(2000))) TXT; $result=$db->query( $xqry ); $arr = $result->fetch(); echo $arr[1]; ?>
  The code for Word 2007 is found in Listing 5.1, which you can save as "msrepurpose.php" for reference purposes. From the output (an HTML snippet), you can easily see how to reformat this into a news feed or incorporate it into an Ajax application!
  Listing 5.1 Re-purposing the Word 2007 format (submit.doc/office0:text let \$p := (for \$pp in \$t0/text0:p return <p>{\$pp/text()}</p>) let \$h := (for \$hh in \$t0/text0:h[@text0:style-name= "Heading_20_2"] return <hl>{\$hh/text()}</hl>) return (\$h,\$p)') as varchar(3000))) TXT; echo "\n"; $result=$db->query( $xqry ); $arr = $result->fetch(); echo $arr[1]; ?>
  In order to run the code, enter:
  php.exe ms Word 2007 with DB2.
  The following listing illustrates the result of the odfrepurpose.php or msrepurpose.php:
  Listing 6. The output OpenOffice and MS Office 2007 documents is easy with IBM DB2 pureXML features. There really is not much code involved, which is conducive to great performance. Consider indexing these XML documents later to take further advantage of the pureXML hybrid storage features.
  To learn more on XQuery, work with the XQuery editor in DB2 Developer Workbench or browse DB2 9: pureXML Overview and Fast Start (IBM Redbooks). See the IBM_PDO site on pecl.php.net for more information on PDO.
  Download
  Resources
  Learn
  - Browse the PDO function reference.
  - XQuery Tutorial: Read a neutral introduction to XQuery.
  - "DB2 XML evaluation guide" (developerWorks, June 2006): Learn everything needed to use XML with DB2 9.
  - "Query DB2 XML Data with XQuery" (developerWorks, June 2006): Learn to use XQuery with DB2 9.
  - "DB2 Basics: An introduction to the SQL/XML publishing functions" (developerWorks, November 2005): Learn about special XML functions in DB2.
  - "Introduction to XML and XQuery" (developerWorks, August 2006): Learn to query XML data in DB2 9.
  - Visit the developerWorks XML zone to expand your XML skills.
  - developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  - Stay current with developerWorks technical events and webcasts.
  Get products and technologies
  - DB2 Express-C can be freely distributed with your applications.
  - Zend Core for IBM bundles Apache, PHP, and all the necessary modules for DB2 with a convenient administration.