Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle DBMS_XMLGEN Package
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.