Reading XML in Oracle -1

As a first example on how to read an XML file, I show how an XML file can be read with a PLSQL programme. The logic is quite simple. We know that XML can be declared as an XMLtype. Once that is known, we declare the full XML file as an object that is read on a record by record base. The values can then be extracted with the ExtractValue procedure. Once the values are extracted, they can be inserted into a table.

We use the XMLTYPE here. This type allows to store XML files in a field. With special methods, one may read, update or delete the content of the XML. One may notice in the procedure below the ExtractValue method that allows to extract a certain value from the XML file.

create or replace PROCEDURE lees_XML AS
x XMLType := XMLType(
'<?xml version="1.0" ?>
<person>
<row>
<name>Tom</name>
<Address>
<State>California</State>
<City>Los angeles</City>
</Address>
</row>
<row>
<name>Jim</name>
<Address>
<State>California</State>
<City>Los angeles</City>
</Address>
</row>
</person>');
BEGIN
FOR r IN (
SELECT ExtractValue(Value(p),'/row/name/text()') as name
,ExtractValue(Value(p),'/row/Address/State/text()') as state
,ExtractValue(Value(p),'/row/Address/City/text()') as city
FROM TABLE(XMLSequence(Extract(x,'/person/row'))) p
) LOOP
insert into STORE_XML values(r.name, r.state, r.city);
END LOOP;
END;

Another display is given when we look at a table that has two XML files:

One may want to extract from the second XML file all values that are stored in. That can be done with

SELECT xt.*
FROM   xml_tab x,
       XMLTABLE('/person/row'
         PASSING x.xml_data
         COLUMNS 
           "State"    VARCHAR2(12)  PATH 'Address/State',
           "City"    VARCHAR2(15)  PATH 'Address/City',
           "Name"    VARCHAR2(10)  PATH 'name'
           ) xt where ID=2;

The xml looks like. The /person/row is related to the <person> and <row> tag. Within that branch, one has the <name> tag with the <Adress>/<State> tag. Such tags are used in the query. Beware that the tags are case sensitive. 

A final word on the clause ‘/person/row’. This is an example of a so-called XQuery. This ‘/person/row’ returns a row. It is possible to make a selection like ‘/person/row[name=”Tom”]’. This only returns the rows where name equals “Tom”.