How to insert XML documents from the file system? Let’s discuss this common question for both, DB2 on z/OS and DB2 on Linux, UNIX, and Windows (LUW).

DB2 for z/OS offers two good options for moving XML documents from the file system into XML columns or LOB or varchar columns. The first option is to use the DB2 LOAD utility, which is described here. The second options is to use File Reference Variables in your application code to read LOB or XML files from the file system. This is a native solution and typically performs very well.

In a COBOl, PL/1, or C application, you can use a file reference variable of type BLOB_FILE, CLOB_FILE, or DBCLOB_FILE to insert a LOB or XML value from a file into a DB2 table. LOB file reference variables move LOB or XML values from an application to the database server without going through the application’s memory. Their usage is described here:


In DB2 LUW, a good way to move XML documents from a file system into a DB2 database is to use the DB2 LOAD or the DB2 IMPORT utility. Alternatively, applications can use custom code to read XML documents from their local file system and bind them to parameter markers of inserts statements that are then submitted to DB2.

In some cases you may want to insert XML files that reside in the file system of the DB2 database server – directly with insert statements, without using LOAD or IMPORT. If you have file access permissions on the database server (which might be restricted) then you can use simple user-defined functions (UDFs) in your insert statements to read one or multiple files from the file system. Here is a package “” with examples of such UDFs.

The XMLfromFile functions in this package are described in section 4.1.2 of the DB2 pureXML Cookbook and also in the readme file that is included in the package. Some of most useful functions include:

  • blobFromFile: Reads a file from the DB2 server’s file system and returns the file contents as a BLOB. If this BLOB contains a well-formed XML document, it can be inserted into an XML column.
  • clobFromFile: Reads a file from the DB2 server’s file system and returns the file contents as a CLOB. If this CLOB contains a well-formed XML document, it can be inserted into an XML column.
  • directoryInfo: Returns a table with information about files in a directory in the DB2 server’s file system.
  • blobsFromZipURL/clobsFromZipURL: Table functions that read a ZIP file from a URL or file and return a table that contains each file from the ZIP in a separate row, as a BLOB or CLOB, respectively. Similar functions exist for gzip files.

And here are some examples of how you might use these functions.

Read the XML file “book07.xml” and insert it into the table called “shelf” where the column “bookinfo” is of type XML or BLOB:

INSERT INTO shelf(id, bookinfo)

List all files that are located in the directory c:\xml\book:

SELECT filename, size, modtime
FROM TABLE(directoryInfo(‘c:\xml\book’)) ;

Insert all XML files from the directory c:\xml\book:

INSERT INTO shelf(name, bookinfo)
SELECT t.filename, blobFromFile(t.filename)
FROM TABLE(directoryInfo(‘c:\xml\book’)) t
WHERE isDirectory = 0 AND filename LIKE ‘%.xml’ ;

Insert all XML files from the ZIP file

INSERT INTO shelf(bookinfo)
WHERE filename LIKE ‘%.xml’OR filename LIKE ‘%.XML’ ;

Note that the ZIP file does not get unzipped to the file system. Instead, the documents are read from the ZIP file and inserted directly into DB2 without uncompressing them in the file system.


XML Tools for DB2 pureXML

December 5, 2010

There is a new article on developerWorks that you may find interesting. It describes the XML capabilities in various database and software development tools, the different job roles that are impacted by having XML in the database, and the specific tasks involved. The article can help you choose the right tool for specific tasks and job roles.

After a very brief introduction to DB2 pureXML, the first part of the article lists a set of database tools, each with a screenshot, and lists their key features. These tools include:

  • DB2 Control Center
  • Data Studio and Optim Development Studio (ODS)
  • Infosphere Data Architect (IDA), formerly known as Rational Data Architect (RDA)
  • Rational Application Developer (RAD)
  • Rational Software Architect
  • InfoSphere Warehouse Design Studio

The second part of the article discusses different job roles (DBA, application developer, architect, etc.) and how they relate to XML-specific tasks in a project life-cycle. Subsequently, the article describes these tasks in more detail and maps them to available features in the above-mentioned tools. Towards the end of the article you find summary tables that show which tools contain which XML features, and which tools support which XML tasks. These tables alone are worth the article!

Although the article focuses mainly on IBM tools, it also points to non-IBM tools such as Altova XMLSPY, <oXygen/>Stylus Studio, and others.

Oh, before I forget, here is the link to the article:

Happy reading!