Monday 10 December 2012

Introduction to Oracle XML



XML in Oracle can be stored in the database type XMLTYPE.
To convert string text into an xml document a call to XMLTYPE needs to be made
/* Convert a string into xml */
DECLARE

l_xml      XMLTYPE ;

BEGIN

   l_xml := XMLTYPE ('<Message>Hello World</Message>') ;

END ;
/
XML data can be converted into a clob by the use of the method getclobval

/*Convert a string into xml and then into a clob */
DECLARE

l_xml      XMLTYPE ;
l_clob     CLOB ;

BEGIN

   l_xml := XMLTYPE ('<Message>Hello World</Message>') ;
   
   l_clob := l_xml.getclobval ;

END ;
/
Trying to convert a null xmltype to a clob will result in an error

DECLARE

l_xml      XMLTYPE ;
l_clob     CLOB ;

BEGIN

   l_clob := l_xml.getclobval ;

END ;
/

DECLARE
*
ERROR at line 1:
ORA-30625: method dispatch on NULL SELF argument is disallowed
ORA-06512: at line 10
XML data can be stored in tables.
/* create a table to store xml in */
CREATE TABLE my_xml OF XMLType
/
/* store xml in the table */

DECLARE

l_xml      XMLTYPE ;
l_clob     CLOB ;

BEGIN

   l_xml := XMLTYPE ('<Message>Hello World</Message>') ;
   
   INSERT INTO my_xml VALUES (l_xml) ;
   
   COMMIT ;

END ;
/

PL/SQL procedure successfully completed.

select * from my_xml ;

SYS_NC_ROWINFO$
------------------------------------------------------------------------------
<Message>Hello World</Message>

When selecting large xml documents from a table in SQLPLUS the details may be truncated. To overcome this increase the size of long using the command SET LONG 999999999

Extracting data from xml using xpath

Using the sample xml document

<userdetails>
   <username>Fred</username>
   <password>pass123</password>
</userdetails>
Insert the xml into a database table

DELETE FROM my_xml ;

INSERT INTO my_xml VALUES (XMLTYPE ('<userdetails>
                                       <username>Fred</username>
                                       <password>pass123</password>
                                     </userdetails>')) ;
                                     
COMMIT ;

/* To extract the username */

SELECT extractValue(value(x),
                    '/userdetails/username')
  FROM my_xml x ;

EXTRACTVALUE(VALUE(X),'/USERDETAILS/USERNAME')
----------------------------------------------------------------
Fred
/* To extract the password */

SELECT extractValue(value(x),
                    '/userdetails/password')
  FROM my_xml x ;

EXTRACTVALUE(VALUE(X),'/USERDETAILS/PASSWORD')
-----------------------------------------------------------
pass123
Note, the xpath case must match the case in the xml document. If we pass in password in uppercase then no data will be located

SELECT extractValue(value(x),
                    '/userdetails/PASSWORD')
  FROM my_xml x ;

EXTRACTVALUE(VALUE(X),'/USERDETAILS/PASSWORD')
-------------------------------------------------------

Extracting XML details in pl/sql

DECLARE

l_xml                XMLTYPE ;
l_username           XMLTYPE ;
l_username_varchar   VARCHAR2(20) ;

BEGIN

   l_xml := XMLTYPE ('<userdetails>
                         <username>Fred</username>
                         <password>pass123</password>
                      </userdetails>') ;
       
   -- extract value to clob               
   l_username := l_xml.extract ('/userdetails/password/text()') ;          

   dbms_output.put_line (l_username.getclobval) ;   

   -- extract value to string 
   l_username_varchar := l_xml.extract ('/userdetails/password/text()').getstringval ;

END ;
/

pass123

PL/SQL procedure successfully completed.

The /text() was required to extract the value, without it we would get the node as shown below

DECLARE

l_xml        XMLTYPE ;
l_username   XMLTYPE ;

BEGIN

   l_xml := XMLTYPE ('<userdetails>
                         <username>Fred</username>
                         <password>pass123</password>
                      </userdetails>') ;
                      
   l_username := l_xml.extract ('/userdetails/password') ;          

   dbms_output.put_line (l_username.getclobval) ;   

END ;
/

<password>pass123</password>


PL/SQL procedure successfully completed.
If an XML document has one or more namespaces then these need to be passed into the xpath

DELETE FROM my_xml ;

INSERT INTO my_xml VALUES (XMLTYPE('<userdetails xmlns="http://abc.com/namespaceabc/v1">
                                      <username>Fred</username>
                                      <password>pass123</password>
                                    </userdetails>')) ;

COMMIT ;
The previous xpath will no longer work as this xml has a namespace, (the namespace being http://abc.com/namespaceabc/v1

SELECT extractValue(value(x),
                    '/userdetails/password')
  FROM my_xml x ;
  
EXTRACTVALUE(VALUE(X),'/USERDETAILS/PASSWORD')
-----------------------------------------------------
   
The namespace needs to be passed into the xpath
SELECT extractValue(value(x),
                    '/a:userdetails/a:password' ,
                    'xmlns:a="http://abc.com/namespaceabc/v1"')
  FROM my_xml x ;
  
EXTRACTVALUE(VALUE(X),'/A:USERDETAILS/A:PASSWORD','XMLNS:A="HTTP://ABC.COM/NAMESPACEABC/V1"')
--------------------------------------------------------------------------------------------------
pass123
The use of the letter a for the alias is not relevant. In this example the word fred is used instead of the letter a
SELECT extractValue(value(x),
                    '/fred:userdetails/fred:password' ,
                    'xmlns:fred="http://abc.com/namespaceabc/v1"')
  FROM my_xml x ;

EXTRACTVALUE(VALUE(X),'/FRED:USERDETAILS/FRED:PASSWORD','XMLNS:FRED="HT
-----------------------------------------------------------------------
pass123  
Sometimes values are stored as attributes. In the example below the usertype of ADMIN is held in the attribute called usertype. References can be made to attributes by using the @ symbol.
DECLARE
 
l_xml        XMLTYPE ;
l_usertype   VARCHAR2(10) ;
 
BEGIN
 
   l_xml := XMLTYPE ('<userdetails>
                         <username usertype="ADMIN">Fred</username>
                         <password>pass123</password>
                      </userdetails>') ;
 
   l_usertype := l_xml.extract ('/userdetails/username/@usertype').getstringval ;          
 
   dbms_output.put_line (l_usertype) ;   
 
END ;
/
 
ADMIN
 
PL/SQL procedure successfully completed.



No comments:

Post a Comment