How does one transform data in a table to XML
format? Just as one can select and format into
HTML code, and this is what takes place behind
the scene in iSQL*Plus, one can select from a
table and have the output be well-formed XML.
Use the DBMS_XMLGEN.GETXML
function to accomplish this.
The syntax is select DBMS_XMLGEN.GETXML(‘your
query here’) from dual and with
spool and SQL*Plus settings set correctly, the
output is a dump of data in XML format. Where is
this useful?
Anywhere or anytime one needs to transform data
into XML format, the GETXML function can be
used. Of particular note, Oracle’s new reporting
tool Business Intelligence Publisher is
intimately tied to XML. In fact, report or
template development is largely driven by having
an XML file representation of data to start
with. Dump a portion of the data into XML
format, load the XML data into an RTF document
in Word, call the table wizard, and one has a
report template just like that.
Of course, much more can take place with respect
to manipulating the data. Oracle recommends that
data selection and formatting, as much as
possible, be done via the SELECT statement as
opposed to forcing the RTF processing engine to
manipulate the data. The RDBMS engine is
obviously much more powerful than what Microsoft
Word has to offer.
A select statement like so…
SQL> select
dbms_xmlgen.getxml(
2
'select
3
EMPNO,
4
ENAME,
5
JOB,
6
MGR,
7
HIREDATE,
8
SAL,
9
COMM,
10
DEPTNO
11
from emp
12
where deptno=10')
13
"XML OUTPUT"
14
from dual;
…yields the following output:
XML OUTPUT
---------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-JUN-81</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
…continued…
</ROWSET>
The ROWSET and ROW tags can be
set via other procedures within this package.
Oracle
recommends that DBMS_XMLGEN be used over
DBMS_XMLQUERY.
DBMS_XMLSAVE
This package can be used to upload XML data into
a table. It requires a good bit of manual typing
as each tag has to be quoted and concatenated.
Here is an example of uploading a new record
into the EMP table.
DECLARE
insctx
dbms_xmlsave.ctxtype;
n_rows
NUMBER;
s_xml
VARCHAR2 (32767);
BEGIN
s_xml :=
'<ROWSET>'
|| '<ROW>'
||
'<EMPNO>7783</EMPNO>'
||
'<ENAME>CLARK</ENAME>'
||
'<JOB>MANAGER</JOB>'
||
'<MGR>7839</MGR>'
||
'<SAL>2450</SAL>'
||
'<DEPTNO>10</DEPTNO>'
|| '</ROW>'
||'</ROWSET>';
insctx :=
dbms_xmlsave.newcontext ('EMP');
-- get the context handle
dbms_xmlsave.setrowtag (insctx,
'ROW');
n_rows :=
dbms_xmlsave.insertxml (insctx, s_xml);
-- this inserts the
document
dbms_xmlsave.closecontext
(insctx);
END;
/
A bit cumbersome, but it can be done. There must
be an easier way.
DBMS_XMLSTORE
This package allows uploading an XML file
directly and inserting the contents into a
table. In this example, table EMP3 is a copy of
EMP. The generated XML data file is named
emp3.xml and is located in a directory object
named MYDIR - C:\Temp in this example.
Here is the procedure code to upload an
XML file:
CREATE OR REPLACE
PROCEDURE insertXML
(dirname
IN VARCHAR2,
filename IN
VARCHAR2,
tablename IN
VARCHAR2)
IS
xmlfile BFILE;
myclob CLOB;
insCtx
DBMS_XMLStore.ctxType;
rows number;
BEGIN
dbms_lob.createtemporary(myclob,
TRUE, 2);
-- handle to the XML file
on the OS
xmlfile := Bfilename(UPPER(dirname),filename);
-- open file
DBMS_LOB.fileOpen(xmlfile);
-- copy contents of file
into empty clob
DBMS_LOB.loadFromFile
(myclob,
xmlfile, dbms_lob.getLength(xmlfile));
-- context handle
insCtx :=
DBMS_XMLStore.newContext(UPPER(tableName));
-- this inserts the file
rows :=
DBMS_XMLStore.insertXML(insCtx, myclob);
dbms_output.put_line(to_char(rows)
|| ' rows inserted');
-- close handle
DBMS_XMLStore.closeContext(insCtx);
END insertXML;
/
The process to upload a file is to execute the
procedure and pass in the directory object name,
the file name, and the target table.
These three XML-related packages and code
examples should enable one to handle basic XML
file or data operations. Querying XML via XPath
Query is beyond the scope of this book. However,
being able to quickly generate and upload XML
data is important, and as can be seen, the
process is not that complicated.
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
