XML usage in relational databases

one of the required procedure arguments. Therefore, after pres sing the Execute button in the XML module window, the following code is performed (listing 3):

$conn = oci_connect($dbuser, $dbpassword, $dbname);

If ($conn) {

$sql_statement = OCIParse(

$conn,

"begin DBMS_XMLSCHEMA.RegisterSchema (

$SchemaName, $userschema, TRUE,TRUE,FALSE,TRUE, $SchemaOwner);

end;");

$command=oci_execute($sql_statement);

If ($command) {

echo "Schem a is registered successfully";

}

}

Listing 3. PHP code of XML Schema registration.

The schema registration is only the first step to enable storing the XML data in a relational database. Next, it can be, for example, schema-based table creation, as shown in the following example:

CREATE TABLE XmlTable OF XMLType

XMLSCHEMA "http:/ /www.XMLModule.com/sample.xsd"

ELEMENT "List";

or posting a schema-based document to be inserted into a schema-based XMLType view. In this case, the database system checks whether the XML document being inserted into the table or view, conforms to the XML schema on which the view is defined. To illustrate this process, two tables in Oracle database - Student and University, with fields corresponding to tags of sample XML (Figure 2.23), were created. Additionally, a new univNo column was added to each table, in order to enable table joining. The appropriate view definition is shown below (Listing 4).

CREATE OR REPLACE VIEW univ_v OF XMLType WITH OBJECT ID

(EXTRACT(OBJECT_VALUE, '/UNIVERSITY/@univNo').getNumberVal())

AS SELECT XMLELEMENT

("UNIVERSITY",

XMLFOREST(univNo, univName),

(SELECT XMLELEMENT("STUDENT",

XMLAGG(

XMLELEMENT("STUDENT",

XMLFOREST(studNO as "Student", BA , Master)

)

)

)

FROM Student s, Universi ty u WHERE s.univNo = u.univNo

)

)

FROM University u;

Listing 4. XMLType view definition.

There are two ways to keep data in a XMLType storage structu re, in Oracle database system:

Store XML in CLOB XMLType.
Store XML as structured data, by the use of object-relational storage. In this situation, the shredded XML document is inserted into the underlying relational table, as a new row.

In the view.php file only the second possibility was implemented (Building Database-Driven PHP Applications), as it is shown in listing 5.

$sql = $_POST["query"];

$tmp = str_replace(";", " ", $sql);

$sql = $tmp;

$sql_statement = OCIParse($connection,$sql);

oci_execute($sql_statement);

If ($r) {

oci_fetch($sql_statement);

$strXMLData = oci_result($sql_statement, 'RESULT');

$doc = ne w DOMDocument("1.0", "UTF-8");

$doc->loadXML($strXMLData); }

Listing 5. XML data result.

It is worth emphasizing that XML data retrieved from the database with applied DOM fidelity consists of the same information as it was inserted into the database, with the exception of insignificant whitespaces. In order to print out the result in a proper way (i.e. XML-formatted), the htmlspecialchars($strXMLData) function was called. Otherwise, only “pure” data – i.e. entries without tags, will be printed. The XML-formatted result of a sample query is shown in Figure 2.23, while “ordinary” one – in Figure 2.24.