Query with XPath:
Select c.extract('/cliente').getStringVal() from clientes c;

Query with several tables:
select extract(c.object_value, '/cliente').
getStringval() "clientes", extract(cu.
object_value, '/cuenta').getStringval()
"cuentas" from clientes c, cuentas cu
where extractvalue(cu.object_value, '/
cuenta@cliente-id') = extractvalue(c.
object_value, '/cliente@id');

Eliminate:
delete from clientes where
extractvalue(object_value,'/
cliente@id')='0011010';

The XMLType also allows structured data to be stored as objects. This is similar to XSU object stores, and for this reason we do not consider it further here.

XSU
The XML SQL Utility is a set of APIs that support operations between XML documents and relational databases. It is possible to build an XML document from an SQL query, and to insert, update, and delete a relational data table from an XML document. To store XML documents in relational tables it is necessary to map the document nodes to a relational table format. So, XML-enablement allows getting XML documents from SQL queries (no new knowledge required) that can be formatted with XSL to any client application format.

Sometimes however, it is not so easy to build an RDBMS structure that matches an XML document. This is because XML documents are hierarchical and may have a dynamic structure that can change easily. In this case, we would need to rebuild the XML document with its structure each time we query the database.

Two storage and retrieval methods are possible for XSU: object-relational table storage (information stored in defined types), and relational table storage (data is stored in a relational table).

We implement both solutions to investigate their implications in code development, and the differences between structure creation and response times.

XSU does not support attribute nodes and element nodes with more than one occurrence in the same level. In the first case we should have to transform attributes in element nodes, for example <client id="001001"> would be <client> <id>001001</id>. In the second case, we would need to fragment the document, thus inserting the part into various tables. There would be an XSU instance to insert each document into its corresponding table. We use XSLT stylesheets to transform each document into the appropriate format before writing it to the database.

With XSU object-relational table storage, information is stored in defined types according to the structure shown in Figure 2.

• The table contains an object.
• The objects contain data.
• XML hierarchical structure is maintained.

• The table contains data. The objects are empty.
• There is no any hierarchical structure. Structure is built when selected (using objects).

We can only represent one nesting level. Response time is elevated more than with the previous case. In the selected approach we have to rebuild the XML document's initial hierarchical structure, thereby creating the objects dynamically with data from the table.

If we have the structure stored in objects it is easier to retrieve XML documents in their initial hierarchy format. XSU (XML-SQL Utility) give us the following functionalities.

• It converts a relational query to XML. Column names would be element names in the document; by default ROW is the element root and there are as many ROW elements as there are rows needed to retrieve the query. These ROW and ROWSET names can be customized through XSU methods respectively (setRowTag(String tagname) and setRowsetTag(String tagname)).
• When using XML documents it is possible to insert, update, and delete data from relational tables according to certain rules. For example, "SELECT * FROM CLIENTE" in XML would be:

  
  <?xml version = '1.0'?>
  <ROWSET>
     <ROW num="1">
        <ID>001</ID>
        <NOMBRE>Pablo</NOMBRE>
        <APELLIDO>Nocedal Renovales</APELLIDO>
        <CIUDAD>Sopuerta</CIUDAD>
        ........
        <EMAIL>pnocedal@eteo.mondragon.edu</EMAIL>
     </ROW>
    <!-- filas adicionales ... -->
  </ROWSET>
  

OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from cliente");