24 June 2008
Sys.xmlType datatype and parsing XML
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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment