Nowadays, a lot of relational databases – e.g. Oracle and MS SQL Server – provide XML Schema support for the numerous purposes. Validation of the XML documents against registered XML Schema definitions is one of them. As a part of the XML Schema registration process, a database automatically creates the storage for a particular set of XML documents, based on the information provided by the schema. If the document fits to the schema, it is shredded into relational data conforming to the underlying relational table. In described XML activity, the schema registration functionality was introduced. To enable participants the registration of their own XML schemas, suitable PHP script was added to a view.php file. In this type of tasks, the Execute button is used to perform the requested action, as it is shown in Figure 2.22.
In fact, the registration process involves an appropriate built-in procedure. For example in Oracle database repository it was DBMS_XMLSCHEMA.RegisterSchema procedure. Consequently, in the view.php file, the learner schema stored in a string variable, is only
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.
Many other mechanisms, required for manipulating and querying the XMLType tables or views, based on registered XML schemas, were provided to the MOODLE e-learning platform. There are a lot of PHP scripts examples in (PHP Oracle Web Development) that are in fact ready-made solutions. Thus, the numerous functionalities were introduced into the XML module.
Besides, the possibility of formulating a simple XPath queries is provided in a described module. As it is presented in (Figure 2.25), the queried XML document is assumed to be stored in a MOODLE platform server.
In order to provide such functionality, a special PHP function that converts an XML file into an object should be utilized. By the use of:
$doc =simplexml_load_file('XML_doc.xml');
assignment, all document elements are available. For example, in order to print BA-tagged data, $doc->student->BA argument should be used in the printf function. To facilitate the algorithm implementation, the document tree hierarchy is stored in a specially created table. Thus, the XPath query realization is reduced to the following steps:
- Check the name of last-written tag.
- Search the auxiliary table to get the level of the tag.
- Convert the well-formed XML document in the given file to an object.
- Loop through the elements at a specified level. Print required data in a user’s activity window.
- 2267 reads