Oracle® Objects for OLE Developer's Guide 10g Release 1 (10.1) Part Number B10118-01 |
|
Oracle Objects for OLE support for XML enables you to easily extract data in XML format from an Oracle 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>