Taking Advantage of Standard Internet Protocols

As mentioned, Oracle XML DB provides native support for standard internet protocols, such as HTTP(S), WebDAV, and FTP. Continuing with the preceding sample, you might, for example, upload another employee XML document into the XML repository with one of the above protocols, say, FTP.

NOTE: Starting with Oracle Database 10g Release 2, FTP is disabled by default, for security reasons. This is achieved by setting the FTP port to 0. To enable FTP, you must manually set the FTP port number to an appropriate value, such as 2100, which is the default value in Oracle Database releases before 10g Release 2. Changing the FTP port to an appropriate value can be easily done with the help of Oracle Enterprise Manager, a graphical tool supplied with Oracle Database. For more information on Oracle Enterprise Manager, refer to Oracle documentation: chapter Getting Started with Oracle Enterprise Manager in the Oracle Database 2 Day DBA manual.

The uploadXML.php script shown below provides a simple example of how you might upload an XML document into the XML repository through FTP protocol.

<?php
//File: uploadXML.php
$host='localhost';
$port=2100;
$timeout=30;
$db_user='xmlusr';
$db_pswd='xmlusr';
$root_dir='/public/xmlusr/emps';
$empid=304;
$file='emp'.$empid.'.xml';
$cnt=
'<?xml version="1.0"?>
<employee id="'.$empid.'" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:nonamespaceschemalocation="employee.xsd"></employee>
<ename></ename>Polonski
<salary></salary>8200
';
$temp = tmpfile();
fwrite($temp, $cnt);
fseek($temp, 0);
$ftpcon = ftp_connect($host, $port, $timeout);
$login = ftp_login($ftpcon, $db_user, $db_pswd);
ftp_chdir($ftpcon, $root_dir);
ftp_fput($ftpcon, $file, $temp, FTP_ASCII);
ftp_close($ftpcon);
?> 
   
%3C%3Fphp%0D%0A%2F%2FFile%3A%20uploadXML.php%0D%0A%24host%3D%27localhost%27%3B%0D%0A%24port%3D2100%3B%0D%0A%24timeout%3D30%3B%0D%0A%24db_user%3D%27xmlusr%27%3B%0D%0A%24db_pswd%3D%27xmlusr%27%3B%0D%0A%24root_dir%3D%27%2Fpublic%2Fxmlusr%2Femps%27%3B%0D%0A%24empid%3D304%3B%0D%0A%24file%3D%27emp%27.%24empid.%27.xml%27%3B%0D%0A%24cnt%3D%0D%0A%27%3C%3Fxml%20version%3D%221.0%22%3F%3E%0D%0A%3Cemployee%20id%3D%22%27.%24empid.%27%22%20%0D%0Axmlns%3Axsi%3D%22http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema-instance%22%20%0D%0Axsi%3Anonamespaceschemalocation%3D%22employee.xsd%22%3E%3C%2Femployee%3E%0D%0A%3Cename%3E%3C%2Fename%3EPolonski%0D%0A%3Csalary%3E%3C%2Fsalary%3E8200%0D%0A%27%3B%0D%0A%24temp%20%3D%20tmpfile%28%29%3B%0D%0Afwrite%28%24temp%2C%20%24cnt%29%3B%0D%0Afseek%28%24temp%2C%200%29%3B%0D%0A%24ftpcon%20%3D%20ftp_connect%28%24host%2C%20%24port%2C%20%24timeout%29%3B%0D%0A%24login%20%3D%20ftp_login%28%24ftpcon%2C%20%24db_user%2C%20%24db_pswd%29%3B%0D%0Aftp_chdir%28%24ftpcon%2C%20%24root_dir%29%3B%0D%0Aftp_fput%28%24ftpcon%2C%20%24file%2C%20%24temp%2C%20FTP_ASCII%29%3B%0D%0Aftp_close%28%24ftpcon%29%3B%0D%0A%3F%3E

In the above script, you start by setting up the parameters required to connect to the FTP server running on the database server.

By including the value of the employee's id attribute in the name of the XML file to be uploaded into the XML repository, you ensure the uniqueness of file names within the repository folder that contains employee XML documents in single files.

Then, you create a temp file and write XML content in it. Then, you connect to the Oracle FTP server and upload the fi le into the XML repository. After the uploadXML.php script is executed, to make sure that the employee XML
document has been successfully uploaded into the XML repository, you might issue the following query from SQL*Plus:

CONN xmlusr/xmlusr
SELECT extract(OBJECT_VALUE,'/').getStringVal()
RESULT FROM employees
WHERE existsNode(OBJECT_VALUE, '/EMPLOYEE/@id="304"') = 1;
 
   
CONN%20xmlusr%2Fxmlusr%0D%0ASELECT%20extract%28OBJECT_VALUE%2C%27%2F%27%29.getStringVal%28%29%0D%0ARESULT%20FROM%20employees%0D%0AWHERE%20existsNode%28OBJECT_VALUE%2C%20%27%2FEMPLOYEE%2F%40id%3D%22304%22%27%29%20%3D%201%3B%0D%0A

If everything is OK, this query should output the employee XML document uploaded by the uploadXML.php script:

<?xml version="1.0"?>
<employee id="304" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:nonamespaceschemalocation="employee.xsd"></employee>
<ename></ename>Polonski
<salary></salary>8200
 
 
   
%3C%3Fxml%20version%3D%221.0%22%3F%3E%0D%0A%3Cemployee%20id%3D%22304%22%20%0D%0Axmlns%3Axsi%3D%22http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema-instance%22%20%0D%0Axsi%3Anonamespaceschemalocation%3D%22employee.xsd%22%3E%3C%2Femployee%3E%0D%0A%3Cename%3E%3C%2Fename%3EPolonski%0D%0A%3Csalary%3E%3C%2Fsalary%3E8200%0D%0A%0D%0A

So, after the uploadXML.php script is executed, the XML document inserted by the script becomes permanent immediately.