• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1066
  • Last Modified:

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

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
kaspak
Asked:
kaspak
1 Solution
 
schwertnerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now