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.

Untitled

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

Untitled

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;

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”.