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 NULL>MARTIN</NAME>

<COMMISSION>1400</COMMISSION>

<JOB_TITLE>SALESMAN</JOB_TITLE>

</EMPLOYEE>

</ALL_EMPLOYEES>