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.
