Oracle: Querying XML from a CLOB

Using Oracle 11g, a quick example of one way to query XML data from a CLOB.

1. Create the table.

CREATE TABLE xml_test ( id NUMBER(9,0), xml CLOB );

2. Insert some xml data

INSERT INTO xml_test VALUES ( 1, '<?xml version="1.0" encoding="iso-8859-1"?> <mydata> <id>100</id> <name>John</name> <address> <line1>10 High Road</line1> <line2>London</line2> </address> <phone>123456789</phone> </mydata>');

3. Query it

SELECT XMLTYPE(t.xml).EXTRACT('//name/text()').getStringVal(), XMLTYPE(t.xml).EXTRACT('//address/line1/text()').getStringVal() FROM xml_test t;

4. Alternative query

WITH xmlData AS ( SELECT XMLTYPE(t.xml) xml FROM xml_test t WHERE t.id = 1 ) SELECT x.xml.EXTRACT('//name/text()').getStringVal(), x.xml.EXTRACT('//address/line1/text()').getStringVal() FROM xmlData x ;

Next step: Load the XML data into the table from a file.