XML Data Interchange
Oracle Objects for OLE support for XML enables you to easily
extract data in XML format from an Oracle9i database.
Data in XML markup language can easily be integrated
with other software components that support XML. Web servers can provide XML
documents along with a stylesheet, thus separating the data content from its
presentation, and preserving the data in its native form for easy searching.
Using XSLT, Extensible Stylesheet Language
Transformations, businesses can reformat XML documents received from other
businesses into their desired style.
For more information about XML, go to
http://www.w3.org/XML/.
Example
OO4O renders XML from the contents of any
OraDynaset
based on a starting row number and continuing for up
to a specified amount of rows. For example:
Dim XMLString As String
Dim startrow as Integer
Dim maxrows as Integer
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
Set OraDynaset = OraDatabase.CreateDynaset("select EMPNO, ENAME, COMM, JOB
from EMP", 0&)
startrow = 4
maxrows = 2
'Output at most 2 rows beginning at row 4
XMLString = OraDynaset.GetXML(startrow, maxrows)
Output:
<?xml version = "1.0"?>
<ROWSET>
<ROW id="4">
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
</ROW>
<ROW id="5">
<EMPNO>7654</EMPNO>
<ENAME>MARTIN</ENAME>
<COMM>1400</COMM>
<JOB>SALESMAN</JOB>
</ROW>
</ROWSET>
The format of the XML may be customized though methods of
OraDynaset
and
OraField
:
Dim XMLString As String
Dim startrow as Integer
Dim maxrows as Integer
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
Set OraDynaset = OraDatabase.CreateDynaset("select EMPNO, ENAME, COMM, JOB
from EMP", 0&)
'Change the root tag of the XML document
OraDynaset.XMLRowsetTag = "ALL_EMPLOYEES"
'Change the row tag of the XML document
OraDynaset.XMLRowTag = "EMPLOYEE"
'Remove the rowid attribute
OraDynaset.XMLRowID = ""
'Turn on the null indicator
OraDynaset.XMLNullIndicator = True
'Change the EMPNO tag name
Set EmpnoField = OraDynaset.Fields("EMPNO")
EmpnoField.XMLTagName = "EMP_ID"
'and make it an attribute rather than an element
EmpnoField.XMLAsAttribute = True
'Change the ENAME tag name
Set EnameField = OraDynaset.Fields("ENAME")
EnameField.XMLTagName = "NAME"
'Change the COMM tag name
Set CommField = OraDynaset.Fields("COMM")
CommField.XMLTagName = "COMMISSION"
'Change the JOB tag name
Set JobField = OraDynaset.Fields("JOB")
JobField.XMLTagName = "JOB_TITLE"
startrow = 4
maxrows = 2
'Output at most 2 rows beginning at row 4
XMLString = OraDynaset.GetXML(startrow, maxrows)
Output:
<?xml version = "1.0"?>
<ALL_EMPLOYEES>
<EMPLOYEE EMP_ID="7566">
<NAME>JONES</NAME>
<COMMISSION NULL="TRUE"></COMMISSION>
<JOB_TITLE>MANAGER</JOB_TITLE>
</EMPLOYEE>
<EMPLOYEE EMP_ID="7654">
<NAME>MARTIN</NAME>
<COMMISSION>1400</COMMISSION>
<JOB_TITLE>SALESMAN</JOB_TITLE>
</EMPLOYEE>
</ALL_EMPLOYEES>