Inserting and Updating Oracle XMLType columns in PHP

Today a reader mailed me about manipulating XMLType columns when the data is longer than the 4K limit that character-type handling imposes. My free book (see sidebar) has examples of how to do this using CLOB handling in PHP. I noticed that my xmlinsert.php example in the book does a SELECT and UPDATE, but never actually does an INSERT. The INSERT code is conceptually no different from UPDATE but, for completeness, here is an expanded example explicitly showing it:


<?php

/*
Table definition is:
create table xwarehouses (warehouse_id number, warehouse_spec xmltype);
*/

$c = oci_connect('hr', 'hrpwd', 'localhost/XE');

$id = 1;

// Delete any current entry
$s = oci_parse($c, "delete from xwarehouses where warehouse_id = :id");
oci_bind_by_name($s, ':id', $id);
oci_execute($s);

// XML data to be inserted
$xml =<<<EOF
<?xml version="1.0"?>
<Warehouse>
<WarehouseId>1</WarehouseId>
<WarehouseName>Southlake, Texas</WarehouseName>
<Building>Owned</Building>
<Area>25000</Area>
<Docks>2</Docks>
<DockType>Rear load</DockType>
<WaterAccess>true</WaterAccess>
<RailAccess>N</RailAccess>
<Parking>Street</Parking>
<VClearance>10</VClearance>
</Warehouse>
EOF;

// Insert new XML data using a temporary CLOB
$s = oci_parse($c, 
    "insert into xwarehouses (warehouse_id, warehouse_spec) 
     values (:id, XMLType(:clob))");
oci_bind_by_name($s, ':id', $id);
$lob = oci_new_descriptor($c, OCI_D_LOB);
oci_bind_by_name($s, ':clob', $lob, -1, OCI_B_CLOB);
$lob->writeTemporary($xml);
oci_execute($s);
$lob->close();

// Query the row back
$s = oci_parse($c, 'select xmltype.getclobval(warehouse_spec)
                    from xwarehouses where warehouse_id = :id');
$r = oci_bind_by_name($s, ':id', $id);
oci_execute($s);
$row = oci_fetch_array($s, OCI_NUM);

// Manipulate the data using SimpleXML
$sx = simplexml_load_string((binary)$row[0]->load());
$row[0]->free();
$sx->Docks -= 1;  // change the data

// Update changes using a temporary CLOB
$s = oci_parse($c, 'update xwarehouses
                    set warehouse_spec = XMLType(:clob)
                    where warehouse_id = :id');
oci_bind_by_name($s, ':id', $id);
$lob = oci_new_descriptor($c, OCI_D_LOB);
oci_bind_by_name($s, ':clob', $lob, -1, OCI_B_CLOB);
$lob->writeTemporary($sx->asXml());
oci_execute($s);
$lob->close();

// Query the changed row back and print it
$s = oci_parse($c, 'select xmltype.getclobval(warehouse_spec)
                    from xwarehouses where warehouse_id = :id');
$r = oci_bind_by_name($s, ':id', $id);
oci_execute($s);
$row = oci_fetch_array($s, OCI_NUM);
print htmlentities($row[0]->load());
$row[0]->free();

?>

Comments:

Great article! Thanks a lot!

Posted by stoimen on July 12, 2009 at 11:27 PM PDT #

Post a Comment:
Comments are closed for this entry.
About

Tourists looking out over an Opal mine
I'm a Product Manager in Server Technologies, working on scripting languages and developer-access.
Email: christopher.jones@oracle.com
Twitter: http://twitter.com/ghrd
Book: Free PHP Oracle book
Download: PHP Linux RPMs with the OCI8 extension
Links: OTN PHP Developer Center

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today