Oracle XML: When an Oracle Database houses an XML guest

...Oracle hooks-up with XML

XML documents apart from being a datasource also serves as a structured document with a definable schema. XML, as a standard, contains textual data which can be stored in an oracle database. XML data is usually described as a Character Large Object(CLOB) since it hosts several bytes of string data in a single document or data structure.

The fact that XML is a specialised data and datasource makes it’s storage in an Oracle database demand a specific approach to  its data storage and retrieval.

An Oracle database stores XML data in either a table or a column with a specific datatype – XMLTYPE.  Such data when stored are accessed using PL/SQL functions (which are an extension of custom XMLTYPE functions and operations) and XPATH constructs.

XPATH, the XML Path Language, is a query language for selecting nodes from XML data.

Below are sampled steps used to store and retrieve XML data in an Oracle database.

1. We create a table to store all ORDER xml content within a column of a database table.

CREATE TABLE ORDER_XML_DOC_TBL

(

FILEID varchar2 (12),

FILENAME varchar2 (64),

XML_DOCUMENT XMLTYPE

);

2. Creation of a CLOB variable to temporary host xml data

DECLARE XML_TEXT CLOB := '

<?xml version="1.0"?>

<Order>

 <Date>2003/07/04</Date>

 <CustomerId>123</CustomerId>

 <CustomerName>Joyce Appiah Ofori</CustomerName>

 <Item>

         <ItemId>356</ItemId>

         <ItemName>Sephora Beauty Range</ItemName>

         <Quantity>2</Quantity>

 </Item>

<Item>

         <ItemId>030</ItemId>

         <ItemName>Mary Kay Cosmetics</ItemName>

         <Quantity unit="12">3</Quantity>

</Item>

<Item> 

         <ItemId>579</ItemId>

         <ItemName>L’eggs Stockings</ItemName>

         <Quantity>1</Quantity>

 </Item>

</Order>';

3. Storage of XML in database table within a transaction to guarantee a full export or none

BEGIN

INSERT INTO ORDER_XML_DOC_TBL VALUES ('ORDER_DOC_1','ORDER_101.xml', XMLTYPE (XML_TEXT));

END;

 4. Fetching data from the XML table using XMLTYPE functions of PL/SQL

Example 1. Using SQL function ‘extract’  and ‘extractValue’ to display all ItemId child elements of the Order parent as a varchar2 string type.

SELECT extract (XML_DOCUMENT, '/Order/Item/ItemId /text()').getStringVal()  "ITEM_IDs"  FROM  ORDER_XML_DOC_TBL

or simply

SELECT extract (XML_DOCUMENT, '/Order//ItemId /text()').getStringVal()  "ITEM_IDs"  FROM ORDER_XML_DOC_TBL

 or using extractValue function (which needs no data conversion) with an ORDER BY clause.

SELECT extractValue(XML_DOCUMENT, '/Order/Item/ItemId ')  "ITEM_IDs" FROM  ORDER_XML_DOC_TBL ORDER BY extractValue(XML_DOCUMENT, '/Order/Item/Quantity ')

Example 2. Using SQL function ‘extractValue’ to display a particular Item with Id= 579. Also introduced is the existsNode boolean function that returns 0 or 1 for either true or false.

SELECT extractValue(XML_DOCUMENT, '/Order/Item/ItemId ')  "ITEM_ID"  FROM ORDER_XML_DOC_TBL WHERE existsNode (XML_DOCUMENT, '/ Order //emp [ItemId ="579"]') =  1;

5. Reference

 Sample Order XML from Sybase Infocenter site.

 Oracle XML syntax from the Oracle Documentation site.