Solved

How to use xmlGen utility or where I can find its documentation?

Posted on 2002-03-13
1
1,027 Views
Last Modified: 2007-11-27
Hi,
 I am working at oracle8i and need to know how to use xml from oracle environment. I heard about xmlGen utility but i am far away from its documentation.
So if some one can tell me how to use it and where I can find its related documentation.

thanks,
Kaspak
0
Comment
Question by:kaspak
1 Comment
 
LVL 47

Accepted Solution

by:
schwertner earned 100 total points
ID: 6860229
This is a simple program to demonstrate how to insert an XML formatted string
into the Oracle database using the XMLGEN package. This sample selects data from
the emp table in XML format and then inserts it into the emp_temp table.

Program Notes
-------------


 
  1.  Create the test table.

      SQL> CREATE TABLE EMP_TEMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2),
        DEPTNO NUMBER(2));

   2.  Create the procedure XMLInsert.

   3.  Create an anonymous block to call the procedure.


References
----------

Oracle8i XML Reference Guide
Release 3 (8.1.7)
Part Number A83730-01

Oracle8i Application Developer's Guide - XML
Release 3 (8.1.7)
Part Number A86030-01

http://technet.oracle.com/tech/xml/


 
Program
-------

 - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -

-- Filename : xmlInsert.sql
CREATE OR REPLACE PROCEDURE XMLInsert (XMLString IN OUT CLOB) IS
----------------------------------------------------------------
-- Usage: Inserts an XML String into the database.
-- Input Parameters: XMLString ==> CLOB - XML string to be inserted into -- the database.
-- Output Parameters: None.
--------------------------------------------------------------------------------

rowsp integer;

begin
  xmlgen.setRowTag('EMPLOYEE_ROW');
  xmlgen.setRowsetTag('EMPLOYEE_RESULTS');

  -- insert the XML CLOB.!
  rowsp := xmlgen.insertXML('scott.emp_temp',xmlstring);

  dbms_output.put_line('INSERT DONE '||TO_CHAR(rowsp));
  xmlgen.resetOptions;
  dbms_lob.close(xmlString);
  dbms_lob.freetemporary(xmlString);

  exception
    when others then
      dbms_lob.close(xmlString);
      dbms_lob.freetemporary(xmlString);
end;
/

-- Filename : test.sql
-- This file is used to call XMLInsert and pass an XML String.
-- select * from emp_temp

declare
 xmlString CLOB := NULL;
begin
 
  xmlgen.setRowTag('EMPLOYEE_ROW');
  xmlgen.setRowsetTag('EMPLOYEE_RESULTS');
  xmlgen.useLowerCaseTagNames();      -- set the tag names to be all in lower case.
  xmlgen.setmaxrows(20);              -- sets the maximum number of rows .
  xmlgen.setErrorTag('ERROR_RESULT'); -- set the ERROR tag to be ERROR_RESULTS.
  xmlgen.setRowIdAttrName('ENO');    
  xmlgen.setRowIdColumn('EMPNO');
  xmlgen.useNullAttributeIndicator(false);  
  xmlgen.setStyleSheet('http://www.oracle.com/xsl');

  -- Builds an XML string from a select statement.
  xmlString := xmlgen.getXML('select * from scott.emp');

  dbms_lob.open(xmlString,DBMS_LOB.LOB_READONLY);
 
  xmlgen.setIgnoreTagCase(xmlgen.IGNORE_CASE);

  -- Execute the stored procedure and pass in an XML String.
  scott.XMLInsert (xmlString);

  exception
    when others then
      dbms_lob.close(xmlString);
      dbms_lob.freetemporary(xmlString);
end;
/

 - - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -

Sample Output
-------------

SQL> drop table emp_temp;

Table dropped.

SQL> CREATE TABLE EMP_TEMP
  2         (EMPNO NUMBER(4) NOT NULL,
  3          ENAME VARCHAR2(10),
  4          JOB VARCHAR2(9),
  5          MGR NUMBER(4),
  6          HIREDATE DATE,
  7          SAL NUMBER(7,2),
  8          COMM NUMBER(7,2),
  9          DEPTNO NUMBER(2));

Table created.

SQL >@xmlInsert

Procedure created.

SQL> @test

INSERT DONE 15

PL/SQL procedure successfully completed.

SQL> select * from emp_temp;

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
--------- ---------- --------- --------- --------- --------- --------- ---------
     7369 SMITH      CLERK          7902 17-JAN-80       800                  20
     7499 ALLEN      SALESMAN       7698 20-JAN-81      1600       300        30
     7521 WARD       SALESMAN       7698 22-JAN-81      1250       500        30
     7566 JONES      MANAGER        7839 02-JAN-81      2975                  20
     7654 MARTIN     SALESMAN       7698 28-JAN-81      1250      1400        30
     7698 BLAKE      MANAGER        7839 01-JAN-81      2850                  30
     7782 CLARK      MANAGER        7839 09-JAN-81      2450                  10
     7788 SCOTT      ANALYST        7566 19-JAN-87      3000                  20
     7839 KING       PRESIDENT           17-JAN-81      5000                  10
     7844 TURNER     SALESMAN       7698 08-JAN-81      1500         0        30
     7876 ADAMS      CLERK          7788 23-JAN-87      1100                  20
     7900 JAMES      CLERK          7698 03-JAN-81       950                  30
     7902 FORD       ANALYST        7566 03-JAN-81      3000                  20
     7934 MILLER     CLERK          7782 23-JAN-82      1300                  10
      420 ali

15 rows selected.


Additional Search Words
-----------------------
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle global variables 4 52
Invalid Identifier Error 3 65
Oracle SQL Insert Script to copy data between tables 8 45
sql query 9 22
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now