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'
To view more of this document
Was this topic helpful?
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