Tuesday, 19 February 2013

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.

No comments: