IBM Support

Insert & select XML data from a BLOB column in DB2.

Troubleshooting


Problem

An example of Inserting & selecting XML data from a BLOB column in DB2.

Symptom

DB2 provides a native XML data type but some legacy databases and applications might use BLOBs to store the XML.

The native XML data type is the recommended one but this technote describes how to extract the XML using the available DB2 functions if the data resides in a BLOB column.

The following examples are executed against the SAMPLE database:


1) Connect to the SAMPLE database
db2 connect to sample

2) Create a table with a BLOB column (col2):
db2 "create table tab1 (col1 int, col2 blob)"

3) Insert some XML data:


3.1) Place this insert statement in a file, in my case I have called the file: xml_ins.sql:


insert into tab1 values(111, cast('<?xml version="1.0"?>
<Warehouse>
<WarehouseId>1</WarehouseId>
<WarehouseName>Kingston,London</WarehouseName>
<Building>Owned</Building>
<Area>SW15</Area>
<Parking>Street</Parking>
</Warehouse>'
as blob))
;

3.2) Using the DB2 clp, execute it as follows:
db2 -tvf xml_ins.sql

insert into tab1 values(111, cast('<?xml version="1.0"?> <Warehouse> <WarehouseId>1</WarehouseId> <WarehouseName>Kingston,London</WarehouseName> <Building>Owned</Building> <Area>SW15</Area> <Parking>Street</Parking> </Warehouse>' as blob))

DB20000I The SQL command completed successfully.

At this point the XML is stored in an encoded binary format so we need to use the built-in xmlparse scalar function to select it:

4) Selecting the XML from the BLOB:

db2 "select col1, xmlparse(document cast(COL2 as varchar(1000)) strip whitespace) as COL2 from tab1"

Which results in this output:

COL1 COL2
----------- --------------
111 <Warehouse><WarehouseId>1</WarehouseId><WarehouseName>Kingston,London</WarehouseName><Building>Owned</Building><Area>SW15</Area><Parking>Street</Parking></Warehouse>

Depending on your requirements, you could specify: 'preserve whitespace' in place of: 'strip whitespace'
[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Tables","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;10.1;10.5;11.1","Edition":"Advanced Enterprise Server;Advanced Workgroup Server;Enterprise Server;Express;Express-C;Personal;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

To view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use the link to actual document below to access the full document. You will be asked to log on if you are not already logged in. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Document Information

More support for:
Db2 for Linux, UNIX and Windows

Software version:
9.8, 9.7, 10.1, 10.5, 11.1

Operating system(s):
AIX, HP-UX, Linux, Solaris, Windows

Document number:
283093

Modified date:
30 April 2025

UID

swg21985937

Manage My Notification Subscriptions