Quick introduction to Oracle XMLDB

XMLDB is an Oracle module provided with all versions of Oracle Database. It allows to store and manage data in XML format. It provides an abstract SQL type, XMLTYPE, which lets you manipulate the XML data.

Thanks to this element you can manipulate complex object without having one column for each attribute of your object. You can just use the method extravalue(Object_Value, xmlpath, xmlnamespace) to extract value of a single node. Object_Value is your XMLTYPE object stored in the table, xmlpath is an existing path in your object  and you can also declare xml namespaces in the last argument .

Ex1:

CREATE TABLE LIBRARY OF XMLTYPE

XMLTYPE STORE AS BINARY XML

INSERT INTO LIBRARY VALUES (XMLType(‘<?xml version = ‘1.0’ encoding = ‘UTF-8’ standalone = ‘yes’?>
<book:BOOK isbn=”ABCD7327923″ xmlns:book=”http://arondor.int/internal/schema/book” xmlns:author=”http://arondor.int/internal/schema/author”>
<book:NAME>Java Programing</ book:NAME>
<book:SUBJECT>Java J2EE</ book:SUBJECT>
<author:AUTHORS>
< author:AUTHOR>Tuna TORE</ author:AUTHOR>
< author:AUTHOR>Linus Torvalds</ author:AUTHOR>
< author:AUTHOR>James Gosling</ author:AUTHOR>
</ author:AUTHORS>
</ book:BOOK>’));

SELECT extractvalue (t.OBJECT_VALUE,’/book:BOOK/NAME’,’xmlns:book=”http://arondor.int/internal/schema/book”) from LIBRARY t;

This select request will return the value of name node.

You can also use extractvalue method to create virtual columns, these are use full for example when you need the extracted data several time.

Ex2:

CREATE TABLE LIBRARY OF XMLTYPE

XMLTYPE STORE AS BINARY XML
VIRTUAL COLUMNS (isbn AS (extractvalue (OBJECT_VALUE,’/book:BOOK/@isbn’,’xmlns:book=”http://arondor.int/internal/schema/book”))

SELECT isbn from LIBRARY

This select request will return value of isbn XML attribute of stored data.

Extractvalue method only works with single value nodes, but Oracle provide extract method which allows to extract multi valued nodes.

 

Ex3:

SELECT extract (OBJECT_VALUE,’/book:BOOK/author:AUTHORS’, xmln:book=”http://arondor.int/internal/schema/book” xmlns:author=”http://arondor.int/internal/schema/author”) from LIBRARY

This request will return an XMLTYPE containing   (<author:AUTHORS> < author:AUTHOR>

Tuna TORE</author:AUTHOR> < author:AUTHOR>Linus Torvalds</ author:AUTHOR>
< author:AUTHOR>James Gosling</ author:AUTHOR></ author:AUTHORS>) according to first example data.

You can also verify if an xml node exists with existsnode method.

Ex4:

Select * from LIBRARY where existsnode(OBJECT_VALUE,’/book:BOOK/book:EDITOR, ’xmlns:book=”http://arondor.int/internal/schema/book”)!=0;

This request will return 0 results because there is no entry containing this node according to first example data.

References :

http://www.oracle.com/technetwork/database/database-technologies/xmldb/overview/index.html

http://www.docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb01int.htm

Share This:

Leave a Reply

Your email address will not be published. Required fields are marked *