Sys.xmlType datatype and parsing XML in Oracle9i, 10g onwards
Oracle9i onwards there is new datatype called sys.xmlType. This provides a great way to handle XML documents with minimal or no parsing required.
Listed below is a sample of how to use the xmlType.
Step 1. Create Table of XML type
CREATE TABLE xml_table( xml_col SYS.XMLTYPE );Step 2. Insert the following XML into the xml_table defined above
<?xml version="1.0"?>
<email>
<from>xyz@gmail.com</from>
<to>xyz1@gmail.com</to>
<subject>some subject</subject>
<body>some body</body>
</email>declare
l_temp sys.xmlType;
begin
l_temp := sys.xmlType.createXML('
<?xml version="1.0"?>
<email>
<from>xyz@gmail.com</from>
<to>xyz1@gmail.com</to>
<subject>some subject</subject>
<body>some body</body>
</email>');
insert into xml_table values ( l_Temp);
Commit;
end;
Step 3. Write select query to extract data from this table. Sample extract query listed below.
select a.xml_col.extract("//from/text()").getStringVal() as from from xml_table a
