[FSF Associate Member] View Annang Sowah's profile on LinkedIn

Friday, 19 July 2013

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.