Skip to Main Content
  • Questions
  • XML Generation/Manipulation using SQL

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Sajid.

Asked: May 29, 2002 - 9:43 am UTC

Last updated: July 25, 2019 - 3:05 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,
After almost 3 months, i got the chance to submit a question. This is first time i submitting. I have few queries related to XML. I would be grateful to you if you answer with small example.

a) How to generate XML from a standard table (EMP)?
b) How to save XML generated above in a table?
c) How to display at SQL prompt the same XML output from above query?
d) How to create DTD for the just generated XML?
e) How to use it once it is done?
f) What is XPath in relation to XML?
h) How to read XML file stored in hard disk (e.g C:\sajid\xmldoc)?
i) How to save the same XML in a table?

If possible could you please answer this as well.
I have by mistake deleted few important records in our test database and commited the changes. Now i want back those records. Could you please tell step by step how to recover it? Again with simple example.

Thanks a million in advance for all your help.

Regards,




and Tom said...

I asked Sean Dillon, our local XML guru, to take a look at this and here's what he had to say:
--
The Oracle XML Developer's Kits (XDK) is fully supported in Oracle8i 8.1.7, so that's where you should start. A version of the XDK is normally installed in the 8.1.7 database, but you can get the latest copy of the FREE APIs on Oracle TechNet (requires a free registration), here: </code> http://otn.oracle.com/tech/xml/content.html
 On the left hand side of the page, there is an index titled "XML".  Click on the first link that reads "XML Developer's Kits" and you will find links to all the downloads for the various APIs on the various supported platforms.

You've asked a large number of questions that, in order to answer, could easily consume a large chapter of a book or even a book on its own if the person answering was somewhat verbose.  I'll try to give some brief examples along with links to more information where appropriate.


----------------------------
GENERATING XML FROM ORACLE &
DISPLAYING XML FM SQL*PLUS

Generating XML from the database can be performed in a large number of ways.  We'll look at the things you asked from a SQL/PLSQL approach, with the understanding that almost everything we discuss can be accomplished using C, C++ and Java as well.  After you've installed the XDK, use this example to generate some XML from SCOTT.EMP:

SQL> set autoprint on
SQL> set long 100000
SQL> set linesize 100000
SQL> set longchunksize 100000
SQL> var g_clob clob
SQL> declare
  2    l_ctx  dbms_xmlquery.ctxHandle;
  3    l_clob clob;
  4  begin
  5    l_ctx := dbms_xmlquery.newContext('select * from scott.emp');
  6    dbms_lob.createtemporary(:g_clob,true,dbms_lob.session);
  7    :g_clob := dbms_xmlquery.getXml(l_ctx);
  8  end;
  9  /

PL/SQL procedure successfully completed.


G_CLOB
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>17-DEC-80</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>20-FEB-81</HIREDATE>
  <SAL>1600</SAL>
  <COMM>300</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 . . .
</ROWSET>

SQL> set linesize 100
SQL> set longchunksize 80
SQL> set autoprint off

Two quick things here.  First, you'll notice I set my linesize, longchunksize and long settings to 100000.  This was just to get formatting in SQL*Plus.  You don't need this unless you want to see data in SQL*Plus.


---------------------------------
GENERATING DTDs FROM THE DATABASE

Using the above example, a quick change from DBMS_XMLQUERY.GETXML to DBMS_XMLQUERY.GETDTD will get your DTD for the query:

CHANGE...
  7    :g_clob := dbms_xmlquery.getXml(l_ctx);
TO...
  7    :g_clob := dbms_xmlquery.getdtd(l_ctx);
RESULTS IN...

G_CLOB
--------------------------------------------------------------------------------
<!DOCTYPE ROWSET [
<!ELEMENT ROWSET (ROW)*>
<!ELEMENT ROW (EMPNO, ENAME?, JOB?, MGR?, HIREDATE?, SAL
?, COMM?, DEPTNO?)>
<!ATTLIST ROW num CDATA #REQUIRED>
<!ELEMENT EMPNO (#PCDATA)>
<!ELEMENT ENAME (#
PCDATA)>
<!ELEMENT JOB (#PCDATA)>
<!ELEMENT MGR (#PCDATA)>
<!ELEMENT HIREDATE (#PCDATA)>
<!ELEMENT S
AL (#PCDATA)>
<!ELEMENT COMM (#PCDATA)>
<!ELEMENT DEPTNO (#PCDATA)>
]>

-------------------------------------------
SAVING XML INTO ORACLE (TABLE OR OTHERWISE)

There are two ways to do this in Oracle8i 8.1.7.  You can either store native XML in a CLOB/NCLOB/BLOB column, or you can have Oracle serialize XML into a relational table.  Saving data into a CLOB column is simple using the above example.  You'd simply insert the resulting CLOB value into a table.  Even better, you could insert an empty_clob() into a table, returning the value into a local variable.  Then just write to the local variable i.e. - 

SQL> create table xmldocs(
  2    docname varchar2(50),
  3    xmldoc  clob)
  4  /

Table created.

SQL> declare
  2    l_ctx  dbms_xmlquery.ctxHandle;
  3    l_clob clob;
  4  begin
  5    l_ctx := dbms_xmlquery.newContext('select * from scott.emp');
  6    insert into xmldocs values ('EMP TABLE', empty_clob())
  7    returning xmldoc into l_clob;
  8    dbms_xmlquery.getXml(l_ctx, l_clob);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> col docname for a20
SQL> col xmldoc  for a60
SQL> select *
  2    from xmldocs
  3  /

DOCNAME              XMLDOC
-------------------- -----------------------------------------------------------
EMP TABLE            <?xml version="1.0"?>
                     <ROWSET>
                      <ROW>
                       <EMPNO>7369</EMPNO>
                       <ENAME>SMITH</ENAME>
                       <JOB>CLERK</JOB>
                       <MGR>7902</MGR>
                       <HIREDATE>17-DEC-80</HIREDATE>
                       <SAL>800</SAL>
                       <DEPTNO>20</DEPTNO>
                      </ROW>
                      <ROW>
                       <EMPNO>7499</EMPNO>
                       <ENAME>ALLEN</ENAME>
                       <JOB>SALESMAN</JOB>
                       <MGR>7698</MGR>
                       <HIREDATE>20-FEB-81</HIREDATE>
                       <SAL>1600</SAL>
                       <COMM>300</COMM>
                       <DEPTNO>30</DEPTNO>
                      </ROW>
                      . . .
                     </ROWSET>

SQL> set echo off

For serializing this XML into a table (for example, you're getting this XML document off the Internet and you want to store it in a relational EMP table), you would again use the XML SQL Utility to accomplish this.  In the following example, we use the XSU to save an XML document into a table:

SQL> create table myemp
  2  as select *
  3       from scott.emp
  4      where 1 = 0
  5  /
Table created.

SQL> select *
  2    from myemp
  3  /
no rows selected

SQL> declare
  2    l_clob  clob :=
  3  '<?xml version = "1.0"?>
  4  <ROWSET>
  5     <ROW num="1">
  6        <EMPNO>7369</EMPNO>
  7        <ENAME>SEANDILLON</ENAME>
  8        <JOB>DBDUDE</JOB>
  9        <MGR>7902</MGR>
 10        <HIREDATE>12/17/1980 0:0:0</HIREDATE>
 11        <SAL>800</SAL>
 12        <DEPTNO>20</DEPTNO>
 13     </ROW>
 14     <ROW num="2">
 15        <EMPNO>7499</EMPNO>
 16        <ENAME>TOMKYTE</ENAME>
 17        <JOB>DBDUDE</JOB>
 18        <MGR>7698</MGR>
 19        <HIREDATE>2/20/1981 0:0:0</HIREDATE>
 20        <SAL>1600</SAL>
 21        <COMM>300</COMM>
 22        <DEPTNO>30</DEPTNO>
 23     </ROW>
 24     <ROW num="3">
 25        <EMPNO>7521</EMPNO>
 26        <ENAME>MICKEYMOUS</ENAME>
 27        <JOB>RAT</JOB>
 28        <MGR>7698</MGR>
 29        <HIREDATE>2/22/1981 0:0:0</HIREDATE>
 30        <SAL>1250</SAL>
 31        <COMM>500</COMM>
 32        <DEPTNO>30</DEPTNO>
 33     </ROW>
 34  </ROWSET>';
 35  
 36    l_ctx   dbms_xmlsave.ctxType;
 37    l_rows  number;
 38  begin
 39    l_ctx := dbms_xmlsave.newContext('MYEMP');
 40    l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
 41    dbms_xmlsave.closeContext(l_ctx);
 42    dbms_output.put_line(l_rows || ' rows inserted...');
 43  end insert_xml_emps;
 44  /
3 rows inserted...
PL/SQL procedure successfully completed.

SQL> select *
  2    from myemp
  3  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SEANDILLON DBDUDE          7902 17-DEC-80        800                    20
      7499 TOMKYTE    DBDUDE          7698 20-FEB-81       1600        300         30
      7521 MICKEYMOUS RAT             7698 22-FEB-81       1250        500         30

SQL> drop table myemp
  2  /

Table dropped.


-----------------------------
HOW TO USE IT ONCE IT IS DONE

You name it.  XML has a variety of usages, but don't use XML for XMLs sake.  If you have a valid requirement for the storage and use of XML, great.  (Such as you are exchanging XML data with 3d parties, you need an audit trail of the data transfered, the data you are storing is totally unstructured and does not lend itself to relational storage, etc.).  

<SOAPBOX>
There are many people in the industry that feel using XML is cool, so you should just do it.  Personally, I think XML is a little overhyped and although useful as a common denominator in data formats, you should have a firm grip on the reason you MUST store/use XML before you head down that road.
</SOAPBOX>


----------------------------------------
READING XML DOCUMENTS FROM THE HARD DISK

This can be accomplished in a variety of ways.  In 8.1.7, I used Java Stored Procedures from inside the database.  You just need to learn some of the Java APIs for the XDK for creating XML documents, and the Java APIs for reading files from your disk.


-------------
WHAT IS XPATH

Before I go into the Sean Dillon version of what XPath is, let me refer you to the abundance of resources on the Internet that can answer that question in a much more elegant, explanative COMPLETE manner :-).  Oracle has a few links that I think you would benefit greatly from:

AN XML PRIMER: 
http://www.oracle.com/pls/db901/db901.to_toc?pathname=appdev.901/a88895/appaxml.htm#621642
Oracle XML Developer's Kits Primer (don't be deterred from the "9i-esque" of the XDK.  It's backwards compatible w/ 8.1.7): 
http://www.oracle.com/pls/db901/db901.to_toc?pathname=appdev.901/a88894/toc.htm

There are a few books out there I highly recommend for IT folks looking to use XML with Oracle.  The best yet is Steve Muench's "Building Oracle XML Applications" which you can find here: 
http://www.oreilly.com/catalog/orxmlapp/


------------------------------------------
FINDING DATA YOU DELETED THEN COMMITTED...

Well, in Oracle9i I would tell you to use Flashback Query.  This new feature of 9i allows you to reconstruct the data in your database as it existed some time in the past.  Since you are still running on 8.1.7 :-), your only options are recovery fm backup or using Log Miner (recommended route).  DBMS_LOGMINER is a supplied package in the database that you can use to do things like find out when a table was 'accidentally' dropped, perform some type of auditing on a table after the actions have been committed, or 'undo' a transaction (your case).  I'm not going to get into all the details of using Log Miner here, but instead will refer you to the documentation.  
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/appdev.901/a89852/dbms_log.htm#73729
Keep in mind, this is 9i documentation so there are going to be differences between 9i and 8.1.7.  You should refer to the documentation that came as a part of your 8.1.7 database.  Another GREAT place you will find information about Log Miner is in Tom's (of Ask Tom ;-) book, Expert One-on-One Oracle.  
http://www.amazon.com/exec/obidos/ASIN/1861004826
He goes into great depth in practical examples of using Log Miner.

Hope that helps!  

Sean Dillon
Senior Technologist, Oracle Corporation
Author "Beginning Oracle Programming" 
http://www.amazon.com/exec/obidos/ASIN/186100690X <code>



Rating

  (106 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Was of great help

Sajid, May 30, 2002 - 4:35 am UTC

You are simply the best. The only thing i couldnt follow is how to recover data. Could you please explain the same for Oracle 9i?
Thanks in advance.

Tom Kyte
May 30, 2002 - 7:46 am UTC

you need to read the documentation on log miner. log miner is a tool that will read your logs and generate sql statements that can undo an insert/update/delete. Read it and you'll understand (the link is right there).

if you are one who won't read docs, search this site for

log miner

I have examples there (but you'll only get part of the total functionality that way).

As for flashback, search this site for

flashback

and you'll see yet more examples.

Hamid, November 28, 2002 - 7:48 am UTC

That's brilian, but what is the fastest way of pulling your data from a table in XML format. PL/SQL is not the fastest tool.

Tom Kyte
November 29, 2002 - 9:09 am UTC

why isn't it? have you shown this? I mean -- in 9i, the xmldb stuff is almost 100% in C, some in java (none is in plsql, there was only a very early - v8 - plsql based limited implementation).

the dbms_xml* stuff is just a thin layer on top of C or Java for XML. If you are in a stored procedure and need XML output.... I sort of think the plsql api would be the best bet.

Inserting LARGE XML files into a database!

Edgar Chupit, January 15, 2003 - 6:31 am UTC

Hello Tom and Sean,

This article is great introduction to working with XML in database.
But let's suppose following scenario, we have a third party system and we use XML to transfer data from that app into a database. Once a month we need to transfer very large amount of data (Let's 100Mb in XML), I was trying to parse this file using DOM and got java.lang.OutOfMemoryError exception. Then I read this article and wanted to test how does XML Utility works with such a large files and also got java.lang.OutOfMemoryError exception. Maybe there is some
methods to split this data in database into smaller chunks and than parse it and save. My question is: is it possible and how to parse and insert large XML file into a database? Thank you.

--

SQL> create table test000 as select * from all_objects;

Table created.

SQL> begin
  2   for i in 0 .. 10 loop
  3      insert into test000 select * from all_objects;
  4   end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from test000;

  COUNT(*)
----------
    245520

SQL> create table result as select * from test000 where 0 = 1;

SQL> set serveroutput on;

SQL> declare
  2      l_ctx  dbms_xmlquery.ctxHandle;
  3      l_clob clob;
  4      l_sctx   dbms_xmlsave.ctxType;
  5      l_rows  number;
  6  begin
  7      l_ctx := dbms_xmlquery.newContext('select * from test000');
  8      dbms_lob.createtemporary(l_clob,true,dbms_lob.session);
  9      l_clob := dbms_xmlquery.getXml(l_ctx);
 10      dbms_output.put_line('CLOB size = '||dbms_lob.getlength(l_clob));
 11  -------------------------------------------------------------------------------
 12      l_sctx := dbms_xmlsave.newContext('RESULT');
 13      l_rows := dbms_xmlsave.insertxml(l_sctx,l_clob);
 14      dbms_xmlsave.closeContext(l_sctx);
 15      dbms_output.put_line(l_rows || ' rows inserted...');
 16  end test0001;
 17  /
CLOB size = 114494290
declare
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.OutOfMemoryError
ORA-06512: at "SYS.DBMS_XMLSAVE", line 65
ORA-06512: at line 13
 

Tom Kyte
January 15, 2003 - 9:03 am UTC

I asked Sean Dillon to take a look at this one, here's what he had to say:
----------------------

Hi Edgar,

Yes, this is one of the issues you must contend with when dealing with large XML files, as the DOM parser loads the entire document into memory before you can parse it. DOM isn't the only parser in town, however... you can always use SAX as an alternative, which is generally what the industry recommends for larger XML files. Where's the cutoff? I guess that depends on the parsers, the complexity of the document, the environment in which you are doing the parsing, and the resources at your disposal (hardware, mem, cpu, etc.).

While DOM loads the entire XML document into a tree structure in memory, SAX requires only a single pass through the entire document to parse it. This cuts down on your flexibility with an API, but improves performance and substantially increases the amount of data you are able to process.

For your example above, there's no way to tell the XML SQL Utility to use the SAX parser, so for a query that returns that much data you will most likely look into an alternative mechanism for generating your XML (SQLX, hand-generated, etc). Based on what you said above, however, I don't think this is necessarily your problem. You are trying to load large XML documents into your database once/month (or so). I'd encourage you to check out the SAX parser and try using that instead of DOM. For more information about the SAX parser, here are some links:
</code> http://otn.oracle.com/tech/xml/content.html http://www.saxproject.org http://builder.com.com/article.jhtml?id=u00220020527adm01.htm

Steve Meunch, a senior product manager out of Oracle's Headquarters, wrote a book entitled "Building Oracle XML Applications".  In Chapter 6, pp 227 .. 234, he shows you a Java class named XMLDocumentSplitter that uses SAX & Java to break down a large XML document into "bite-sized chunks" that are easier to deal with in DOM.  Just a thought.  Here's his book:
http://www.amazon.com/exec/obidos/tg/detail/-/1565926919/qid=1042639343/ <code>

I hope that helps!

_smd_

XML

A reader, January 15, 2003 - 3:16 pm UTC

Do I absolutely have to use java to load/install xml kit ?

At this point we are not using the java porting of
oracle 8.1.7 (EE)/Linux73. But we do use XML all foreign/external data feed in in XML format I have to load
the data after removing the XML tags. and generate XML to provide data to other source after process.

1.) At this point I just see 9.x.x XDK
will it work with 8.1.7 ?
2.) Can you please clearly tell me how can I install it ?
(Do I have to install or is it stand alone app. ? )
(limitation: I can not use loadjava )

Thanks,

Tom Kyte
January 15, 2003 - 7:10 pm UTC

If you want the xml stuff in the db -- you'll be hard pressed NOT to use loadjava.

XML

A reader, January 16, 2003 - 8:37 am UTC

Hi tom,

I want to use xml_plsql (XDK for Pl/sql).
I want to do exactly same as Mr. Sean Dillon demonstrated above.

1.) get the xml, strip the xml tag get the data insert only
data into database
2.) and query the database in such a way (may be using xsu,
i don't know yet) so that I get the xml back from the
data base and send it to the client database

there is no web I don't know what you are saying there.
But, please tell me how can I load and
perform above tasks with out java or
though it is called xml_plsql it is completely depandent on
java and one has to know the java, use the java option to
utilize this feature of oracle other wise not

Thanks,




Tom Kyte
January 16, 2003 - 9:26 am UTC

all of the xml stuff is under the covers in java. that is what I'm saying. once upon along time ago there was a "plsql only" one with very very limited features -- but plsql isn't the right language to parse documents (processing database data -- nothing better, parsing xml, umm, no).

You do not have to KNOW java -- you just have to have support for it in the database.

Thanks,

A reader, January 16, 2003 - 10:16 am UTC

Hi Tom,

Thanks for your reply.
My DBA says that if it requires java to be enabled, I should
do it outside no java in the db as db is to store and retrive the database an enalbling java =>

1.) overhead
2.) more maintanance
3.) load

Thanks again !!



Tom Kyte
January 16, 2003 - 10:42 am UTC

This is Sean...

While I understand your adminstrator's point of view, there isn't anything that needs to be maintained here. There is a touch of overhead when using Java in the database because you are starting up a Java Virtual Machine, but Java has its advantages as well. In this case, Tom hit it on the head when he said the PL/SQL stuff in the XML Developer's Kits loaded into the database rely on the Java code under the covers. That being said... however... in Oracle9iR2, quite a bit of the functionality in the XML Developer's Kits has been rewritten in C and linked into the database. Now the PL/SQL code in the 9iR2 database supplied packages is no longer reliant on Java. Actually, quite a bit of the Java code that performs XML functionality in the Java supplied packages is written on top of C libraries under the covers as well, simply for performance.

If you want to avoid loading Java into the database, you can (1) move the functionality to the app server, (2) use Oracle9i Release 2 which includes more features than I could possibly describe here... or (3) write it yourself.

Hope that helps!

_smd_

Thanks Sean !!

A reader, January 16, 2003 - 11:23 am UTC


Too many XML API's

A reader, January 17, 2003 - 7:31 am UTC

Hi Tom, Sean

I am a PL/SQL programmer using 9iR2. I was going through various Oracle manuals related to XML and that left me confused. It would be great if you could clarify a few things -

1. What is the difference between XDK and XML DB.

2. There are a lot of APIs given in XDK and XML DB features as well. There are different APIs which do the same job. Like for XML generation from PL/SQL we have some 4 or 5 methods that can be used. How do we chose which one to use

3. You have mentioned that some APIs are now written in C instead of Java, but the documentation does not mention which (Only exception is dbms_xmlgen). This way we can avoid the JVM calls.

A general point - Oracle has been introducing some fantastic features, but its not making it easier to learn them. AskTom is doing a great job, but I think we need more TOMs who can help us out with this. Oracle magazine is also very good, but we should have more database technology articles there (It is more j2ee biased it seems)

Thanks


Tom Kyte
January 18, 2003 - 8:33 am UTC

I asked Sean to take a look at this, here's what he had to say:
-----------------------------

The XDK is the XML Developer's Kit. It is a collection of APIs, tools & utilities for performing XML-related functionality. It runs in the database (as Java stored procedures & PL/SQL stored procs), in the application server (as Java classes, Java Beans, C & C++ libraries), or even in a fat client if you're so inclined. As you can see above, there are numerous languages supported, including Java, JavaBeans (not really a language right but cool nonetheless), C, C++ and my personal favorite PL/SQL :-). The XDK can be freely downloaded from the Oracle Technology Network, and installed into any Oracle database provided it is version 8.1.6 or above. It costs nothing to use.

XML DB is the collection of XML-related functionality introduced into the Oracle database in version 9.2.x.x. Included in this functionality (but not limited to...) are Java stored procedures, PL/SQL supplied procs, new operators for generating XML called "SQLX", the XML Repository, the native XML datatype called XMLType, and more. This functionality is ONLY natively available in the database. The only way to use it from the application server or from a client machine is through the database. The Java stored procedures and PL/SQL code in the database is based on C libraries, providing a theoretical improved performance through lowered memory consumption and CPU load.

The APIs that are written in C as opposed to Java are in XML DB. You're right, the documentation doesn't really talk about which are which, but as a guideline... any PL/SQL you find in the XML Developers Kits are Java-based. The PL/SQL found in XML DB is C-library based. The XML DB supplied PL/SQL is usually preceded by DBMS_<packagename>. If you wanted to figure this out for yourself you have only to alter your session and set SQL_TRACE to true. Execute the functionality in question, then end your session and TKPROF the trace file. This will tell you if any Java was called under the covers.

I hope that helps!

_smd_
Sean Dillon


complex xml generation

reader, April 27, 2003 - 8:31 am UTC

Sean Dillon,

I wanted to generate xml from database. My requirments are

1. i should not use JVM
2. less of resource consumption
3. the xml that i need to generate is huge - that caters to 10000 records from 3 tables. its a complex, nested with many tags through which way i should generated ?
3.Does XML DB supplied PL/SQL supports complex xml generation ?
4. how do you identify XDK's procedures? - i just want to avoid it ! - it uses my JVM
>(The XML DB supplied PL/SQL is usually preceded by DBMS_<packagename>.)

Thanks

Tom Kyte
April 27, 2003 - 8:50 am UTC

you lose.

#1 precludes me from reading on. you'll not be doing it in the database. much of it is in java, much of it is in C.


the DBMS_ packages -- guess what, a thin layer on top of -- java. Just like utl_smtp and other pieces of functionality. sometimes C, sometimes java.

When to use which methode????

Vipin, August 07, 2003 - 10:59 am UTC

Hi Tom,

This is very basic doubt regarding XML generation from RDBMS data.

I just wanted to know when to use DBMS_XMLGEN instead of SYS_XMLGEN or SYS_XMLGEN instead of SQLX functions like XMLELEMENT, XMLFOREST etc. In almost any scenario I deal with, I am getting a feeling that all these can be used. I am sure that there would be specific context in which each methode would outperform the other. Please explain this with performance as the very most inportant factor.


Too many XML API's

A reader, August 26, 2003 - 9:05 am UTC


Too many XML API's

A reader, August 26, 2003 - 9:05 am UTC


Getting sequence and updating multiple XML files

A reader, August 26, 2003 - 8:15 pm UTC

Tom,
I have 2 xml files with the data shown below. (The data in the XML file corresponds to the data in the emp, dept table s).
Scenario I have
The deptno in the dept table is generated used a sequence number. I want to load the data in the 2 xml files I have to the emp/dept table, but the dept table already has records for depts. 10,20,30 and 40. I want to change the deptno values from 10, 20, 30, 40 to 41, 42, 43, 44 respectively in both the XML files and then insert the data into the emp/dept tables (The values 41, 42, 43, 44 are the values I have got using the sequence number).
How can the DEPTNO value be changed in both the XML files(IDRef!)?

--dept.xml

<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
</ROW>
<ROW>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
</ROW>
<ROW>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
</ROW>
<ROW>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
</ROW>
</ROWSET>



--emp.xml

<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>

<EMPNO>7654</EMPNO>
<ENAME>MARTIN</ENAME>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7698</EMPNO>
<ENAME>BLAKE</ENAME>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7788</EMPNO>
<ENAME>SCOTT</ENAME>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<DEPTNO>10</DEPTNO>

</ROW>
<ROW>
<EMPNO>7844</EMPNO>
<ENAME>TURNER</ENAME>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7876</EMPNO>
<ENAME>ADAMS</ENAME>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7900</EMPNO>
<ENAME>JAMES</ENAME>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW>
<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
<EMPNO>7934</EMPNO>

<ENAME>MILLER</ENAME>
<DEPTNO>10</DEPTNO>
</ROW>
</ROWSET>


Thank you


Tom Kyte
October 27, 2003 - 2:56 pm UTC

Sean here...

This isn't a hard thing to do. Your best bet.. honestly, is find-replace or some sexy sed/awk script to update your XML documents ;-). If you're bent on using the database to do this, I would recommend you insert these into some OTHER tables that are used for inbound XML. After the inserts of the XML documents (which would still have 10, 20, 30, 40 for their IDs...), I would use a stored procedure to simply execute an INSERT INTO DEPT (deptno, dname, etc...) SELECT DEPTNO + 1, DNAME, etc... FROM XMLDEPT. Same thing holds true for the XML EMP table...

We've left the XML functionality and crossed into basic application development issues. I guess I'd have to ask "WHY are you trying to do this? What are you getting at?"

Serialize to more than one table (Master-details relationship)

Victor Jaen, October 27, 2003 - 11:16 am UTC

Hello! Could you elaborate the example of serializing the XML into a table, let's say the XML file has data which corresponds to 2 tables (repetitive tags for a detail table), is it possible?

Tom Kyte
October 27, 2003 - 3:26 pm UTC

Sean here...

This is pretty easy to accomplish. There is no automated XML utility in Oracle to insert a single XML document into two different tables... What you could do, however, is create a join view on the two tables, then write an INSTEAD OF trigger on the join view. Insert the XML document into the join view. The INSTEAD OF trigger's job would be to insert rows into the appropriate tables based on the values of the parent key found in each ROWSET of the XML document.

As an example, I have an XML document that looks like SO:

<?xml version = "1.0"?>
<ROWSET>
<ROW num="1">
<DEPTNO>10</DEPTNO>
<DNAME>SALES</DNAME>
<EMPNO>100</EMPNO>
<ENAME>MARK JOHNSON</ENAME>
</ROW>
<ROW num="2">
<DEPTNO>20</DEPTNO>
<DNAME>TECHNOLOGY</DNAME>
<EMPNO>200</EMPNO>
<ENAME>TOM KYTE</ENAME>
</ROW>
<ROW num="3">
<DEPTNO>20</DEPTNO>
<DNAME>TECHNOLOGY</DNAME>
<EMPNO>300</EMPNO>
<ENAME>SEAN DILLON</ENAME>
</ROW>
</ROWSET>

So you can see... the department data and the employee data co-mingled. We want to normalize this into two tables... so here's what I'd do:

-----------------------------
system@SLAP> create table dept (
2 deptno number
3 primary key,
4 dname varchar2(30));
Table created.

system@SLAP> create table emp (
2 empno number
3 primary key,
4 deptno number,
5 ename varchar2(30));
Table created.

system@SLAP> create view deptemp as
2 select d.deptno, d.dname, e.empno, e.ename
3 from dept d, emp e
4 where d.deptno = e.empno;
View created.

system@SLAP> create or replace trigger deptemp_ioifer
2 instead of insert on deptemp
3 declare
4 begin
5 begin
6 insert into dept (deptno, dname)
7 values (:new.deptno, :new.dname);
8 exception
9 when DUP_VAL_ON_INDEX then
10 update dept
11 set dname = :new.dname
12 where deptno = :new.deptno;
13 end;
14 --
15 insert into emp (empno, deptno, ename)
16 values (:new.empno, :new.deptno, :new.ename);
17 end;
18 /
Trigger created.

system@SLAP> declare
2 l_clob clob := '<?xml version = "1.0"?>
3 <ROWSET>
4 <ROW num="1">
5 <DEPTNO>10</DEPTNO>
6 <DNAME>SALES</DNAME>
7 <EMPNO>100</EMPNO>
8 <ENAME>MARK JOHNSON</ENAME>
9 </ROW>
10 <ROW num="2">
11 <DEPTNO>20</DEPTNO>
12 <DNAME>TECHNOLOGY</DNAME>
13 <EMPNO>200</EMPNO>
14 <ENAME>TOM KYTE</ENAME>
15 </ROW>
16 <ROW num="3">
17 <DEPTNO>20</DEPTNO>
18 <DNAME>TECHNOLOGY</DNAME>
19 <EMPNO>300</EMPNO>
20 <ENAME>SEAN DILLON</ENAME>
21 </ROW>
22 </ROWSET>';
23
24 l_ctx dbms_xmlsave.ctxType;
25 l_rows number;
26 begin
27 l_ctx := dbms_xmlsave.newContext('DEPTEMP');
28 l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
29 dbms_xmlsave.closeContext(l_ctx);
30 dbms_output.put_line(l_rows || ' rows inserted...');
31 end insert_xml_emps;
32 /
PL/SQL procedure successfully completed.

system@SLAP> select * from dept;

DEPTNO DNAME
---------- ------------------------------
10 SALES
20 TECHNOLOGY

system@SLAP> select * from emp;

EMPNO DEPTNO ENAME
---------- ---------- ------------------------------
100 10 MARK JOHNSON
200 20 TOM KYTE
300 20 SEAN DILLON

-----------------------------

...and there you have it. Hope that helps!

_smd_

What about repetitive tags?

Victor Jaen, October 27, 2003 - 4:26 pm UTC

How can I handle this? One row for the department table and 3 rows for the employee table in the same XML row.
<?xml version = "1.0"?>
<ROWSET>
<ROW num="1">
<DEPTNO>10</DEPTNO>
<DNAME>SALES</DNAME>
<EMPLOYEE>
<EMPNO>100</EMPNO>
<ENAME>MARK JOHNSON</ENAME>
</EMPLOYEE>
<EMPLOYEE>
<EMPNO>200</EMPNO>
<ENAME>VICTOR JAEN</ENAME>
</EMPLOYEE>
<EMPLOYEE>
<EMPNO>300</EMPNO>
<ENAME>JHON SMITH</ENAME>
</EMPLOYEE>
</ROW>
</ROWSET>

Thanks a lot!

Tom Kyte
October 27, 2003 - 6:46 pm UTC


1* select dbms_xmlgen.getxml( 'select deptno, dname, cursor( select empno, ename from emp where emp.deptno = dept.deptno ) employee from dept where deptno = 10' ) from dual
scott@ORA920> /

DBMS_XMLGEN.GETXML('SELECTDEPTNO,DNAME,CURSOR(SELECTEMPNO,ENAMEFROMEMPWHEREEMP.D
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<EMPLOYEE>
<EMPLOYEE_ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
</EMPLOYEE_ROW>
<EMPLOYEE_ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
</EMPLOYEE_ROW>
<EMPLOYEE_ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
</EMPLOYEE_ROW>
</EMPLOYEE>
</ROW>
</ROWSET>


And what about xsl...

Mirjana, October 28, 2003 - 2:06 am UTC

Hi Tom...
Just one question:
DBMS_XMLQUERY has a procedure SETXSLT that applies XSL to an XML document. Is there similar procedure for DBMS_XMLGEN package? How can an XSL be applied to XML document using 9iR2 specific features (I'm trying to avoid using DMBMS_XMLQUERY)?

Thank you...


Tom Kyte
October 28, 2003 - 7:57 am UTC


what is the reasoning for avoiding dbms_xmlquery?

Asking again

Victor Jaen, October 28, 2003 - 7:49 am UTC

I receive a XML file with data for departments and
employees tables in the same XML rowsets, how can I insert the data to those tables using dbms_xmlsave.insertxml? any other way?
<?xml version = "1.0"?>
<ROWSET>
<ROW num="1">
<DEPTNO>10</DEPTNO>
<DNAME>SALES</DNAME>
<EMPLOYEE>
<EMPNO>100</EMPNO>
<ENAME>MARK JOHNSON</ENAME>
</EMPLOYEE>
<EMPLOYEE>
<EMPNO>200</EMPNO>
<ENAME>VICTOR JAEN</ENAME>
</EMPLOYEE>
<EMPLOYEE>
<EMPNO>300</EMPNO>
<ENAME>JHON SMITH</ENAME>
</EMPLOYEE>
</ROW>
</ROWSET>

Thanks!

Tom Kyte
October 28, 2003 - 8:12 am UTC

</code> https://asktom.oracle.com/Misc/oramag/on-procedures-flushes-and-writes.html <code>
is another way.

check out
Article-ID: <Note:227476.1>

on metalink.oracle.com for a dbms_xmlsave example

XSL...

Mirjana, October 28, 2003 - 9:31 am UTC

Hi Tom,
I read in
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:11650482108651 <code>
that DBMS_XMLGEN "..It's much the same as the
DBMS_XMLQuery package in the XDK, but it's linked to the C libraries and
compiled into the database kernel, offering faster speeds (XMLQuery uses Java
under the covers)..."
I'm not really trying to avioid DBMS_XMLQUERY, rather just trying to make the same thing working
with DBMS_XMLGEN if possible (as it offers faster speeds).

Thanks...


Tom Kyte
October 28, 2003 - 10:23 am UTC

well, both packages are documented in the supplied packages guide. I don't see anything referencing xsl in dbms_xmlgen -- it is a rather mean/lean package.

xsl..

Mirjana, October 28, 2003 - 11:09 am UTC

Yes...I saw the supplied packages documentation also, but I thought you might know something more about it.

Too bad ... It could be really useful having something like SETXSLT for DBMS_XMLGEN.

Thanks anyway,

Regards.

additional question

mark griffin, January 05, 2004 - 5:22 pm UTC

I have a xsd schema(hopefully correct terminology) that i need to:
load into my database

map xsd to series of data columns in my database(entites from xsd are completely different in terms of my database attributes)

once mapped then pull data from my database and output into xml format

I've already read a lot of the Oracle XML documentation. However, either I missed the example/documentation on how to perform this action or its not there.

Could you please assist?

thank you!


Tom Kyte
January 07, 2004 - 8:31 am UTC

Hi Mark,

Using XML DB, it's relatively simple to register an XML Schema and have Oracle create the storage architecture "under the covers" for XML documents you load which validate against said schema. Alternatively, you can use XML DB Schema annotations to tell Oracle how to architect the storage... the table names to be created, the column names, the data types, various parameters such as max length of fields, etc. It sounds like you're trying to use XML DB to load your XML documents into an EXISTING set of tables and columns. I.e., you have a table where you have rows and columns already, and you want to put your XML document into XML DB, and have it parsed and loaded into that table. This is not available out of the box, and XML DB is unable to use this type of storage architecture. At this time, the storage for XML Schema-related XML documents in XML DB is dedicated to those documents, and must be created by the database through the XML Schema registration process.

How to do all of this is available in the XML Database Developer's Guide, XML DB. Chapters 4 and 5 are where you should spend your time if this is what you need.

Alternatively, if you'd like to maintain your own tables and have your XML documents parsed and loaded into them... you can use the XML Developer's Kits. This is a programmatic interface (you'll be writing code to accomplish your task) that lets you specify an XML document and have Oracle load it into a table. The only catch to this is the document must be in a specific structure. I'd first recommend you search through Ask Tom for examples, search on "XDK" and/or "<ROWSET>". The XDK has a PL/SQL, C, C++, Java and JavaBeans interface so whatever your programming skills, you should be covered.

When you want to generate XML from your existing relational tables (and you're not using XML DB), I'd recommend you use SQLX. Again, there are plenty of examples of how to do this throughout AskTom. Just search on SQLX or look here: </code> http://asktom.oracle.com/~sdillon/rss.html
 I wrote an overview of building an RSS interface into AskTom, and there is an example of how to generate XML from relational tables (this is me generating the RSS from AskTom's relational tables).  Hope that helps.

_smd_

** Sean Dillon **
** Principal Technologist, Oracle Corporation **
** Author, Beginning Oracle Programming **
** 
http://www.amazon.com/exec/obidos/tg/detail/-/186100690X <code>**


follow up to additional question

Mark Griffin, January 07, 2004 - 9:59 am UTC

Hello again,

First thing is I just submitted brand new question covering this same topic so please ignore question. Thanks and sorry for confusion.

To follow up my previous question.

My .xsd does not contain data at all. It simply contains series of data elements that need to be pulled from my database:
ex of .xsd:
<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XMLSPY v2004 rel. 2 U (</code> http://www.xmlspy.com
 by Wang (bline express) -->
<!-- edited with XML Spy v4.4 U (
http://www.xmlspy.com
 by Nancy Rackley (B-Line Express) -->
<xs:schema xmlns:xs="
http://www.w3.org/2001/XMLSchema" <code>elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="INVENTORY">
<xs:annotation>
<xs:documentation>XML SCHEMA FOR INVENTORY DATABASE</xs:documentation>
</xs:annotation>
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="TASK">
<xs:complexType>
<xs:all minOccurs="0">
<xs:element name="TASK_NAME">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="4000"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="FUNDING" minOccurs="0">


Now i need to take this .xsd and either load it into my database or utilize it so that I can pull data from my database. The data that I pull will be spit back out into this same .xsd but as data. The rub is that these .xsd elements dont directly map to my database. Logically I know what the mapping is. The piece that Im struggling with is how to perform the mapping programatically.

It's at this point I dont find what Im trying to accomplish in Oracle documentation. It may be that its there but I cant find it.

Thank you for your assistance.

another follow-up question

Mark Griffin, January 09, 2004 - 2:18 pm UTC

I guess the question I have at this point is
Oracle XML DB Foldering what I need to accomplish my goal?

Tom Kyte
January 11, 2004 - 4:31 pm UTC

Hi Mark, Sean here again. I'll answer both the previous "feedback"'s in this followup.

Yes, I understand the XSD vs XML (document formatting vs document content) thing. I also understand your requirements, and now it's easy to understand why you've been struggling.

There is no automated tool for accomplishing what you're trying to accomplish. Oracle doesn't have a tool to map an XML document into your custom-built relational (obj-relational possibly, understood) data model. The reason there's not a tool for doing this is because Oracle built this capability into XML DB... but the limitation there is that Oracle must create and manage the underlying relational storage. The steps involved there would be..

1. You register your XSD with XML DB through DBMS_XMLSCHEMA.
2. Oracle, in response to the XSD registration, creates the table(s) to store the underlying CONTENT that is read from your XML files.
3. You load an XML document into Oracle that references the XSD, Oracle stores that content in the underlying storage architecture created in (2), above.
4. You can "load" documents into the XML Repository (which is XML Foldering, as you asked above)... or you can create XMLType columns/tables that reference the registered XSD... which will also then utilize this underlying storage.
5. You can then access the data as object-relational rows (by accessing the underlying storage) or as XML documents (by using the XMLType methods or through the XML Repository access methods).

What you're trying to do, however, is NOT THIS. From what you've stated above, you have an existing relational data model you'd like to load XML into. You MAY want to extract the XML data back out of this model as XML. To accomplish this, you need to PARSE your XML documents and programatically load the rows yourself. Oracle supports the DOM and SAX parsers, so you're covered for the two different types of parsers. For documentation on the parsers, check the XML Developer's Kits (XDKs) and the Supplied Packages Reference. You can do the parsing in PL/SQL or Java (we support both inside Oracle).

Bottom line, no XML DB Foldering is not what you need to accomplish your goal. You need to learn how to use the XML Developer's Kits and/or the supplied packages (DBMS_XMLPARSER, DBMS_XMLDOM, associated Java classes if you build using Java). Essentially, in the code YOU WRITE, you'll parse each document and insert the rows into your appropriate table(s).

_smd_

performace ?

Hannibal, January 12, 2004 - 11:26 am UTC

hi tom,

i am looking at the xml-feaures of oracle with interest. I did some test but the result
is a little bit confusing...

here are my create table statement + the way i put some simply data in it ...


create table t ( x integer, y varchar2(32));

begin
for v_int in 1 .. 25
loop
insert into t ( x, y ) values ( v_int , 'v'||to_char(v_int));
end loop;
end;

jom@NINA.US.ORACLE.COM> select * from t;

X Y
---------- --------------------------------
1 v1
2 v2
3 v3
4 v4
5 v5
6 v6
7 v7
8 v8
9 v9
10 v10
11 v11
12 v12
13 v13
14 v14
15 v15
16 v16
17 v17
18 v18
19 v19
20 v20
21 v21
22 v22
23 v23
24 v24
25 v25

25 rows selected.

then i run my test (you are surely familiar with :-))

jom@NINA.US.ORACLE.COM> r
1 declare
2 type t_xmltypes is table of sys.xmltype index by binary_integer;
3 v_xml_res t_xmltypes;
4 cursor cur_res
5 is
6 select x , y from t;
7 type t_tab_str is table of VARCHAR2(128) index by binary_integer;
8 v_tab_str t_tab_str;
9 v_i integer default 0;
10 begin
11 runstats_pkg.rs_start;
12 for v_int in 1 .. 100
13 loop
14 select xmlelement("t",xmlattributes(x as "id"),xmlforest(y)) bulk collect into v_xml_res from t;
15 end loop;
16 runstats_pkg.rs_middle;
17 for v_int in 1 .. 100
18 loop

19 for rec_res in cur_res
20 loop
21 v_i := v_i +1;
22 v_tab_str(v_i) := '<t id="'||to_char(rec_res.x)||'" <y>'||rec_res.y||'</y></t>';
23 end loop;
24 v_i := 0;
25 end loop;
26 runstats_pkg.rs_stop(500);
27* end;
Run1 ran in 42 hsecs
Run2 ran in 7 hsecs
run 1 ran in 600% of the time

Name Run1 Run2 Diff
STAT...buffer is not pinned co 100 2,500 2,400
STAT...no work - consistent re 100 2,500 2,400
STAT...table scan blocks gotte 100 2,500 2,400
STAT...consistent gets 303 2,704 2,401
STAT...session logical reads 804 3,207 2,403
STAT...calls to get snapshot s 2,801 301 -2,500
STAT...recursive calls 102 2,702 2,600
LATCH.cache buffers chains 3,097 7,891 4,794
LATCH.library cache pin alloca 5,204 4 -5,200
LATCH.library cache pin 5,412 210 -5,202
LATCH.library cache 10,718 216 -10,502
LATCH.row cache enqueue latch 15,600 0 -15,600
LATCH.row cache objects 15,600 0 -15,600
STAT...session pga memory max 16,428 0 -16,428
STAT...session uga memory max 65,464 0 -65,464
STAT...session uga memory 196,392 0 -196,392
STAT...session pga memory 196,608 0 -196,608

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
56,670 8,936 -47,734 634.18%

PL/SQL procedure successfully completed.

adding 100 times more data

jom@NINA.US.ORACLE.COM> r
1 begin
2 for v_int in 26 .. 2500
3 loop
4 insert into t ( x, y ) values ( v_int , 'v'||to_char(v_int));
5 end loop;
6* end;

PL/SQL procedure successfully completed.

jom@NINA.US.ORACLE.COM> select count(1) from t;

COUNT(1)
----------
2500

jom@NINA.US.ORACLE.COM> declare
2 type t_xmltypes is table of sys.xmltype index by binary_integer;
3 v_xml_res t_xmltypes;
4 cursor cur_res
5 is
6 select x , y from t;
7 type t_tab_str is table of VARCHAR2(128) index by binary_integer;
8 v_tab_str t_tab_str;
9 v_i integer default 0;
10 begin
11 runstats_pkg.rs_start;
12 for v_int in 1 .. 100
13 loop
14 select xmlelement("t",xmlattributes(x as "id"),xmlforest(y)) bulk collect into v_xml_res from t;
15 end loop;
16 runstats_pkg.rs_middle;
17 for v_int in 1 .. 100
18 loop
19 for rec_res in cur_res
20 loop
21 v_i := v_i +1;
22 v_tab_str(v_i) := '<t id="'||to_char(rec_res.x)||'" <y>'||rec_res.y||'</y></t>';
23 end loop;
24 v_i := 0;
25 end loop;
26 runstats_pkg.rs_stop(500);
27 end;
28 /
Run1 ran in 6044 hsecs
Run2 ran in 582 hsecs
run 1 ran in 1038.49% of the time

Name Run1 Run2 Diff
LATCH.undo global data 513 5 -508
LATCH.enqueue hash chains 758 10 -748
LATCH.shared pool 1,040 103 -937
LATCH.checkpoint queue latch 1,054 96 -958
LATCH.SQL memory manager worka 1,350 134 -1,216
STAT...recursive cpu usage 5,842 370 -5,472
STAT...buffer is not pinned co 500 250,000 249,500
STAT...no work - consistent re 500 250,000 249,500
STAT...table scan blocks gotte 500 250,000 249,500
STAT...consistent gets 703 250,205 249,502
STAT...session logical reads 1,204 250,710 249,506
STAT...calls to get snapshot s 250,301 301 -250,000
STAT...recursive calls 102 250,202 250,100
LATCH.cache buffers chains 7,508 502,906 495,398
LATCH.library cache pin alloca 500,670 2 -500,668
LATCH.library cache pin 501,010 212 -500,798
LATCH.library cache 1,001,843 213-1,001,630
LATCH.row cache enqueue latch 1,500,758 0-1,500,758
LATCH.row cache objects 1,500,759 0-1,500,759
STAT...session uga memory max ########## 0##########
STAT...session uga memory ########## 0##########
STAT...session pga memory max ########## 65,712##########
STAT...session pga memory ########## 65,712##########

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
5,019,500 504,224-4,515,276 995.49%

PL/SQL procedure successfully completed.

or using dbms_xmlquery

jom@NINA.US.ORACLE.COM> declare
2 v_xml_handle dbms_xmlquery.ctxHandle;
3 v_clob clob;
4 cursor cur_res
5 is
6 select x , y from t;
7 type t_tab_str is table of VARCHAR2(128) index by binary_integer;
8 v_tab_str t_tab_str;
9 v_i integer default 0;
10 begin
11 runstats_pkg.rs_start;
12 for v_int in 1 .. 100
13 loop
14 v_xml_handle := dbms_xmlquery.newContext('select x,y from t');
15 dbms_lob.createtemporary(v_clob,true,dbms_lob.session);
16 v_clob := dbms_xmlquery.getXml(v_xml_handle);
17 end loop;
18 runstats_pkg.rs_middle;
19 for v_int in 1 .. 100
20 loop
21 for rec_res in cur_res
22 loop
23 v_i := v_i +1;
24 v_tab_str(v_i) := '<t id="'||to_char(rec_res.x)||'" <y>'||rec_res.y||'</y></t>';
25 end loop;
26 v_i := 0;
27 end loop;
28 runstats_pkg.rs_stop(500);
29 end;
30 /
Run1 ran in 2329 hsecs
Run2 ran in 577 hsecs
run 1 ran in 403.64% of the time

Name Run1 Run2 Diff
STAT...consistent gets - exami 603 5 -598
STAT...dirty buffers inspected 1,179 0 -1,179
STAT...free buffer inspected 1,179 0 -1,179
STAT...calls to get snapshot s 2,001 301 -1,700
STAT...free buffer requested 2,107 10 -2,097
LATCH.checkpoint queue latch 3,016 123 -2,893
LATCH.cache buffers lru chain 4,481 39 -4,442
STAT...consistent changes 5,889 495 -5,394
STAT...db block changes 6,380 986 -5,394
STAT...db block gets 19,782 505 -19,277
STAT...session uga memory 65,464 0 -65,464
STAT...session logical reads 46,185 250,710 204,525
STAT...buffer is not pinned co 26,200 250,000 223,800
STAT...consistent gets 26,403 250,205 223,802
STAT...recursive calls 26,003 250,202 224,199
STAT...no work - consistent re 25,600 250,000 224,400
STAT...table scan blocks gotte 25,400 250,000 224,600
LATCH.cache buffers chains 108,516 504,359 395,843
STAT...session pga memory 6,356,992 -131,072-6,488,064

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
119,617 508,510 388,893 23.52%

PL/SQL procedure successfully completed.

latches look good, but time not ...

it looks like oracle-xml-features are much slower
is there
really so much overheader in it?
or
am i using it in the wrong way (and yould be tuned to better just by doing it right)?
or
is the test i run so wrong and using runstats is not a good test in this case?

regards

hannibal

Tom Kyte
January 12, 2004 - 1:34 pm UTC

the xml stuff has lots more functionality than a simple string -- the xmltype is a complex data type with "lots of stuff"

if you have no need beyond the trivial xml snippet you have, a string will be fine. else, xmltype has the stuff you "need"

okay...

Mark Griffin, January 14, 2004 - 4:31 pm UTC

Well I guess I will have to attempt to use
combination of DBMS_XMLDOM & other database packages to accomplish my goal.

I'm going to have to still muddle through the mapping though even with use of DBMS_XMLDOM.

I have another question is there a way once you have document pulled in and parsed via DBMS_XMLDOM to then insert data elements into some type of object(whether table, collection...)

If I can go this route then I can manipulate the mapping via Oracle relation logical perspective...



hello again

mark griffin, January 15, 2004 - 11:52 am UTC

thank you both sean and tom for your patience, wisdom, and help...

i've brainstormed my scenario over and over again...

could i do this:
- take xsd and do
BEGIN
dbms_xmlschema.registerURI(
'cd.xsd',
'</code> http://gennick.com/cd.xsd' <code>;
END;

- put my xsd file into HTTP/WebDAV Protocol Server

- then load xsd into structures that were created by register command

- after xsd is loaded then pull data based upon data queried from our database ?

will this work?

thank you!






Tom Kyte
January 15, 2004 - 1:18 pm UTC

Mark,

After you register the schema (dbms_xmlschema.registerURI), the Schema has been loaded and the underlying storage architecture (the object/relational tables) will have been created.

I think you're a little confused about the "put my xsd file into ... server" comment. You don't store your XSD (XML Schema), you store your XML. The XML you'll be storing references the XML Schema. The XML is the DATA DOCUMENT, the XSD is the DOCUMENT STRUCTURE. The XSD is registered with the database. The XML is loaded into the database and stored in o/r tables).

So.... with that in mind, You can then use the HTTP/WebDAV protocol to load **XML** documents into the database (btw, there is no HTTP/WebDAV Protocol Server, there is the Oracle Net Services Listener, which is now capable of communicating over HTTP (and therefore WebDAV) and FTP connections). Behind the scenes, YES these XML documents are stored in the object/relational tables which were created as a result of the registerURI call.

Once the XML has been stored in these object/relational tables, YES you can query the object/relational tables in SQL queries, or you can pull the documents as complete XML using HTTP/WebDAV, FTP and even SQL/PLSQL if you so desire!

Ultimately, yes it will work. You just need to get the XSD vs XML terminology straightened out ;-).

Good luck!

_smd_
Once the

lot of help

mark griffin, January 15, 2004 - 11:39 pm UTC

yeah depending on who you talk to you get a different defintion of terms...thank you for your clarification between XSD and XML

it seems to me that one of the first thing(s) that people would have come up with is a mechanism to allow fluent mapping between xsd elements...maybe im just a dreamer though and because its the most important thing that i need :)

i just dont see how people at this point based upon xsd can effectively pull data from their system to give to another system in a dynamic sense with the available tools. Even if one writes custom code how much of a dynamic process can one create? maybe this is where my lack of knowledge/ignorance comes into play...also maybe this is where the tools have to grow up/mature.




XMLType.genXMLschema?

orabin, June 12, 2004 - 9:59 am UTC

Whether it is possible to generate automatically the text annotated XMLschema on the basis of instance XMLType received by SQLX of functions? In fact there is a method genDTD (), should be and a method genXMLschema ().. In the documentation has not found a good example, as on the basis of such annotated and registered XMLschema and instance XMLType, AUTOMATICALLY to scatter ("insert/update") xml-data under target relational tables?

Generating the ?xml header tag

Martin, July 26, 2004 - 6:12 am UTC

Might sound a simple question this, but when generating XML from SQL using SQLX (i.e. the XMLElement functions etc.), you don't get the <?xml version="1.0"?>, unlike generating using DBMS_XMLQuery etc. Is this possible?

Thanks in advance

Tom Kyte
July 26, 2004 - 7:32 am UTC

concatenation? just "add it"

Well... yes...

Martin, July 26, 2004 - 7:45 am UTC

I'd obviously thought of that! But, doing that to the output XMLTYPE generates a CLOB, and I was wondering if you could add the element, but without having to convert to CLOBs and back?

Thanks

Tom Kyte
July 26, 2004 - 8:00 am UTC

not that i am aware of.

Thanks

Martin, July 26, 2004 - 8:04 am UTC


XDK Memory Leak

Ark Furmansky, July 26, 2004 - 10:36 am UTC

Tom, Sean,

 the code below that you recommended works fine for a single insert.  Hovewer, executing newContext, insertXML, and closeContext in a loop presents a Java memory error.  This is true for all XDK packages utilizing newContext method in Oracle 8i,9i, and 10g.  In addition, the new  xmlStore package has another bug - too many open cursors.

SQL> declare
  2    l_ctx  dbms_xmlquery.ctxHandle;
  3    l_clob clob;
  4  begin
  5    l_ctx := dbms_xmlquery.newContext('select * from scott.emp');
  6    insert into xmldocs values ('EMP TABLE', empty_clob())
  7    returning xmldoc into l_clob;
  8    dbms_xmlquery.getXml(l_ctx, l_clob);
  9  end;
 10  /
 

Very Informative

M.S.Udankar, August 17, 2004 - 8:59 am UTC

"XML Generation/Manipulation using SQL", 

Dear Tom,

I have a situation which is exactly opposite to "XML Generation/Manipulation using SQL"

We have an application developed for a cart site which is in Java and Oracle. In the 

earlier version of our application when a person places an order through the site. The Java 

application collects all the data and forms an XML string which is stored in the database 

as a column.

SQL> desc order_formats
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_FORMAT_ID                           NOT NULL NUMBER(18)
 ORDER_ID                                  NOT NULL NUMBER(18)
 ORDER_TYPE_ID                             NOT NULL NUMBER(18)
 ORDER_XML                                 NOT NULL SYS.XMLTYPE <============
 STATUS_ID                                 NOT NULL NUMBER(18)
 CREATED_BY_ID                             NOT NULL NUMBER(18)
 CREATED_DATE                              NOT NULL DATE
 UPDATED_BY_ID                                      NUMBER(18)
 UPDATED_DATE                                       DATE

Now comes the real test .We are developing a new version of our software in which we are 

trying to get rid of the XML script in the database. We have designed various tables where 

the appropriate data will be stored - Not as a single XML script.

Now I have 2 jobs in hand

(1) First is to migrate the data in the database to various tables - Already placed orders

(2) The orders which are to be placed in future should also be inserted into various tables 

- Not as a single XML script in a table.

What I have planned is like this

There will be a parent procedure say P1

This will read the ORDER_ForMATS table,pick up the XML string and get all the leaf nodes.

Once all the leaf nodes are reached this P1 will call another procedure P2. 

This P2 will use the leafnodes of the XML script and form Oracle Object and return it to 

P1. 

Once again P1 will call another Procedure P3 which will receive the Object as parameter and 

insert the values in the appropriate tables.

The reason for myself planning in this way is this 

(1) P1 + P2 + P3 can be used for Datamigration 

(2) P3 will be used by the Java front end application to place the data in the appropriate 

tables of the orders to be placed

I am sending you the Object. The object which is at the last(named CXML) will be passed as 

parameter to the procedure P3. Which essentially means that when I read the XML script in 

P1 and reach the leaf nodes the same object has to be formed by P2 using the leaf nodes of 

XML.


Plz comment on the way I am I am proceeding. If there is anything I am missing out or I 

have err do let me know.

Expecting to hear from your end.


*******************************************************************************************

*******************************************************************************************
create or replace type VARR_STRING AS VARRAY(256) OF VARCHAR2(256);

 create or replace type OBJ_CREDENTIAL as object
(
    SEQUENCE_NO NUMBER(18),
    DOMAIN VARCHAR2(256),
    IDENTITY VARCHAR2(256),
    SHAREDSECRET VARCHAR2(256)
);


 create or replace TYPE VARR_CREDENTIALS AS VARRAY(256) OF OBJ_CREDENTIAL;

 create or replace type OBJ_PARTY as object
(
    NAME1 VARCHAR2(256),
    USERAGENT VARCHAR2(256),
    CREDENTIALS VARR_CREDENTIALS    
);

 create or replace type OBJ_HEADER as object
(
    SEQUENCE_NO NUMBER(18),
    FROM1 OBJ_PARTY,
    TO1 OBJ_PARTY,
    SENDER OBJ_PARTY
);

 create or replace type OBJ_NAME_VALUE as object
(
    SEQUENCE_NO NUMBER(18),
    NAME1 VARCHAR2(256),
    VALUE VARCHAR2(256)
);

 create or replace TYPE VARR_COMPONENTS AS VARRAY(256) OF OBJ_NAME_VALUE;



 create or replace type OBJ_BROWSERFORMPOST as object
(
    URL VARCHAR2(256)
);


 create or replace type OBJ_SUPPLIERSETUP  as object
(
    URL VARCHAR2(256)
);


 create or replace type OBJ_COUNTRY as object
(
    COUNTRYCODE VARCHAR2(256),
    COUNTRY VARCHAR2(256)
);


 create or replace type OBJ_POSTALADDRESS  as object
(
    SEQUENCE_NO NUMBER(18),
    COMPONENTS    VARR_COMPONENTS,
    NAME1       VARCHAR2(256),
    DELIVERTOS       VARR_STRING,
    STREETS       VARR_STRING,
    CITY       VARCHAR2(256),
    STATE       VARCHAR2(256),
    POSTALCODE       VARCHAR2(256),
    COUNTRY       OBJ_COUNTRY    
);


 create or replace type OBJ_EMAIL as object
(
    NAME1 VARCHAR2(256),
    EMAIL VARCHAR2(256)
);


 create or replace type OBJ_COUNTRYCODE as object
(
    ISOCOUNTRYCODE VARCHAR2(256),
    COUNTRYPREFIX VARCHAR2(256)
);


 create or replace type OBJ_TELEPHONENUMBER as object
(
    COUNTRYCODE       OBJ_COUNTRYCODE,
    AREAORCITYCODE       VARCHAR2(256),
    NUMBERID       VARCHAR2(256),
    EXTENSION       VARCHAR2(256)
);



 create or replace type OBJ_PHONE as object
(
    NAME1 VARCHAR2(256),
    TELEPHONENUMBER OBJ_TELEPHONENUMBER    
);


 create or replace type OBJ_FAX as object
(
    NAME1 VARCHAR2(256),
    TELEPHONENUMBER OBJ_TELEPHONENUMBER,
    URL VARCHAR2(256),
    EMAIL OBJ_EMAIL    
);



 create or replace type OBJ_ADDRESS  as object
(
    SEQUENCE_NO NUMBER(18),
    SHIPTO        VARCHAR2(256),
    BILLTO        VARCHAR2(256),
    CREDITCARD        VARCHAR2(256),
    TYPEID        VARCHAR2(256),
    ISOCOUNTRYCODE        VARCHAR2(256),
    ADDRESSID        VARCHAR2(256),
    XMLLANG        VARCHAR2(256),
    NAME1       VARCHAR2(256),
    POSTALADDRESS        OBJ_POSTALADDRESS ,
    EMAIL        OBJ_EMAIL ,
    PHONE        OBJ_PHONE ,
    FAX        OBJ_FAX,
    URL VARCHAR2(256)
);


 create or replace type OBJ_FREIGHTCALCULATOR as object
(
    FREIGHT                 OBJ_NAME_VALUE,
    FLAT_RATE               VARCHAR2(256),
    PERCENTAGE_RATE         VARCHAR2(256),
    UPSMANUFACTURERS        VARR_STRING,
    DIRECTMANUFACTURERS     VARR_STRING,
    GEOGRAPHYID             VARCHAR2(256),
    UNKNOWNID               VARCHAR2(256)    
);


 create or replace type OBJ_OTHERCHARGECALCULATOR as object
(
    OTHERCHARGE     OBJ_NAME_VALUE,
    DIRTYGROUPS VARR_STRING,
    UNKNOWNID VARCHAR2(256)
);


 create or replace type OBJ_TAXINGAPI as object
 (
     COMPANY     VARCHAR2(256),
     CANADA     VARCHAR2(256),
     CANADA_COUNTRY_CODE_2     VARCHAR2(256),
     CANADA_COUNTRY_CODE_3     VARCHAR2(256),
     UNITED_STATES     VARCHAR2(256),
     TAXEXEMPT VARCHAR2(256),
     TAXEXEMPT_PROVINCE     VARCHAR2(256),
     TAXABLE     VARCHAR2(256),
     FREIGHT_PRODUCT_CODE     VARCHAR2(256),
     HOSTNAME     VARCHAR2(256),
     LISTENER     VARCHAR2(256),
     SID     VARCHAR2(256),
     USERNAME     VARCHAR2(256),
     PASSWORD     VARCHAR2(256),
     SHIPFROMCITY VARCHAR2(256),
     SHIPFROMSTATE     VARCHAR2(256),
     SHIPFROMPOSTALCODE     VARCHAR2(256),
     SHIPFROMCOUNTY     VARCHAR2(256),
     ORDERACCEPTANCECITY     VARCHAR2(256),
     ORDERACCEPTANCESTATE     VARCHAR2(256),
     ORDERACCEPTANCEPOSTALCODE     VARCHAR2(256),
     ORDERACCEPTANCECOUNTY     VARCHAR2(256),
     LNGSHIPFROMGEOCODE     VARCHAR2(256),
     LNGORDERACCEPTANCEGEOCODE     VARCHAR2(256),
     SHIPTOGEOCODE     VARCHAR2(256),
     SHIPFROMGEOCODE     VARCHAR2(256),
     ORDERACCEPTANCEGEOCODE     VARCHAR2(256)
);

 create or replace type OBJ_SUPPLIERPARTAUXILIARYID AS OBJECT
(
    COMPONENTS    VARR_COMPONENTS,
    TYPE VARCHAR2(256),
    VALUE VARCHAR2(256),
    ENTEREDINUMBER VARCHAR2(256),
    SUPPLIERPRODUCTID VARCHAR2(256),
    CATALOGPRODUCTID VARCHAR2(256),
    SUPPLIERID VARCHAR2(256),
    SSIORDERNO VARCHAR2(256)      
);


 create or replace type OBJ_ITEMID AS OBJECT
(
    SEQUENCE_NO NUMBER(18),
    SUPPLIERPARTID VARCHAR2(256),
    SUPPLIERPARTAUXILIARYID OBJ_SUPPLIERPARTAUXILIARYID
    
);


 create or replace type OBJ_CLASSIFICATION AS OBJECT
(
    DOMAIN VARCHAR2(256),
    CLASSIFICATION VARCHAR2(256)
);


 create or replace TYPE VARR_CLASSIFICATIONS AS VARRAY(256) OF OBJ_CLASSIFICATION;

create or replace type OBJ_DESCRIPTION as object
(
    XMLLANG     VARCHAR2(256),
    SHORTNAME     VARCHAR2(256),
    DESCRIPTION     VARCHAR2(256)
);

 create or replace type OBJ_MONEY as object
(
    SEQUENCE_NO NUMBER(18),
    NAME1     VARCHAR2(256),
    AMOUNT VARCHAR2(256),
    CURRENCY     VARCHAR2(256),
    ALTERNATEAMOUNT     VARCHAR2(256),
    ALTERNATECURRENCY     VARCHAR2(256)
);


 create or replace type OBJ_ITEMDETAIL AS OBJECT
(
    COMPONENTS    VARR_COMPONENTS,
    UNITPRICE OBJ_MONEY,
    DESCRIPTION OBJ_DESCRIPTION,
    UNITOFPRICE VARCHAR2(256),
    CLASSIFICATIONS VARR_CLASSIFICATIONS,
    MANUFACTERERPARTID VARCHAR2(256),
    MANUFACTURERNAME VARCHAR2(256),
    URL VARCHAR2(256),
    EXTRINSICS VARR_COMPONENTS 
);


 create or replace type OBJ_SEGMENT as object
(
    TYPE VARCHAR2(256),
    ID VARCHAR2(256),
    DESCRIPTION VARCHAR2(256)
);


 create or replace type OBJ_ACCOUNTINGSEGMENT as object
(
    TYPE VARCHAR2(256),
    ID VARCHAR2(256),
    DESCRIPTION VARCHAR2(256)    
);



 create or replace TYPE VARR_SEGMENTS AS VARRAY(256) OF OBJ_SEGMENT;


 create or replace TYPE VARR_ACCOUNTINGSEGMENTS AS VARRAY(256) OF OBJ_ACCOUNTINGSEGMENT;


 create or replace type OBJ_ACCOUNTING as object
(
    SEQUENCE_NO NUMBER(18),
    NAME1 VARCHAR2(256),
    SEGMENTS VARR_SEGMENTS,
    ACCOUNTINGSEGMENTS VARR_ACCOUNTINGSEGMENTS
);


 create or replace type OBJ_DISTRIBUTION as object
(
    SEQUENCE_NO NUMBER(18),
    ACCOUNTING OBJ_ACCOUNTING,
    MONEY OBJ_MONEY
);


 create or replace TYPE VARR_DISTRIBUTIONS AS VARRAY(256) OF OBJ_DISTRIBUTION;


 create or replace type OBJ_ITEMOUT as object
(
    SEQUENCE_NO NUMBER(18),
    COMPONENTS    VARR_COMPONENTS,
    TAXDIRTY VARCHAR2(256),
    QUANTITY VARCHAR2(256),
    LINENUMBER VARCHAR2(256),
    REQUISTIONID VARCHAR2(256),
    AGREEMENTITEMNUMBER VARCHAR2(256),
    REQUESTEDDELIVERYDATE VARCHAR2(256),
    ITEMID OBJ_ITEMID,
    ITEMDETAIL OBJ_ITEMDETAIL,
    SHIPTO     OBJ_ADDRESS,
    SHIPPING OBJ_SHIPPING,
    TAX OBJ_TAX,
    DISTRIBUTIONS VARR_DISTRIBUTIONS,
    COMMENTS VARCHAR2(256)
    
);



 create or replace type OBJ_ITEMSOUT as object
(
    LINENUMBER               VARCHAR2(256),
    ITEMOUT    VARR_ITEMOUT
);


 create or replace TYPE VARR_ITEMOUT AS VARRAY(256) OF OBJ_ITEMOUT;

 create or replace type OBJ_PUNCHOUTSETUPREQUEST as object
(
    OPERATION               VARCHAR2(256),
    BUYERCOOKIE               VARCHAR2(256),
    BROWSERFORMPOST               OBJ_BROWSERFORMPOST ,
    SUPPLIERSETUP               OBJ_SUPPLIERSETUP ,
    ADDRESS         OBJ_ADDRESS,
    ITEMSOUT               OBJ_ITEMSOUT,
    FLAT_RATE               VARCHAR2(256),
    PERCENTAGE_RATE               VARCHAR2(256),
    UPSMANUFACTURERS               VARR_STRING,
    DIRECTMANUFACTURERS               VARR_STRING,
    GEOGRAPHYID               VARCHAR2(256),
    UNKNOWNID VARCHAR2(256)    
);



 create or replace type OBJ_SHIPPING as object
(
    SEQUENCE_NO NUMBER(18),
    COMPONENTS    VARR_COMPONENTS,
    TOTAL     OBJ_MONEY,
    DESCRIPTION     OBJ_DESCRIPTION
);


 create or replace type OBJ_TAX as object
(
    SEQUENCE_NO NUMBER(18),
    COMPONENTS    VARR_COMPONENTS,
    TOTAL     OBJ_MONEY,
    DESCRIPTION     OBJ_DESCRIPTION
);


 create or replace type OBJ_PCARD as object
(
    COMPONENTS    VARR_COMPONENTS,
    POSTALADDRESS OBJ_POSTALADDRESS,
    NUMBER1   VARCHAR2(256),
    EXPMONTH   VARCHAR2(256),
    EXPYEAR   VARCHAR2(256)
);


 create or replace type OBJ_PAYMENT as object
(
    COMPONENTS    VARR_COMPONENTS,    
    PCARD   OBJ_PCARD
);



 create or replace type OBJ_ORDERREQUESTHEADER as object
(
    EARLIEST_DELIVER_DATE     VARCHAR2(256),
    LATEST_DELIVER_DATE     VARCHAR2(256),
    COMPONENTS    VARR_COMPONENTS,
    ORDERID     NUMBER(18),
    COMMENTS     VARCHAR2(256),
    ORDERDATE     VARCHAR2(256),
    TYPE1     VARCHAR2(256),
    TOTAL     OBJ_MONEY,
    SHIPTO     OBJ_ADDRESS,
    BILLTO     OBJ_ADDRESS,
    SHIPPING     OBJ_SHIPPING,
    TAX     OBJ_TAX,
    PAYMENT     OBJ_PAYMENT,
    EXTRINSICS     VARR_COMPONENTS
);



 create or replace type OBJ_ORDERREQUEST as object
(
    COMPONENTS    VARR_COMPONENTS,
    ORDERREQUESTHEADER  OBJ_ORDERREQUESTHEADER,
    ITEMSOUT               VARR_ITEMOUT
);



 create or replace type OBJ_REQUEST as object
(
    COMPONENTS    VARR_COMPONENTS,
    PUNCHOUTSETUPREQUEST       OBJ_PUNCHOUTSETUPREQUEST,
    ORDERREQUEST               OBJ_ORDERREQUEST    
);


 create or replace type OBJ_PUNCHOUTORDERMESSAGEHEADER as object
(
    OPERATIONALLOWED   VARCHAR2(256),
    TOTAL     OBJ_MONEY,
    ADDRESS         OBJ_ADDRESS,    
    TAX     OBJ_TAX
);


 create or replace type OBJ_ITEMIN as object
(
    QUANTITY VARCHAR2(256),
    LINENUMBER VARCHAR2(256),
    ITEMID OBJ_ITEMID,
    ITEMDETAIL OBJ_ITEMDETAIL,
    SHIPTO     OBJ_ADDRESS,
    SHIPPING OBJ_SHIPPING,
    TAX OBJ_TAX    
);

create or replace TYPE VARR_ITEMIN AS VARRAY(256) OF OBJ_ITEMIN;

 create or replace type OBJ_ITEMSIN as object
(
    LINENUMBER               VARCHAR2(256),
    ITEMIN    VARR_ITEMIN
);



 create or replace type OBJ_PUNCHOUTORDERMESSAGE as object
(
    COMPONENTS    VARR_COMPONENTS,
    BUYERCOOKIE   VARCHAR2(256),
    PUNCHOUTORDERMESSAGEHEADER   OBJ_PUNCHOUTORDERMESSAGEHEADER,
    ITEMSIN               OBJ_ITEMSIN    
);


 create or replace type OBJ_MESSAGE as object
(
    PUNCHOUTORDERMESSAGE   OBJ_PUNCHOUTORDERMESSAGE
);



 create or replace type OBJ_STATUS as object
(
    CODE   VARCHAR2(256),
    TEXT   VARCHAR2(256)
);


 create or replace type OBJ_STARTPAGE as object
(
    URL   VARCHAR2(256)
);


 create or replace type OBJ_PUNCHOUTSETUPRESPONSE as object
(
    STARTPAGE   OBJ_STARTPAGE
);


 create or replace type OBJ_RESPONSE as object
(
    STATUS   OBJ_STATUS,
    PUNCHOUTSETUPRESPONSE   OBJ_PUNCHOUTSETUPRESPONSE
);

create or replace type OBJ_AUDIT as object
(
    STATUS_ID VARCHAR2(256),
    CREATED_BY_ID VARCHAR2(256),
    CREATED_DATE VARCHAR2(256),
    UPDATED_BY_ID VARCHAR2(256),
    UPDATED_DATE VARCHAR2(256)
);
 create or replace type CXML as object
(
    HEADER OBJ_HEADER,
    REQUEST OBJ_REQUEST,
    MESSAGE OBJ_MESSAGE,
    RESPONSE OBJ_RESPONSE,
    VERSION VARCHAR2(256),
    PAYLOADID VARCHAR2(256),
    TIMESTAMP VARCHAR2(256),
    XMLLANG VARCHAR2(256),
    FORAUDIT OBJ_AUDIT
);


 

A reader, August 21, 2004 - 6:08 am UTC

Hi,
  1)In your following example if I have a nested element and want to store them in two seprate table ,is it possible ??If yes how do I do that.For an example EMP details in myemp table and kid details in kid table.Also there should be some link between this two tables to know the kid belongs to perticular emp.

2)If I want to follow schema method,meaning register a schema ,which inturn should create a structure to hold this nested structure (As you have shown in your oramag article)and load this xml into  that table.Can you give me
one example schema (with all annotation) to hold this type of nested data.I went thro your oramag article,but couldn't find .xsd file.



SQL> declare
  2    l_clob  clob :=
  3  '<?xml version = "1.0"?>
  4  <ROWSET>
  5     <ROW num="1">
  6        <EMPNO>7369</EMPNO>
  7        <ENAME>SEANDILLON</ENAME>
  8        <JOB>DBDUDE</JOB>
  9        <MGR>7902</MGR>
 10        <HIREDATE>12/17/1980 0:0:0</HIREDATE>
 11        <SAL>800</SAL>
 12        <DEPTNO>20</DEPTNO>
                  <KID>
                     <NAME>X</NAME>
                     <AGE>10</AGE>
                  </KID>
 13     </ROW>
 34  </ROWSET>';
 35  
 36    l_ctx   dbms_xmlsave.ctxType;
 37    l_rows  number;
 38  begin
 39    l_ctx := dbms_xmlsave.newContext('MYEMP');
 40    l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
 41    dbms_xmlsave.closeContext(l_ctx);
 42    dbms_output.put_line(l_rows || ' rows inserted...');
 43  end insert_xml_emps;

cheers 

Tom Kyte
September 01, 2004 - 6:47 am UTC

I asked Sean Dillon to take a look at this, here's what he had to say:
-------

You have three options to accomplish what you're trying to do. The first option (and probably the long-had way to do it), parse the XML document yourself and do the inserts manually. This is using a DOM or SAX parser, which Oracle fully supports (PL/SQL, C, C++, Java, etc.) in the XML Developer's Kits. See the XML Technology Center on OTN @ </code> http://otn.oracle.com/tech/xml

Another way to do it, while still using DBMS_XMLSAVE is to store your XML into an OBJECT-relational table.  This means you can have some semblence of a nested structure in the source XML document you're using (some caveats here, see the documentation link below), and the PL/SQL package DBMS_XMLSAVE (and the Java class OracleXMLSave I think, for that matter) can use a nested XML document to store the data into an object in a table.  You could choose to read that object on an insert trigger and store the data in tables with a parent-child relationship.

Look in chapter seven of the XML Developer's Kit Programmers Guide for information on "Storing XML in the Database Using DBMS_XMLSave".

Finally, you could use XML DB to perform this serialization for you as well, but not only is this probably overkill for what you're trying to do, you wouldn't be able to store the XML into YOUR relational tables.  Oracle would create NEW tables to store the XML content for you based on an XML Schema (which is mandatory for this type of data storage).  XML DB's XML Schema-based structured storage is incredibly powerful, and it's definitely something I recommend, so you may want to read a chapter or two of the XML DB Developer's Guide about structured storage in XML DB to see the benefits it provides.

Here's a good article by Jonathon Gennick about XML DB : 
https://asktom.oracle.com/Misc/oramag/on-measuring-distance-and-aging.html

Here's the XML Developer's Kit Programmers Guide on OTN :
http://www.oracle.com/pls/db10g/db10g.to_toc?pathname=appdev.101%2Fb10794%2Ftoc.htm&remark=portal+%28Books%29

Here's the XML DB Developer's Guide on OTN :
http://www.oracle.com/pls/db10g/db10g.to_toc?pathname=appdev.101%2Fb10790%2Ftoc.htm&remark=portal+%28Books%29 <code>

Hope that helps!

I get an error

A reader, August 27, 2004 - 1:30 pm UTC

This is an error I get ERROR at line 1:
ORA-06550: line 3, column 5:
PLS-00382: expression is of wrong type
ORA-06550: line 2, column 15:
PL/SQL: Item ignored
ORA-06550: line 39, column 44:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 39, column 5:
PL/SQL: Statement ignored
my oracle version is 8.1.7

Tom Kyte
August 27, 2004 - 1:38 pm UTC

you have 817, clobs were not treated as strings in that release, that was a new 9i feature way back in the day.

you would have to use dbms_lob to allocate a lob, write to a lob and so on.



Lola, August 30, 2004 - 3:12 pm UTC

Yes We are right as usual
I have a problem now
I Have a xml which come from the user input as two records from two diffeerent tables
and I need to update two different table
Is any way I can read the xlm and update the respective table?
Thanks


Tom Kyte
August 30, 2004 - 3:19 pm UTC

the question is "how many ways would you like to do this..."

suggest you check out the xml developers guide for an overview.

lola, August 30, 2004 - 4:11 pm UTC

Did I miss the word pl/sql
Sorry about that!
But as you saw there is a way.I could not find a plsql example even in you forum.

Thanks anyway
Much Appreaciated

Tom Kyte
August 30, 2004 - 5:32 pm UTC

</code> http://www.oracle.com/pls/db92/db92.docindex?remark=homepage#index-XML <code>

there are tons of plsql examples in there -- much of the XML XDK is exposed in plsql.

DBMS_XMLSAVE

CG, June 08, 2005 - 4:02 pm UTC

I understand your examples of taking an XML document and
inserting it into a relational table using DBMS_XMLSAVE.

My question is does DBMS_XMLSAVE parse the document using the DOM parser?

If so is there a way to use SAX parser instead? Seems like there will be memory issues if I use DOM.

Im on 9.2.0.6.0.

Thank you for your time

Java (Jdbc) and the oracle.xdb.XMLType class

Lucia, June 17, 2005 - 9:28 am UTC

Hi Tom,
I am trying to update(insert/update/delete) the element of a xlm document stored in a xmltype column.
I would like to use Java (Jdbc) and the oracle.xdb.XMLType class.
I have found an example in the Oracle9i XML Database Developer's guide - Oracle Xml DB, release 2 (9.2.0.2).
Java DOM API for XMLType Example 9-6.
I don't Know What is the Jar for oracle.xdb.XMLType.
In the example is named the oraxdb.jar but I haven't found it.
Is oraxdb.jar the jar for oracle.xdb.XMLType class?
Where is oraxdb.jar?
Can I download it?
Thank you for your help.

Tom Kyte
June 17, 2005 - 3:39 pm UTC

$ORACLE_HOME/rdbms/jlib/xdb.jar

A reader, August 01, 2005 - 4:17 pm UTC

Hi Tom,
We have a requirment where I have to update xml message which is being stored in xmltype data type field.

Could you please explain me giving a small example?

Thanks a lot.



Tom Kyte
August 01, 2005 - 8:39 pm UTC

well, it is just a column? update it?

How did the insert happened?

reader, August 02, 2005 - 5:05 am UTC

Dear Tom:
Thank you for helping and supporting, Can you please explain how did the insert statment work in the following example?
SQL> declare
  2    l_ctx  dbms_xmlquery.ctxHandle;
  3    l_clob clob;
  4  begin
  5    l_ctx := dbms_xmlquery.newContext('select * from scott.emp');
  6    insert into xmldocs values ('EMP TABLE', empty_clob())
  7    returning xmldoc into l_clob;
  8    dbms_xmlquery.getXml(l_ctx, l_clob);
  9  end;
 10  /
how the value is inserted into XMLDOC column?
 

Tom Kyte
August 02, 2005 - 7:44 am UTC

lobs are like pointers, when you write to a lob, it is writing it into the lob pointer.

just like you might use dbms_lob.writeAppend() to add to the end of lob -- not an update.


Think of a lob like an open file, you write to the file.

A reader, August 02, 2005 - 4:14 pm UTC

Hi Tom,

Thanks for the reply but my requirment on update is different. I just want to update element value .

<ename>100</ename> update to <ename>200</ename>.

Thanks




Tom Kyte
August 02, 2005 - 4:38 pm UTC

unless you shred the xml on the way in (and store it in rows and columns), there is just the document, you update the document.

How did the insert happened?

reader, August 02, 2005 - 8:33 pm UTC

Dear Tom:
I am sorry, but I still don't get it, how did the
6 insert into xmldocs values ('EMP TABLE', empty_clob())
7 returning xmldoc into l_clob;
8 dbms_xmlquery.getXml(l_ctx, l_clob);
modify the empty_clob() to a value? is it just a special case when dealing with LOBs?

Thank you.


Tom Kyte
August 03, 2005 - 9:59 am UTC

empty_clob() is a function that returns a pointer to an allocated clob that doesn't have anything in it yet.

then getxml was passed that pointer and wrote into it.

A reader, August 03, 2005 - 11:31 am UTC

Hi Tom,
I am able to update specific element in xml document. Cheked oracle doc. and found updatexml function. It is working fine.


update temp_xml
set tmp_xml = updateXML(tmp_xml,'/SiebelMessage/ListOfMhOrderLineItems/LineItems/OrderNumber/text()','797026')
where X_ID = 123

Thanks




reading attributes in 9i release 1

shyam singh, August 17, 2005 - 8:56 am UTC

Hi Tom,

its nice to have such a comperehensive help on oracle xml.
but my still unable to answer out my question. My need is to read attributes from xml into table in oracle 9i release 1.

Also I want to know is it possible to download install xmldb in 9i release 1, some how?

please help me.

thanks
shyam

Tom Kyte
August 17, 2005 - 1:45 pm UTC

xmldb is shipped with the database, the functionality is installed by using dbca. you do not "download" it, it is part of the database itself.


have you checked out available documentation for 9iR1?

</code> http://docs.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a88899/toc.htm http://docs.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a88894/toc.htm <code>

A reader

A, September 01, 2005 - 8:50 am UTC

Hi,
My </code> http://localhost:8080/public/ <code>is empty.How do I save a xml document over here.

cheers


Tom Kyte
September 01, 2005 - 3:37 pm UTC

?

processing each row

Phil, September 21, 2005 - 7:35 am UTC

Hi Tom/Sean

I have a function that returns me something like this

<ROWSET>
<ROW num="1">
<DEPTNO>10</DEPTNO>
<DNAME>SALES</DNAME>
<EMPLOYEE>
<EMPNO>100</EMPNO>
<ENAME>MARK JOHNSON</ENAME>
</EMPLOYEE>
<EMPLOYEE>
<EMPNO>200</EMPNO>
<ENAME>VICTOR JAEN</ENAME>
</EMPLOYEE>
<EMPLOYEE>
<EMPNO>300</EMPNO>
<ENAME>JHON SMITH</ENAME>
</EMPLOYEE>
</ROW>
</ROWSET>


And then I need to process each ROW, calling a stored procedure. What it the easiest way to approach this in 10.1 please?

Tom Kyte
September 21, 2005 - 7:20 pm UTC

there are many ways, you'll want to browse the XML documentation available here:

</code> http://otn.oracle.com/pls/db10g/portal.portal_demo3?selected=3#index-XML <code>

You can simply use an xmltype variable for example.

Registering XML Schema

Notna, October 01, 2005 - 12:21 pm UTC

Hi,

Is there any chance we can reference the XSD's outside the database, on a file system for example?

Thanks and Regards,
NOTNA

How about changing the structure of an xml document?

Dave Hemming, October 03, 2005 - 11:45 am UTC

I get an xml document inserted by a 3rd party application into an XMLTYPE column on a table. I use extract to validate various values and, if it passes, use the extracted values to update a table.

They then want a confirmation document passed back, with the same structure but with an extra field <RESULT></RESULT> which would contain 'OK', or an error message, depending.

Is there a simple way to 'add' a field to an existing XMLTYPE, or would I have to .extract() all the values and rebuild the document with the extra field?

I wondered if updateXML would do the job, but it seems to only work on existing fields.

Tom Kyte
October 03, 2005 - 8:33 pm UTC

they really want the entire document back? that seems "madness" or a scheme by a network provider....??????

really? not just the key and the "OK" bit?

Yep. The whole kit and caboodle.

Dave Hemming, October 04, 2005 - 4:37 am UTC

Believe me I've tried - my first choice was a generic xml type just for errors. But no dice.

However my vociferous objections seems to have thrown the whole thing back into discussions with the third party, so yay!

I'd still be interested to know if there's a way to add a node to an existing XMLtype, though...

Tom Kyte
October 04, 2005 - 3:41 pm UTC

I'm not sure, I'd ask around if it came down to it..

XML to oracle

Tracy, November 13, 2005 - 4:45 pm UTC

Tom
We have a front end application that sends the filtering criterion to the back-end(oracle) in XML format.I have to interpret that and open a sql using ref cursor..Can you give me an example of how to do that?
Thanks
Tracy

Tom Kyte
November 13, 2005 - 5:14 pm UTC

you'll need to (unfortunately) parse XML and get good old fashioned scalar parameters back out

bummer that PARAMETERS have become some "1990's" anymore - seems like we could still use them from time to time :( XML is good for somethings, to pass parameters from subroutine to subroutine is decidely NOT one of them.

Best I can do is point you to the XML docs so you can see what kind of parsing options you have. I will guess you will spend more resources building the query than you do executing it (unfortunately, end users don't care about the former, only the latter output..)


</code> http://www.oracle.com/pls/db102/portal.portal_db?selected=3#index-XML <code>

Tracy, November 16, 2005 - 8:26 am UTC

Tom
Thanks for the reply...i would really appreciate if you can illustarte with a small/concise working example
wherein you receive a XML parameter and parsing it and using it in a procedure to open a sql by ref cursor...please
Thanks for your inputs
Tracy

Tom Kyte
November 16, 2005 - 5:35 pm UTC

I don't have one - I would never do this, it is a bad idea.

parameters rock and roll.


but there are lots of examples of parsing xml in the documentation (and the ref cursor part isn't anything fancy, once you parse the xml, you'll have the scalars - which should have been PARAMETERS - in plsql variables and just

open ref cursor for select .... where column = plsql_variable......;



Best way to substitute strings with strings ...

VKOUL, January 23, 2006 - 9:49 pm UTC

Hi Tom,

What is the best way to substitute strings in place of strings ?

Example :

I have a string stored into the database column as '<Dummy1>$1<Dummy2>$2<Dummy3>$3', and I have 'value1||value2||value3' or 'value1,value2,value3' fed into a stored proc as a single parameter.

My desired output is '<Dummy1>value1<Dummy2>value2<Dummy3>value3'.

Thanks

Tom Kyte
January 23, 2006 - 11:16 pm UTC

replace()

you'll have to parse out the value1...valueN values in a loop and replace them

Changing the Structure of an XML Document

Claude, January 27, 2006 - 2:10 pm UTC

To followup on Dave from the UK's question about changing the structure. I would think this would work. Correct me if I'm wrong.

create or replace function f_replace (p_xmltype in xmltype)
return xmltype is
begin
return XMLType.createXML('<result status="ok">'||
p_xmltype.getStringVal()||
'</result>');
end;
/


Insert XML-Documents in two tables

A reader, March 23, 2006 - 7:38 am UTC

Hi

I have XML-Documents with one Element of General Information about the data delivery and up to several hundred (will become even several thousands) of "records" in the document. Because our initial application ant't handle that many records, we want to get the information into oracle (with xml db, I think that's the way to do it), somehow split the information into two tables (header and records) and read it out "record-wise", to go on working with it in our application. I get more and more information, but somehow I seem to be swimming in the large amount of possibilities.
Could you give me some hints about:
- how to register the xml-scheme(s)
- how to insert many documents, from file-system
- how to get the data out of the database to import it into the application

I would be glad to get any help... Thanks in advance...

Insert XML-Documents in two tables (the 2nd)

A reader, April 04, 2006 - 9:53 am UTC

My Document looks somehow like that:


<Document>
<Header>
<headerfield1> ... </headerfield1>
<headerfield2> ... </headerfield2>
</Header>
<Footer>
<footer-record>
<footerfield1> ... </footerfield1>
<footerfield2> ... </footerfield2>
....
</footer-record>
<footer-record>
<footerfield1> ... </footerfield1>
<footerfield2> ... </footerfield2>
....
</footer-record>
<footer-record>
<footerfield1> ... </footerfield1>
<footerfield2> ... </footerfield2>
....
</footer-record>
....
</Footer>
</Document>



Do you have an idea, how to get that into a database? Can I use dbms_xmlstore.insert_xml???

A related xml issue.

Richard Z. Tan, May 11, 2006 - 11:14 am UTC

Hi Mr. Kyte,

Thank you very much for your previous help. I sincerely appreciate it.

Now, I have xml doc with multiple layers, I am using dbms_xmlquery and pass in the sql string, in order to generate perfect correlations between multiple layers, I use CURSOR functions in the sql string, which have achieved the right correlations. All xml tags and data are perfect.

Without using xslt, as you know, the result shows like this
<ROWSET>
<ROW>
<LEVEL_1_ROW>
<LEVEL_1>
…
<LEVEL_2_ROW>
<LEVEL_2>
…
<LEVEL_3_ROW>
<LEVEL_3>
…
<LEVEL_4_ROW>
<LEVEL_4>
The result is perfect, except
1. replace <ROWSET> with <TOP_HEADER>
-- DBMS_XMLQuery.setRowsetTag(l_ctx, 'TOP_HEADER');
Probably, OK here.
2. get rid of <ROW>
-- DBMS_XMLQuery.setRowTag(l_ctx, '');
Probably, OK here.
3. get rid of <LEVEL_1_ROW>,<LEVEL_2_ROW>,<LEVEL_3_ROW>,<LEVEL_4_ROW>, These elements are extra.

The problem is:

There is no Oracle function to let me get of these extra elements, by default, when you use cursor function, these four elements are generated automatically if I use cursor alias, which I have to use anyway.

Please advice how to get rid of those four elements.

Thanks for help.

Richard Z. Tan



DBMS_XMLStore() and bind variables

Doug, May 15, 2006 - 3:35 pm UTC

An undocumented (as far as I could find) side-effect of calling DBMS_XMLStore.setUpdateColumn() is that a subsequent DBMS_XMLStore.insertXML() will use bind variables instead of literals! I'm not sure why it was implemented this way, but maybe there was a good reason. Anyway, here's the research behind my claim:

select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.1.0.3.0 - 64bit Production
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production

CREATE TABLE t (name VARCHAR2(100));

ALTER SESSION SET SQL_TRACE=TRUE;

DECLARE
l_xml CLOB;
l_ins_ctx DBMS_XMLStore.ctxType;
l_tname VARCHAR2(40);
l_row_count NUMERIC;

BEGIN
l_tname := 'T';
l_xml := '<ROWSET><ROW NUM="1"><NAME>' || TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSXFF') || '</NAME></ROW></ROWSET>';

DBMS_OUTPUT.PUT_LINE('l_xml=' || l_xml);

l_ins_ctx := DBMS_XMLStore.newContext(l_tname);
--> DBMS_XMLStore.setUpdateColumn(l_ins_ctx,'NAME'); <--
l_row_count := DBMS_XMLStore.insertXML(l_ins_ctx, l_xml);

DBMS_OUTPUT.PUT_LINE('l_row_count=' || l_row_count);
END;
/

I ran the above a few times with the highlighted line in, and then a few times with it commented out, then disconnected and did:

grep -h 'INSERT INTO T ' /path/to/udump/*.trc

INSERT INTO T ("NAME") VALUES (:1)
INSERT INTO T ("NAME") VALUES (:1)
INSERT INTO T ("NAME") VALUES (:1)
INSERT INTO T ("NAME") VALUES (:1)
INSERT INTO T ("NAME") VALUES (:1)
INSERT INTO T ("NAME") VALUES ('20060515132857.577137000')
INSERT INTO T ("NAME") VALUES ('20060515132858.615985000')
INSERT INTO T ("NAME") VALUES ('20060515132859.144808000')
INSERT INTO T ("NAME") VALUES ('20060515132859.645258000')
INSERT INTO T ("NAME") VALUES ('20060515132900.117234000')


A reader, May 29, 2006 - 6:07 am UTC

I am trying to Insert Records using a Stored Procedure.

The XML i generate has Date Fields in the following format dd-mon-yy. When i try
inserting this it throws an error that Date is Unparsable. I know that if i
Change the date format it works perfectly. My question is can I make the
Procedure insert Data using InsertXML using the above mentioned date Format. I Tried XSLT but i cldnt suceed in that.

Regards


Tom Kyte
May 30, 2006 - 8:15 am UTC

did you try, well, changing the date format as you said?

Thanks to Doug from Cincinnati!

Jan van Mourik, October 16, 2006 - 7:06 pm UTC

Just wanted to say thanks to Doug for his tip two posts up ("DBMS_XMLStore() and bind variables")! Just what I needed!
Don't see much about that in the docs. All that's mentioned in the "XML DB Developer's Guide", chapter 12 is this:

"For Inserts: You can set the list of columns to insert calling function DBMS_XMLSTORE.setUpdateColumn for each column. This is highly recommended, since it will improve performance. The default behavior is to insert values for all of the columns whose corresponding elements are present in the XML document."

Doesn't tell us why "it will improve performance" though. The whole binds thing isn't even mentioned. Actually, I think the description for insertXml is a bit misleading (see "Inserting with DBMS_XMLSTORE"):

"To insert an XML document into a table or view, you supply the table or view name and the document. DBMS_XMLSTORE parses the document and then creates an INSERT statement into which it binds all the values. By default, DBMS_XMLSTORE inserts values into all the columns represented by elements in the XML document."

That description would make me think that it is using binds, until you trace it...

Regards,

jan

XML Read and Update in Oracle Table

Mohan, October 20, 2006 - 3:20 pm UTC

A simple question and the following scenario I do have..

Environment: Oracle9i Enterprise Edition Release
9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data
Mining options
JServer Release 9.2.0.4.0 - Production

Front End: .NET Application

Scenario is: Thru the front end .net application there
would be a webservice call..
Based on that a set of 10 records is formed
in XML document which containing the
following columns and values

ID_EMP ID_DEPT ID_SALARY
1 10 3000
2 10 4000
3 10 5000

Based on XML document, I am supposed to read
it and update the values in Emp Table which
reside in Oracle.

Question is 1. As soon as the Fron end .Net Application
form the XML document where do they
store it.. Is it in any Physical Hard
disk on Server Side

2. How Oracle will read the XML document and
Update the Table.. Does Oracle
expect the document to be physically
presented.

3. what is the criteria is needed for this
operation in terms of any
Supplied Packages etc..

4. Any Code with example would be
appreciated.

Since am new to XML and kind of lost..
If anything am missed, please include that as well.

Regards,
Mohan.








Rowwise XML generation

Karthik, November 29, 2006 - 1:36 am UTC

Hi Tom,

I have a dynamically generated query. I'm writing the results of the query to a XML. This XML is then inserted to a table.

Eg:

Begin
l_query = 'select * from'||table_name;

open ref_query for l_query using 'emp';

qryctx := DBMS_XMLGEN.newcontext (ref_query);
--Generate the XML and store it in a CLOB
l_xml := DBMS_XMLGEN.getxml (qryctx);
end;

Now l_xml contains the entire resultset of the query in a single XML. What I want is that every row returned by the query should be written to a seperate XML.Since I donot know the columns or the tablenames in the query, I cannot fetch the resultset into a collection and manipulate it.

Can you please let me know, how I can write each row to a seperate XML?

Thanks,
Karthik


Tom Kyte
November 30, 2006 - 8:49 am UTC

you would have to sort of write procedural code that fetched "your keys" and then called dbms_xmlgen over and over

rather inefficient - seems like a single xml document would be the correct approach, but that is your decision.

getting multi line output while using XMLELEMENT

Prasad Rayaprolu, January 02, 2007 - 9:27 am UTC

Hi Tom,

It is only because of your explanations I'm able to speak some thing of XML now.

Earlier you have given some examples for getting an XML in desired format. But that is not sufficing my requrement.

I'm using XMLELEMENT. My Query is like :-

SELECT '<?xml version="1.0" encoding="UTF-8"?>',XMLELEMENT("ItemChannels", 
         XMLAGG(XMLELEMENT("Item",
            XMLFOREST(im.item As "ItemId",
                                xmlagg(xmlelement("Channel",wic.channel_type)) as "ChannelList") 
                     )
                  )
               ) 
      from  item_master im,
            wsi_item_channels wic
      where im.item=wic.item 
      group by im.item;


In a PL/SQL block I¿m getting the result into an XMLTYPE variable, later converting it into CLOB, then putting the CLOB into the file.

Since I¿m using XMLELEMENT for the xml creation¿ the out put is coming in 1 line.
The result set of the above query will be very large. Still its all printed in 1 line into the XMLTYPE variable then into the CLOB variable.
Because of this when I¿m trying to print the CLOB to file, I¿m getting file write error.
It is exceeding the line size limit.

1) Now I¿m really lost how to insert a new line while using XMLELEMENT.

2)How can I get a multi lined XML file in a CLOB with maximum permissible line size(32760)?

3)Is there a way to print exactly the way we want with all tags aligned properly?

4) how the same result can be accomplished using DBMS_XMLQUERY?

Please help me¿

Thanks in advance,
Prasad Rayaprolu.

XML Manipulation

Kishore, January 24, 2007 - 3:32 pm UTC

Hi Tom,

I have a small query pertaining to XML. I have to different xml inputs

Input 1
=======
<REPORT>
<HEADER1>NAME OF EMPLOYEE</HEADER1>
<HEADER2>EMPLOYEE IDENTIFICATION<HEADER2>
<HEADER3>SALARY OF EMPLOYEE</HEADER3>
</REPORT>

Input 2
=======
<EMPLOYEES>
<EMP>
<EMPNO>1234</EMPNO>
<EMPNAME>Name_1</EMPNAME>
<EMPSAL>10000</EMPSAL>
<EMP>
<EMPNO>1234</EMPNO>
<EMPNAME>Name_1</EMPNAME>
<EMPSAL>10000</EMPSAL>
</EMP>
</EMPLOYEES>

I want both the xmls to be concatinated in a way to get the output as shown below. I have Oracle 9iR2. How can this be done using the functions available for XML instead of doing the string manipulation

Output Should Be
================
<REPORT>
<HEADER1>NAME OF EMPLOYEE</HEADER1>
<HEADER2>EMPLOYEE IDENTIFICATION<HEADER2>
<HEADER3>SALARY OF EMPLOYEE</HEADER3>
<EMPLOYEES>
<EMP>
<EMPNO>1234</EMPNO>
<EMPNAME>Name_1</EMPNAME>
<EMPSAL>10000</EMPSAL>
<EMP>
<EMPNO>1234</EMPNO>
<EMPNAME>Name_1</EMPNAME>
<EMPSAL>10000</EMPSAL>
</EMP>
</EMPLOYEES>
</REPORT>

Could you please let me know how would we read the first xml find out the HEADER3 element node and append our second input xml after that.

Thanks for your response in advance

Null data is inserted with DBMS_XMLSTORE

Laxman Kondal, April 24, 2007 - 11:26 am UTC

Hi Tom,

I have xml file in Oracle10g as CLOB fetched with
insert into xmlclob values (1, httpuritype.createuri('http://172.100.109.187/resources/State.xml').getclob());

(this url is not going to work - I changed it)
and when I loaded data into a table all records are loaded as null, here is what I did:
SQL> desc xmlclob
 Name   Null?    Type
------- -------- ------------
 N               NUMBER(38)
 X               CLOB

SQL> select x from xmlclob;

X
------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW><state>Alabama</state><port>DAUPHIN ISLAND</port></ROW>
<ROW><state>Alabama</state><port>Mobile</port></ROW>
<ROW><state>Alabama</state><port>MOBILE BAR</port></ROW>
<ROW><state>Alabama</state><port>SAND ISLAND</port></ROW>
...
</ROWSET>


SQL> desc states;
 Name    Null?  Type
 -------- ----- ----------------
 STATE          VARCHAR2(30)
 PORT           VARCHAR2(50)

SQL> DELETE STATES;

76 rows deleted.

SQL> declare
  2     insCtx DBMS_XMLSTORE.ctxType;
  3     rows   NUMBER;
  4     xmlDoc CLOB;
  5  begin
  6     SELECT x INTO xmlDoc FROM XMLCLOB WHERE ROWNUM < 2;
  7     insCtx := DBMS_XMLSTORE.newContext('STATES'); -- Get saved context
  8     DBMS_XMLSTORE.clearUpdateColumnList(insCtx); -- Clear the update settings
  9     -- Set the columns to be updated as a list of values
 10     DBMS_XMLSTORE.setUpdateColumn(insCtx, 'STATE');
 11     DBMS_XMLSTORE.setUpdateColumn(insCtx, 'PORT');
 12     rows := DBMS_XMLSTORE.insertXML(insCtx, xmlDoc);
 13     DBMS_OUTPUT.put_line(rows || ' rows inserted.');
 14  end;
 15  /
76 rows inserted.

PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.

SQL> select count(*) from states;

  COUNT(*)
----------
        76

SQL> rem although there are 76 rows but all are null
SQL> select * from states;

STATE                          PORT
------------------------------ --------------------------




76 rows selected.

SQL>
---------------------


What am I missing over here which makes all records null?

Thanks and regards.
Tom Kyte
April 24, 2007 - 12:51 pm UTC

state <> STATE

ops$tkyte%ORA10GR2> declare
  2     insCtx DBMS_xmlSave.ctxType;
  3     rows   NUMBER;
  4     xmlDoc CLOB;
  5  begin
  6     SELECT x INTO xmlDoc FROM XMLCLOB WHERE ROWNUM < 2;
  7     insCtx := DBMS_xmlSave.newContext('STATES'); -- Get saved context
  8     dbms_xmlSave.setIgnoreCase( insCtx, 1 );
  9     DBMS_xmlSave.clearUpdateColumnList(insCtx); -- Clear the update settings
 10     -- Set the columns to be updated as a list of values
 11     DBMS_xmlSave.setUpdateColumn(insCtx, 'STATE');
 12     DBMS_xmlSave.setUpdateColumn(insCtx, 'PORT');
 13     rows := DBMS_xmlSave.insertXML(insCtx, xmlDoc);
 14     DBMS_OUTPUT.put_line(rows || ' rows inserted.');
 15  end;
 16  /
4 rows inserted.

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from states;

STATE      PORT
---------- --------------------
Alabama    DAUPHIN ISLAND
Alabama    Mobile
Alabama    MOBILE BAR
Alabama    SAND ISLAND

Null data is inserted with DBMS_XMLSTORE

Laxman Kondal, April 24, 2007 - 3:04 pm UTC

Hi Tom,

Thanks and learned that XML tags are case senstive.

In DBMS_XMLSAVE there was setIgnoreCase/IGNORE_CASE and not in DBMS_XMLSTORE.

Might well use REPLACE() tag to upper case before calling DBMS_XMLXTORE.

Thanks and regards.

Tom Kyte
April 24, 2007 - 3:05 pm UTC

do not use replace, you might replace data in the "data".

just use the setignorecase.

Null data is inserted with DBMS_XMLSTORE

Laxman Kondal, April 24, 2007 - 3:11 pm UTC

Hi Tom,

You are right, sure it might replace data also, so
REPLACE( x, '<state>', '<STATE>')
and
REPLACE (X, '</state>', '</STATE>')

might be better.

Thanks and regards.
Tom Kyte
April 24, 2007 - 3:16 pm UTC

but....

why would you even CONSIDER replace?

what happens when it starts being:

<State>
<STate>
.....

whatever. The right approach would seem to be setIgnoreCase since the functionality to do a case insensitive TAG is what you want...

Null data is inserted with DBMS_XMLSTORE

Laxman Kondal, April 25, 2007 - 2:28 pm UTC

Hi Tom,

It seems there is some alternate which truly I am not aware of it.

Will appricate if you could give some clue.

Thanks and regards.
Tom Kyte
April 25, 2007 - 3:56 pm UTC

did you see my example, I demonstrated how to do it????

Null data is inserted with DBMS_XMLSTORE

Laxman Kondal, April 25, 2007 - 5:07 pm UTC

Hi Tom,

We are using XE for one of our potential client and XE does not have DBMS_XMLSAE pkg and I couldn't find
setignorecase
in DBMS_XMLSTORE.

This is the subprogram summary of DBMS_XMLSTORE, unless it's burned into one of this functionality which I missed:

Summary of DBMS_XMLSTORE Subprograms:
CLEARKEYCOLUMNLIST
CLEARUPDATECOLUMNLIST
CLOSECONTEXT
DELETEXML
INSERTXML
NEWCONTEXT
SETKEYCOLUMN
SETROWTAG
SETUPDATECOLUMN
UPDATEXML


Thanks and regards.
Tom Kyte
April 25, 2007 - 5:31 pm UTC

you neglected to mention that you did not have access to that package.

Null data is inserted with DBMS_XMLSTORE

Laxman Kondal, April 26, 2007 - 9:00 am UTC

Hi Tom,

Sorry if I did not mention.
I needed to use this in XE as well as Orcle10g EE and Oracle XML DB Developer's Guide says:

The functionality of the DBMS_XMLSTORE package is similar to that of the DBMS_XMLSAVE package, which is part of the Oracle XML SQL Utility. There are, however, several key differences:
DBMS_XMLSTORE is written in C and compiled into the kernel, so it provides higher performance.
DBMS_XMLSTORE uses SAX to parse the input XML document and hence has higher scalability and lower memory requirements. DBMS_XMLSTORE allows input of XMLType in addition to CLOB and VARCHAR.
PL/SQL functions insertXML, updateXML, and deleteXML, which are also present in package DBMS_XMLSAVE, have been enhanced in package DBMS_XMLSTORE to take XMLType instances in addition to CLOB values and strings. This provides for better integration with Oracle XML DB functionality.

That's why I used DBMS_XMLSTORE. Is there any way, I am sure you have some sort of workaround, to fix case sensitivity it in DBMS_XMLSTORE.

Thanks and regards.
Tom Kyte
April 26, 2007 - 1:06 pm UTC

no

dbms_xmlsave vs dbmx_xmlstore date/time stamps

bah, May 30, 2007 - 12:24 am UTC

In the example on dbms_xmlsave, you use dates of the formate mm/dd/yyyy HH24:MI:SS and this works, however if I try the same with dbms_xmlstore, no joy, does this work differently, for example the following works fine (dbms_xmlsave), but the second example (dbms_xmlstore) does not - is there a correct time format for the dbms_xmlstore? I cannot find any documentation regarding this:

DROP TABLE x_AskTom;

CREATE TABLE x_AskTom
(
 id number
 , name varchar2(250) 
 , created_ts date 
);



DECLARE
savCtx    DBMS_XMLSAVE.ctxType;
v_xmlTmp  XMLType;
v_rows    NUMBER;
    
BEGIN 
execute immediate 'truncate table x_AskTom';

SELECT XMLTransform(
xmltype.createXML('<?xml version="1.0" encoding="UTF-8"?>
<myXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb" > 
<item>
<id>1</id>
<Name>Julian</Name>
</item>
<item>
<id>2</id>
<Name>Dick</Name>
</item>
</myXML>
')
, 
xmltype.createXML('<?xml version="1.0"?>
<xsl:stylesheet version="1.0"  
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                >
 <xsl:template match="/">
    <ROWSET>
  <xsl:for-each select="myXML/item">
        <ROW>
             <ID><xsl:value-of select="id" /></ID>
             <NAME><xsl:value-of select="Name" /></NAME>
             <CREATED_TS>'|| to_char(systimestamp, 'mm/dd/yyyy HH24:MI:SS')|| '</CREATED_TS>
        </ROW>
      </xsl:for-each>
     </ROWSET>
 </xsl:template>
</xsl:stylesheet>')
) INTO v_xmlTmp FROM dual;

-- now perform some saves
savCtx := DBMS_XMLSAVE.newContext('X_ASKTOM');
v_rows := DBMS_XMLSAVE.insertxml(savCtx,  v_xmlTmp.getClobVal());
COMMIT;
DBMS_XMLSAVE.closeContext(savCtx);
DBMS_OUTPUT.PUT_LINE(v_rows || ' rows inserted...');
END;
/



whereas the following will throw a wobbler with mask adjusted to 'dd/Mon/yyyy HH24:MI:SS' - though it works ok with the mask adjusted to 'dd/Mon/yyyy':

-- Oracle 10.2.0.1.0

DROP TABLE x_AskTom;

CREATE TABLE x_AskTom
(
 id number
 , name varchar2(250) 
 , created_ts date 
);



DECLARE
savCtx    DBMS_XMLSTORE.ctxType;
v_xmlTmp  XMLType;
v_rows    NUMBER;
    
BEGIN 
execute immediate 'truncate table x_AskTom';

SELECT XMLTransform(
xmltype.createXML('<?xml version="1.0" encoding="UTF-8"?>
<myXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xdb="http://xmlns.oracle.com/xdb" > 
<item>
<id>1</id>
<Name>Julian</Name>
</item>
<item>
<id>2</id>
<Name>Dick</Name>
</item>
</myXML>
')
, 
xmltype.createXML('<?xml version="1.0"?>
<xsl:stylesheet version="1.0"  
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                >
 <xsl:template match="/">
    <ROWSET>
  <xsl:for-each select="myXML/item">
        <ROW>
             <ID><xsl:value-of select="id" /></ID>
             <NAME><xsl:value-of select="Name" /></NAME>
             <CREATED_TS>'|| to_char(systimestamp, 'dd/Mon/yyyy HH24:MI:SS')|| '</CREATED_TS>
        </ROW>
      </xsl:for-each>
     </ROWSET>
 </xsl:template>
</xsl:stylesheet>')
) INTO v_xmlTmp FROM dual;

--             <CREATED_TS>'|| to_char(systimestamp, 'dd/Mon/yyyy HH:MI:SS')|| '</CREATED_TS>

-- WORKS:              <CREATED_TS>1/Mar/2007</CREATED_TS>
-- NOT WORKS:          <CREATED_TS>1/Mar/2007 12:00:00 AM</CREATED_TS>
-- NOT WORKS:          <CREATED_TS>1/Mar/2007 03:06:24.860031 PM +11:00</CREATED_TS>
-- NOT WORKS:          <CREATED_TS>1/Mar/2007 15:06:24</CREATED_TS>
-- now perform some saves
savCtx := DBMS_XMLSTORE.newContext('X_ASKTOM');
DBMS_XMLStore.clearUpdateColumnList(savCtx); 
DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'ID');
DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'NAME');
DBMS_XMLSTORE.SetUpdateColumn (savCtx, 'CREATED_TS');
v_rows := DBMS_XMLSTORE.insertxml(savCtx,  v_xmlTmp.getClobVal());
COMMIT;
DBMS_XMLSTORE.closeContext(savCtx);
DBMS_OUTPUT.PUT_LINE(v_rows || ' rows inserted...');


END;
/



xml

Sam, July 31, 2007 - 6:59 pm UTC

Tom:

Do you know why when i try your example in oracle 9i pl/sql program it does not work.


PROCEDURE TEST_XML_SQL

IS
l_ctx dbms_xmlquery.ctxHandle;
l_clob clob;
g_clob clob;
begin

l_ctx := dbms_xmlquery.newContext('select * from emp1');
dbms_lob.createtemporary(:g_clob,true,dbms_lob.session);
:g_clob := dbms_xmlquery.getXML(l_ctx);

END; -- Procedure

PLS-00049 bad bind variable 'G_CLOB'
PLS-00049: bad bind variable 'G_CLOB'
Tom Kyte
August 05, 2007 - 9:44 am UTC

because that is not my example.


g_clob is NOT a bind variable - g_clob is a local variable.

I don't know why you put a colon in front of it there.

You never use bind variables explicitly in a stored procedure like that. It doesn't work that way.

remove hex chars from clob value

sara, October 02, 2007 - 10:05 am UTC

Hi,

I am getting an xmlelement and converting to a clob value.
When I send out the output as clob, it is being said that the clob data contains some hex chars which should be remeoved. How do I remove those hex chars from the clob

Thanks

Saradha
Tom Kyte
October 05, 2007 - 10:52 am UTC

what is a 'hex char'???

you'll need to be a bit more 'precise'....


xml

A reader, May 28, 2008 - 7:51 pm UTC


Read XML Data as 'IN' list

Srini, July 06, 2008 - 11:23 pm UTC

Hi Tom,

I'm trying to push XML data into a table and read it as a 'IN' list in a SQL. This is what I've done so far.

SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

SQL> create table test
  2  (
  3  tst_fld XMLTYPE
  4  )
  5  /

Table created.

SQL> insert into test
  2  select XMLELEMENT("TABLES",xmlagg(XMLELEMENT("TABLENAME",TABLE_NAME))) from
 all_tables where rownum < 5
  3  /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test
  2  /

TST_FLD
--------------------------------------------------------------------------------
<TABLES><TABLENAME>TAB$</TABLENAME><TABLENAME>USER$</TABLENAME><TABLENAME>BOOTSTRAP$</TABLENAME><TABLENAME>UNDO$</TABLENAME></TABLES>


Now, If I give
select extract(tst_fld,'TABLES/TABLENAME') from test


I can get the 'TABLENAME' as a set of nodes. Is it possible to give the values directly as a list to a SQL query. Something like

select * from tablea where fld1 in (<XML node values related to 'TABLENAME'>)


I hope I've made it clear. Please let me know otherwise.

XML or table

Sagar, July 23, 2008 - 8:25 am UTC

I have come across java programmers who store the data from master tables in XML files and then get the required data from XML files so as to avoid hit to database.They say it is fast and the master data is has to refered frequently.

Master tables contains not more than 1000 rows and do not change frequently. If there is change in tables then we have to generate XML files again.

I do not know how much time they save. By doing this they put the database at risk of losing data integrity.








Tom Kyte
July 24, 2008 - 10:23 am UTC

tell them to show you the numbers that show it is faster.

they will not be able to.


XML into rows

Juan, August 16, 2008 - 5:31 am UTC

Hi Tom,

I have XMLs like this (working in oracle9i):

<test>
<measure>measure1</measure>
<measure>measure2</measure>
<measure>measure3</measure>
<measure>measure4</measure>
<measure>measure5</measure>
<val>
<element>element1</element>
<r>44444</r>
<r>4444</r>
<r>444</r>
<r>44</r>
<r>4</r>
</val>
<val>
<element>element2</element>
<r>5</r>
<r>55</r>
<r>555</r>
<r>5555</r>
<r>55555</r>
</val>
</test>

And then, I'm using this procedure to extract measure/value:

DECLARE

xml XMLtype;

BEGIN
xml := XMLTYPE(Getfilecontent(BFILENAME('XML_DIR','test.xml')));

SELECT
meas.element,
measures,
value
FROM
(
SELECT
extractvalue(value(mt), '/') measures,
extractvalue(value(element), '/') element,
ROWNUM pos
FROM TABLE(XMLSequence(extract(xml,'/'))) a,
TABLE(XMLSequence(extract(value(a), '/test/val/element'))) element,
TABLE(XMLSequence(extract(value(a), '/test/measure'))) mt) meas,
(SELECT extractvalue(value(r), '/') value,
extractvalue(value(element), '/') element,
ROWNUM pos
FROM TABLE(XMLSequence(extract(xml,'/'))) a,
TABLE(XMLSequence(extract(value(a), '/test/val'))) val,
TABLE(XMLSequence(extract(value(val), '/val/element'))) element,
TABLE(XMLSequence(extract(value(val), '/val/r'))) r) val
WHERE meas.element=val.element
AND meas.pos=val.pos;

And then, I get this:

ELEMENT,MEASURES,VALUE
element1,measure1,44444
element1,measure2,4444
element1,measure3,444
element1,measure4,44
element1,measure5,4
element2,measure1,5
element2,measure2,55
element2,measure3,555
element2,measure4,5555
element2,measure5,55555


Now, I would like it to insert into tabla XXX (measure1, measure2, measure3, measure4, measure5)
element1,44444,4444,444,44,4
element2,5,55,555,5555,55555

How is the best way to get this?
Thank you

Loading XML data into table columns

MK, September 26, 2008 - 12:48 pm UTC

Hi Tom,
I have a situation currently whereby some Java developers have decided to import/export table data from local XE boxes into XML files using Java programs. Currently the import into tables takes about an hour.. which is ridiculous compared to how much faster it is using 10g Data pump. If we did have to stick to the XML paradigm could you suggest a way of loading the elements (columns) into their respective columns in the relational database? I am not trying to store these XMl documents as CLOBS in the database. If you can suggest a fast way of unloading/reloading table data either using SQLLDR or some other mechanism that would work too. The idea behind having XML files is that developers feel that they wish to be able to see what data gets exported when they make changes. What I need is a method of detecting a difference in the XML file and the table data and then dumping it all out if there is a change in table definition or rows. Is this possible?


XML data
--------
<?xml version="1.0" encoding="UTF-8" ?>
- <dataset>
- <table name="HELP_MESSAGE_LANG">
<column>HELP_MESSAGE_ID</column>
<column>LANGUAGE</column>
<column>IS_SMALL_VALUE</column>
<column>SMALL_VALUE</column>
<column>LARGE_VALUE</column>
<column>COMMENTS</column>
<column>DATA_CLASSIFICATION</column>
<column>CREATED</column>
<column>INSERTED</column>
<column>MODIFIED</column>
- <row>
<value>1</value>
<value>en</value>
<value>1</value>
- <value>
- <![CDATA[ Get New Artists
]]>
</value>
<null />
<null />
<value>300000</value>
- <value>
- <![CDATA[ 2007-05-17 12:45:03.0
]]>
</value>
- <value>
- <![CDATA[ 2007-05-17 12:45:03.0
]]>
</value>
- <value>
- <![CDATA[ 2007-05-17 12:45:03.0
]]>
</value>
</row>


</table>
</dataset>



Table definition
----------------

SQL> desc help_message_lang
Name Type Nullable Default Comments
------------------- ------------------ -------- ------- --------
HELP_MESSAGE_ID NUMBER(10) NULL
LANGUAGE CHAR(2 CHAR)
IS_SMALL_VALUE NUMBER(1) 1
SMALL_VALUE VARCHAR2(256 CHAR) Y
LARGE_VALUE CLOB Y
COMMENTS VARCHAR2(256 CHAR) Y
DATA_CLASSIFICATION NUMBER(10) 400000
CREATED TIMESTAMP(3) SYSDATE
INSERTED TIMESTAMP(3) SYSDATE
MODIFIED TIMESTAMP(3) SYSDATE


Continuation...

MK, September 26, 2008 - 8:58 pm UTC

Hi Tom,
Just to add to my previous post, I have unloaded the data using your PL/SQL routine to a flat file .csv and now am trying to use External Tables to load the data into the database.

<b>CSV File ( 2 records pipe delimeted with CLOB data in it)</b>

12|xx|0||<p>This screen displays your Buzz profile. This includes your Buzz name, image, and catchphrase along with your Buzz stats such as your star-rating, your "listens" (the number of times others have listened to your playlists) and the number of Buzz friends you have. </p>

<p>
From the profile screen you can view:
<ul>
<li><strong>Cool members -</strong> Popular members, featured members and members that you may like based on music tastes.  </li>
<li><strong>Buzz playlists -</strong> Popular playlists, featured playlists, celebrity playlists and playlists that you may enjoy based on your music tastes. </li>
<li><strong>My Friends -</strong> A list of users that that you have added as a friend. </li>
</ul>
</p>||300000|17-MAY-07 12.45.03.000|17-MAY-07 12.45.03.000|17-MAY-07 12.45.03.000
13|en|1|Lineup|||300000|17-MAY-07 12.45.03.000|17-MAY-07 12.45.03.000|17-MAY-07 12.45.03.000




Now I was trying the following code to create an external table following one of your examples here and get the following error

CREATE DIRECTORY oraload AS 'c:\oraload\';
GRANT READ,WRITE ON DIRECTORY oraload TO PUBLIC;


create table ext_help_message_lang
(
HELP_MESSAGE_ID NUMBER(10) ,
LANGUAGE CHAR(2) ,
IS_SMALL_VALUE NUMBER(1) ,
SMALL_VALUE VARCHAR2(256) ,
LARGE_VALUE CLOB ,
COMMENTS VARCHAR2(256) ,
DATA_CLASSIFICATION NUMBER(10) ,
CREATED TIMESTAMP(3) ,
INSERTED TIMESTAMP(3) ,
MODIFIED TIMESTAMP(3)
)
organization external
(type oracle_loader
default directory ORALOAD
access parameters
(fields terminated by '|'
missing field values are null
(
HELP_MESSAGE_ID ,
LANGUAGE ,
IS_SMALL_VALUE ,
SMALL_VALUE ,
LARGE_VALUE ,
COMMENTS ,
DATA_CLASSIFICATION,
CREATED char date_format timestamp mask "DD-MON-RR HH24.MI.SSXFF" ,
INSERTED char date_format timestamp mask "DD-MON-RR HH24.MI.SSXFF",
MODIFIED char date_format timestamp mask "DD-MON-RR HH24.MI.SSXFF"
)
)
location ('help_message_lang.csv')
)
parallel 2
reject limit 1
;


-- On Running the commands above
SQL>

Table created

SQL> select count(*) from ext_help_message_lang;

select count(*) from ext_help_message_lang

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "language": expecting one of: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 5 column 2
ORA-06512: at "SYS.ORACLE_LOADER", line 19

Can you please help out and explain how I can do this the right way?


Tom Kyte
September 27, 2008 - 11:32 am UTC

did you put the html into that extract or was it there - it is hard to figure out what is in the file or not.

Now, if the data has embedded newlines like it appears it does - you will need to MODIFY the EXTRACT code to put out a new "end of line marker" - eg: end the line with something unique so we can tell the external table "the end of line is '###\n', not just '\n'"


but here is what I suggest, I like to use sqlldr to create the external table sometimes - especially when I have a sqlldr control file with the syntax already there (and I did for this, I took it from my book - on loading data with newlines...)

ops$tkyte%ORA10GR2> create table test ( a int, b date, c varchar2(4000) );

Table created.

<b>that is the structure of the external table...</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> !cat test.ctl
LOAD DATA
INFILE test.dat "str X'2323230A'"
INTO TABLE TEST
REPLACE
FIELDS TERMINATED BY ','
optionally enclosed by '"'
TRAILING NULLCOLS
(a, b, c)

<b>that is a control file to load it - X'2323230A' is hex for ###\n
</b>

ops$tkyte%ORA10GR2> rem !sqlldr / test.ctl external_table=generate_only
ops$tkyte%ORA10GR2> rem edit test.log

<b>run sqlldr to create the external table in the log file, edit log file to fix up the create table statement and then run it:</b>

ops$tkyte%ORA10GR2> !cat test.log
create or replace directory mydir as '/home/tkyte'
/

CREATE TABLE "ET"
(
  "A" NUMBER(38),
  "B" DATE,
  "C" VARCHAR2(4000)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY MYDIR
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY 0x'2323230A' CHARACTERSET US7ASCII
    BADFILE 'test.bad'
    LOGFILE 'test.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "A" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "B" CHAR(255)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
      "C" CHAR(4000)
        TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    )
  )
  location
  (
    'test.dat'
  )
)REJECT LIMIT UNLIMITED
/

ops$tkyte%ORA10GR2> @test.log
ops$tkyte%ORA10GR2> create or replace directory mydir as '/home/tkyte'
  2  /

Directory created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> CREATE TABLE "ET"
  2  (
  3    "A" NUMBER(38),
  4    "B" DATE,
  5    "C" VARCHAR2(4000)
  6  )
  7  ORGANIZATION external
  8  (
  9    TYPE oracle_loader
 10    DEFAULT DIRECTORY MYDIR
 11    ACCESS PARAMETERS
 12    (
 13      RECORDS DELIMITED BY 0x'2323230A' CHARACTERSET US7ASCII
 14      BADFILE 'test.bad'
 15      LOGFILE 'test.log_xt'
 16      READSIZE 1048576
 17      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
 18      MISSING FIELD VALUES ARE NULL
 19      REJECT ROWS WITH ALL NULL FIELDS
 20      (
 21        "A" CHAR(255)
 22          TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 23        "B" CHAR(255)
 24          TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
 25        "C" CHAR(4000)
 26          TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
 27      )
 28    )
 29    location
 30    (
 31      'test.dat'
 32    )
 33  )REJECT LIMIT UNLIMITED
 34  /

Table created.

ops$tkyte%ORA10GR2> !cat test.dat
1,01-jan-2008,"How now brown cow,
how you doing?
this is the end...."###

<b>that is my data - using quotes to get commas saved in the long text and ending the line with ###
</b>
ops$tkyte%ORA10GR2> select * from et;

         A B
---------- ---------
C
-------------------------------------------------------------------------------
         1 01-JAN-08
How now brown cow,
how you doing?
this is the end....


Loading CLOB into external tables

MK, September 27, 2008 - 4:36 am UTC

Hi,
With my previous example and error I realized that I was not wrapping the column names with a "" hence a predefined word like LANGUAGE was causing me problems.
I am using the PL/SQL program provided by Tom to flatten the contents of relational tables into CSV files pipe delimeted. Hence I have control over the way that data is put into files as well. What is the best way of dealing with CLOB data types and TIMESTAMPS? I am also aware of Tom's flatten Pro*C which works much faster than even the PL/SQL but I think the PL/SQL will suffice for this exercise. :)

1. CLOB Problem: When I unload the CLOB data using the PL/SQL dump_csv function it puts the clob data into the CSV file with a lot of new line characters and other special predefined characters that I might have to escape. Then using the external tables on this data would not work. Can you show me a good trick to unload and load CLOB data efficiently. The PL/SQL function is pretty fast to unload the amount of data that I need to move. Its about 2Gb which is not much at all.

2. The Timestamps. Is there a way to modify the PL/SQL function to go ahead and format the timestamps and dates in a standard format such that if these CSV dumps are given to developers with different XE locale settings they are still able to use external tables to get the data back in.

I am in the process of writing a PL/SQL routine that will get all the list of tables in the DB and call the dump_csv function on each table to drop them out into CSV files. I will check to see if the column datatype is a CLOB or a TIMESTAMP and format accordingly. (is there some other things i should look out for?). Then another routine to generate the external table commands from the list of tables.
Now once the data is loaded into external tables would you advise to disable all constraints, foreign keys, indexes on the target table. And then do a insert ..select from ext_table? With the insert would you suggest NOLOGGING to speed things up?

I want you to advise me as to what would be the simplest and relatively faster approach of unloading and loading data into a local 10g XE database.

Thanks in advance,
MK
Tom Kyte
September 27, 2008 - 11:41 am UTC

see above....

Loading BLOBs into External Tables

MK, September 27, 2008 - 12:12 pm UTC

That was a brilliant solution. As to answer your question about the CLOB column. I use Tom's dump_csv PL/SQL function to unload the table data and the CLOB in this table happens to have HTML text in it. It got dumped out alright. I am facing problems using Tom's dump_csv when it comes across a BLOB field. How do I dump this one out? A CLOB seems to just be treated like a multiple line string. Now that I can terminate the string with something other than the newline to mark the end of the record then I can understand how it would be easy for the external table solution to work.
But the BLOBs currently store Java Serialized Objects in them (don't ask me why) and I am wondering what the best way of dumping them out and loading them in would be?
Can you please suggest how I deal with BLOBs too?
Tom Kyte
September 28, 2008 - 9:39 am UTC

I would use export and import (or the data pump depending on release).

Blobs are problematic due to the fact that - well - they are not text. You do not have an end of line marker that you can use (blobs can contain anything - truth be told, so could the clobs but we can use our knowledge of the data to use some string that won't happen)

why cannot you just export/import the data?

Loading BLOB into External Tables

MK, September 27, 2008 - 12:30 pm UTC

Just to add to my previous post (sorry this is becoming a habit now :) ) but I found this article on the web about using external tables to load LOB data.

http://www.oracle-base.com/articles/10g/LoadLobDataUsingSqlLoader.php

Would that mean that I would have to modify Tom's dump_csv PL/SQL routine to check if it is dealing with a BLOB or a CLOB and place it into external files into the directory?

Cheers,
MK

Loading BLOB into External Tables

A reader, September 29, 2008 - 8:11 am UTC

Trust me I have generated a dump using Datapump as well but java architects and developers alike in the company love the idea of being able to see their exported dumps visually! Even though the current process of using Java to dump out tables in XML format is so slow, tedious, time consuming and an absolute waste of time. So for now I have a PL/SQL script that goes through a list of all the tables in the schema and dumps them out as CSV or DAT files and does it all in a matter of seconds! I skip the columns that are of type BLOB. With the CLOBs I am pretty sure that they contain XML or HTML data in there...but I don't want to take this risk with images stored in BLOBs. I could check to see if its a BLOB and then create an external file, but that might be overkill for now.

They also want to be able to import/export a SUBSET of data in tables. they have a data classification column in every table from which they delete data and insert data into. I think this process is ludicrous hence my push to get this process working in pure PL/SQL using dump_csv and external tables. I will then write a JDBC wrapper to call this PL/SQL package of mine to keep the Java guys happy! :)

Say I had to import/export only a subset of each table based on what the value in a column was... this column can have a maximum of say 5 distinct value types (Ex: 1000, 2000, 3000). Would you suggest creating an index on these columns in my script to pull the data out and then drop them before inserting records back in there? Or would you suggest using the MERGE command? What would be a faster option?


Tom Kyte
September 29, 2008 - 9:23 am UTC

this is binary data, there is nothing to be seen here.


Unless you want to write a ton of code, use data pump.

If you want to write a ton of code, you are on your own.


data pump (and even old fashioned export) can filter (where clause)

xml

A reader, November 19, 2008 - 7:45 pm UTC

Tom:

The TEST_RESULT is a CLOB column and there is data for this record. do you know why the result is XMLTYPE(). I thought the function will either give you valid XML output or an error if the XML file is not valid?

  1* select to_xmltype(med_result) from bk_data where bkno=1000
SQL> /

XMLTYPE(TEST_RESULT)()
--------------------------------------------------------------------------
XMLTYPE()


2. If i have an xml file in a CLOB column would i be able to use XPATH to query the file like

select extractvalue()

or do i need to convert it using XMLTYPE and then query it.

Tom Kyte
November 24, 2008 - 11:21 am UTC

given we cannot see med_result - and what is to_xmltype - and why is the column in the select med_result, but the column name in sqlplus output is test_result.


no create
no insert
no look - if I cannot run it, I'm not going to waste cycles trying to guess what you actually did.


2) extractvalue described:

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions052.htm#SQLRF06173

... The EXTRACTVALUE function takes as arguments an XMLType instance and an XPath expression and returns a scalar value of the resultant node. ...


ops$tkyte%ORA10GR2> select extractvalue( xmltype('<doc><x>Hello</x><y>World</y></doc>'), '/doc/x' ) from dual;

EXTRACTVALUE(XMLTYPE('<DOC><X>HELLO</X><Y>WORLD</Y></DOC>'),'/DOC/X')
-------------------------------------------------------------------------------
Hello

xml

A reader, November 20, 2008 - 10:15 pm UTC

Tom:

If i want to show the EMP table in XML on the web, i do this but i get an error using htp.p. DO i need to conver the xml type data to string? or any other comments

FOR x in
(SELECT XMLELEMENT("Emp", XMLELEMENT("empno", empno),
XMLELEMENT("ename", ename),
XMLELEMENT("job", job) )
AS "result"
FROM emp1 )
loop
htp.p (x.result);
end loop;

PLS-00306: WRONG NUMBER OF TYPES OR AGUMENTS


Tom Kyte
November 24, 2008 - 1:40 pm UTC

well, first thing I would do is see what htp.p takes as input


PROCEDURE P
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CBUF                           VARCHAR2                IN     DEFAULT
PROCEDURE P
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 DBUF                           DATE                    IN
PROCEDURE P
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NBUF                           NUMBER                  IN


and then I'd read about xmlelement

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions220.htm#SQLRF06168

to see what it might return...

<quote> ... It returns an instance of type XMLType ... </quote>


and then I'd figure out how to convert an xmltype into a varchar, date or number....


scott%ORA10GR2> declare
  2          nm      owa.vc_arr;
  3          vl      owa.vc_arr;
  4  begin
  5          nm(1) := 'WEB_AUTHENT_PREFIX';
  6          vl(1) := 'WEB$';
  7          owa.init_cgi_env( nm.count, nm, vl );
  8  end;
  9  /

PL/SQL procedure successfully completed.

scott%ORA10GR2>
scott%ORA10GR2> @test
scott%ORA10GR2> begin
  2  for x in (SELECT XMLELEMENT("Emp",
  3                     XMLELEMENT("empno", empno),
  4                     XMLELEMENT("ename", ename),
  5                     XMLELEMENT("job", job) ) result
  6               FROM emp )
  7  loop
  8          htp.p( x.result.getclobval() );
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.



I used a clob because I know that can be implicitly converted into a 32k varchar2 in plsql - if you need more than 32k, you need to work a bit more and break the clob up into 32k chunks and htp.p them

XML

sam, November 26, 2008 - 10:24 pm UTC

Tom:

You are really genius! It works like art.

1. The issue here is that how do you really know whether the ouput fom Database is going o be > 32 K or not? It depends on the query and the input parameters provided?

2. It seems oacle is pretty powerful when it comes to XMl storage, manipulation and geenration.

How do you advise to create XML from DB:
a) using XMLELEMENT like above (SQLX).
b) using DBMS_XMLGEN
c) using SYS_XMLGEN
d) using a cursor for query and customized code to print the tags inside the loop.

thanks very much



Tom Kyte
November 28, 2008 - 5:19 pm UTC

This had nothing to do with intelligence, inside information, genius or anything like that.

It had only to do with common debugging sense - something we should all have been able to do really fast. I tried to point that out:

well, first thing I would do is see what htp.p takes as input


PROCEDURE P
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
CBUF VARCHAR2 IN DEFAULT
PROCEDURE P
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DBUF DATE IN
PROCEDURE P
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NBUF NUMBER IN



and then I'd read about xmlelement

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions220.htm#SQLRF06168

to see what it might return...

<quote> ... It returns an instance of type XMLType ... </quote>


and then I'd figure out how to convert an xmltype into a varchar, date or number....






1) and - this is key - your knowledge of the possible data set....

So, if you think to yourself "could it be greater than 32k", then - well - you have your answer. Do you have a requirement to support more than 32k? well, you have your answer. Be prepared to deal with more than 32k

2) especially after you read the documentation - sure.

the only answer to a question like that is "it depends"

If there were a universal best way, in all cases....

why would we have bothered implementing the others?


but once you have *learned* them all, you'll be able to answer your own question each time it comes up (really! you will)


xml

A reader, November 28, 2008 - 9:02 pm UTC

Tom:

I did read chapter 10 once and did not fully understood when to use which. It might take more than one read.
For SQL queries it said to use DBMS_XMLGEN. But I can get the same result using XMLELEMENT.
Since you know this stuff inside out can't you just list how to use it like this

if (you expect more than 32 K output for the query)
use DBMS_XMLGEN
elsif ( if output is less than 32 K)
use XMLELEMENT and SQLJ functions
else
use cursor and custom code to print the tags
end if

2. Would XMLELEMENT escape the special characer or do i need to use ESCAPE_SC for the data within XMELEMENT.

thank you,
Tom Kyte
December 01, 2008 - 5:57 am UTC

... It
might take more than one read.
...

not only that but it will take experience, testing, learning, knowledge, common sense, intuition, etc etc etc.

That is - it is like all other features, functions, options in this information technology industry we are in.


...
Since you know this stuff inside out can't you just list how to use it like .
....

A) I don't know everything inside out
B) Even if I did - even if I did - and such a list were possible ( so you could be replaced by a code generator, we would obviously NOT NEED you at all - you would be replaced by a check list and the end user would just hit check boxes and viola - they are the programmer!!!) we would have printed it in the documentation.


it is not just size
it is not just 'what api'

it is many factors. SMK (part of your email) I will address you directly. You ask more than anyone (that is good). However, you most frequently ask to be told exactly how to do things - precisely and exactly how. If you continue in that mode - you will never become a good developer or DBA. You will always look for the checklist, you will learn one way to do something and stick with you - ultimately you will be replaced by a piece of software that simply automates what you do.

XML through PL/SQL proc

reader from LA, January 13, 2009 - 2:00 pm UTC

Tom,

I got requirement to generate XML to URL. So I configured XMLDB and configured embedded PL/SQL Gateway with DBMS_EPG so everything works fine in that respect.
I created package that generates XML tags using htp.print while executing through cursor but that is pretty slow for large data sets.

So I created following packaged procedure but I can't make it to work. Can you pleas tell what I am doing wrong.

Essentially I need to pass parameters to procedure that would execute and display result set in the browser.
As I said earlier my other procedures in the same package execute like this and work just fine but very inefficient.

http://servername:8080/xml_report.p?p_date_format=q

PROCEDURE p (p_date_format varchar2)
IS
queryCtx dbms_xmlquery.ctxType;
result CLOB;
BEGIN

queryCtx := dbms_xmlquery.newContext(
'select c1,
trunc( D, '||p_date_fomat||'),
count(*) count
from t
where c2 = 'Y'
and c3 is not null
and c4 = 'SCOTT'
group by C1, trunc( D, '|| p_date_format||')'
);

dbms_xmlquery.setRowTag(queryCtx, 'info');
dbms_xmlquery.setRowSetTag(queryCtx, 'root');

result := dbms_xmlquery.getXml(queryCtx);
printClobOut(result);
dbms_xmlquery.closeContext(queryCtx);

EXCEPTION

when others then
raise;

END p;

Thank you in advance

Tom Kyte
January 16, 2009 - 4:04 pm UTC

EXCEPTION

when others then
raise;

END p;


why, why, why would anyone do that? why? I cannot imagine why - unless you purposely want to make debugging virtually impossible.


no binds either :( using string concatenation, sql injectable.

and you don't even say "why it doesn't work"

"but I can't make it to work."

Ok, "my car won't start"

why not?

Hmm?

Dan, January 20, 2009 - 3:15 pm UTC

Tom,

I thought that your rule was that this was bad:
EXCEPTION
  when others then
    NULL;
END p; 


And that any "when others then" should be followed by a raise. So what's wrong with the code above? Shouldn't the raise propagate the error?
Tom Kyte
January 20, 2009 - 3:49 pm UTC

exception
when others then raise;
end;


Why would you do that? Think about it. Does it do anything positive? Why would you

a) catch all exceptions
b) DO NOTHING
c) re-raise it


the only thing accomplished by that is to HIDE THE REAL LINE NUMBER that caused the error.

ops$tkyte%ORA11GR1> create table t ( x int check (x>0) );

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> begin
  2      insert into t values ( 1 );
  3      insert into t values ( 2 );
  4      insert into t values ( 0 );
  5      insert into t values ( 3 );
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C0016182) violated
ORA-06512: at line 4


ops$tkyte%ORA11GR1> begin
  2      insert into t values ( 1 );
  3      insert into t values ( 2 );
  4      insert into t values ( 0 );
  5      insert into t values ( 3 );
  6  exception
  7      when others
  8      then
  9          raise;
 10  end;
 11  /
begin
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C0016182) violated
ORA-06512: at line 9



first error message - useful.

second error message - would cause me to scream when I pulled up the code seeing as how the developer just put that in their to foil any attempts to figure out "what went wrong"


exception

SAm, January 20, 2009 - 6:09 pm UTC

Tom:

It would only make sense to do the above if the developer "logs" the error and "displays" a user firendly message to the user before "RAISE" the original error.

right?


Tom Kyte
January 20, 2009 - 7:30 pm UTC

I wouldn't even do it then. Too far down in the call stack. Should be done at the HIGHEST level, else you'll end up logging it 500 times before it gets back to the highest level in the call stack.

extract data from xml file and pass to Oracle table

Mário Campos, April 24, 2009 - 12:02 pm UTC

hello Tom i'm working in Oracle database and SAP Mii and i use this stored procedure to catch data from XML files, but only when the XML file is little because when the XML files are bigger SAP Mii throws an error like..String too long...do you could help me? do you know some way do pass this problem?

create or replace
PROCEDURE INSERTXML2(
p_xml_in CLOB,
p_table IN VARCHAR2 ) AS

insCtx DBMS_XMLStore.ctxType;
v_rows NUMBER;

BEGIN

insCtx := DBMS_XMLStore.newContext(p_table); -- get saved context
dbms_xmlstore.setRowTag(insCtx,'Row');
DBMS_XMLStore.clearUpdateColumnList(insCtx); -- clear the update settings
-- set the columns to be updated as a list of values
DBMS_XMLStore.setUpdateColumn(insCtx,'ORDERNUMBER');
DBMS_XMLStore.setUpdateColumn(insCtx,'PLANT');
DBMS_XMLStore.setUpdateColumn(insCtx,'MATERIAL');
DBMS_XMLStore.setUpdateColumn(insCtx,'LINENUMBER');
DBMS_XMLStore.setUpdatecolumn(insCtx,'NOMSPEED');
DBMS_XMLStore.setUpdatecolumn(insCtx,'STARTDATE');
DBMS_XMLStore.setUpdatecolumn(insCtx,'FINISHDATE');
DBMS_XMLStore.setUpdatecolumn(insCtx,'TARGETQTY');
DBMS_XMLStore.setUpdatecolumn(insCtx,'UNIT');
DBMS_XMLStore.setUpdatecolumn(insCtx,'SYSTEMSTATUS');
v_rows := DBMS_XMLStore.insertXML(insCtx, p_xml_in);
-- Close the context
DBMS_XMLStore.closeContext(insCtx);

END;


regards


Tom Kyte
April 27, 2009 - 12:26 pm UTC

wouldn't that be a question for SAP? In all probability - whatever SAP Mii is - it is binding a STRING (database independence and all....)

and depending on how they bind, the limit for a string is either going to be 4000 bytes or 32kbytes




You'll need to ask them how to properly support a clob using their API's.

data validation using XSD

rahul, June 18, 2009 - 1:30 pm UTC

Hi Tom/Sean,

I am new to XML and come from hardcore DBA background. I have done some amount of reading on this and have a problem at hand. I have data stored in oracle tables. I now want to generate XML file from it. I know it can be done using the dbms_xmlgen. My question is, is it possible to validate this data (using xsd) before pushing it in the XML file? As per my understanding i can validate the xml file is it correct?
Could you please explain this with example. I am working on 10g.

Thanks,
Rahul.
Tom Kyte
June 18, 2009 - 3:34 pm UTC

I'll refer you to the XML discussion forums on otn.oracle.com, Sean is no longer with Oracle and I don't do XML :)

Do we have any PL/SQL APIs for XML digital signature

Divya, June 08, 2010 - 2:17 am UTC

Hi Tom,

Can you please let me know if we have any PL/SQL APIs to put digital signature into an XML document.

Thank you for your help in advance

Gaurang, July 25, 2010 - 6:44 am UTC

Tom - We've a requirement where we've to split XML document based on number of order line items.

Currently we use Oracle SQLX functions to generate Order XML document using PL/SQL and send it to our parent company using Oracle AQ. We use Oracle Siebel CRM application to capture order. Number of line items in the order could go up to ~1000. Current solution is to send one big XML that contain both Order Header and ALL line items.

Our parent company uses TIBCO as a middleware and if the number of line items in an order goes beyond 500, its raising errors so the middleware team is asking us to split XML document in 400 line chunks.

e.g. if an order has 1400 line items, middleware expects 4 XML documents to be split (400, 400, 400, 200)

We use Oracle 9.2.0.6. I know we need to upgrade the database but that's planned next year.

How can we achieve this with minimal effort?

Thanks in advance for your help

xmlgen weird error

A reader, October 20, 2011 - 9:24 am UTC

Hi Tom,
We are running oracle 10.2.0.5 on aix in a RAC environment.
I am running into this strange error when I try to create an
xml file using the dbms_xmlgen package:

ORA-19202: Error occurred in XML processing
ORA-08103: object no longer exists

ORA-06512: at SYS.DBMS_XMLGEN, line 7
ORA-06512: at SYS.DBMS_XMLGEN, line 147
-----
This error doesnt happen all the time,and I have tried to run this isolated on one node as well as in the rac env.

the error happens at this line:
xmlclob := DBMS_XMLGEN.getXML(qryCtx);

please advise.
thanks a lot in advance

XML

aruna_erusadla@yahoo.com, December 08, 2011 - 10:58 am UTC

Hi Tom,

I am new to the XML in RDBMS .
I would like to ask the same questions in 2011 (11G) that were asked in 2002 (8i) .

Could you please explain (or) refer to the BEST approaches to do the below on 11g mainly ?

) How to generate XML from a standard table (EMP)?
b) How to save XML generated above in a table?
c) How to display at SQL prompt the same XML output from above query?
d) How to create DTD for the just generated XML?
e) How to use it once it is done?
f) What is XPath in relation to XML?
h) How to read XML file stored in hard disk (e.g C:\sajid\xmldoc)?
i) How to save the same XML in a table?



Many thanks

Tom Kyte
December 08, 2011 - 12:57 pm UTC

there is no single 'best way' for anything. the only thing I'll suggest here is getting familiar with your options - educate yourself on what is available - and then use that knowledge coupled with your knowledge of what you need to accomplish - and you will be able to figure out what is the best way.

Else - we don't really need you - we could just write a program that would be able to apply all of the technologies in a repeated fashion....


http://www.oracle.com/pls/db112/portal.all_books#index-XML

see the XML DB developers guide.

xml

A reader, December 08, 2011 - 10:41 pm UTC


@aruna_erusadla@yahoo.com on XML

Stew Ashton, December 09, 2011 - 4:05 am UTC


I just completed a study on this subject, so I would like to take a stab at these questions. There's always the chance someone will correct one of my errors...

) How to generate XML from a standard table (EMP)?
When you read the XML DB developers guide, look at the SQL/XML "publishing functions", especially XMLELEMENT, XMLFOREST, XMLAGG and XMLROOT. There is also XMLATTRIBUTES, but I don't bother with attributes.
WARNING: these functions will do implicit datatype conversions! They handle dates OK because there is an XML standard, but numbers and fractional seconds will use your session's decimal point, which can be either comma or period. This drove me crazy and prompted me to write a package that does explicit data conversions.

b) How to save XML generated above in a table?
Don't. Structured data should always be stored relationally. If I had to store semi-structured data as XML, I would look into the new "binary XML" format that comes with 11G.
If you mean how to convert XML to relational, read about XMLTABLE.

c) How to display at SQL prompt the same XML output from above query?
Just run the query?
If you mean how to "pretty print", use XMLSERIALIZE( content <your XML> indent)

d) How to create DTD for the just generated XML?
Don't. Use XML schemas, not DTD. I don't bother with XML schemas because I don't store structured data as XML. I use DDL and constraints just like always.

e) How to use it once it is done?
Don't. Store structured data relationally and let the constraints validate the data, then you don't need an XML schema to validate the data. I suppose you may want to communicate the XML schema to someone you send the data to, in which case I can't help you.

f) What is XPath in relation to XML?
XML is just the data, formatted as character strings and described by beginning and end tags. XPath is a way of navigating in the XML. For example, '//EMP' refers to any EMP element anywhere in the XML document, whether it is a root element or nested.

h) How to read XML file stored in hard disk (e.g C:\sajid\xmldoc)?
If it's huge, don't. To load very large XML documents, the only way I found was to use XSLT transformation to create a CSV file. If it's not huge, try DBMS_XSLPROCESSOR.READ2CLOB. To convert a CLOB to an XMLTYPE, use
XMLTYPE(<clob>, null, 1, 1)
i) How to save the same XML in a table?
See b). If it can be stored relationally, use XMLTABLE to go from XML to result set.

Hope this helps.

Found suggestion for generating XML schema

Stew Ashton, December 09, 2011 - 4:14 am UTC

generating xml from oracle table

michael, January 02, 2012 - 11:37 pm UTC

Hi Tom,

I am trying to execute your script after installing oracle 10g express edition, sql developer, xdk for 10g, and getting following error:


ORA-06550: line 2, column 14:
PLS-00201: identifier 'DBMS_XMLQUERY.CTXHANDLE' must be declared


var g_clob clob;

declare
l_ctx dbms_xmlquery.ctxHandle;
l_clob clob;
begin
l_ctx := dbms_xmlquery.newContext('select * from scott.emp');
dbms_lob.createtemporary(:g_clob,true,dbms_lob.session);
:g_clob := dbms_xmlquery.getXml(l_ctx);
end;
/
Tom Kyte
January 03, 2012 - 11:39 am UTC

dbms_xmlquery stuff needs java, XE does not have java support in the database, this is not available.

dbms_xmlgen is available however.

Creating and Validating XML in SQL

David, July 12, 2012 - 4:09 am UTC

Hi Tom,

I am using Oracle advanced queueing with an XML payload. I am trying to create an XML message based on an XML template and populate it with data from table(s). I also need to validate the XML output against the template/stylesheet. Below is a rudimentary example of what I am trying to do. My issue is how do I populate the template with the retrieved table data???? Can I do this without having to create an XML schema? Do I create the XML in the SELECT statement (using DBMS_XML routines) and if so, how can I validate that against a template/stylesheet before placing on the queue. Any assistance you could give would be much appreciated. Many thanks in advance.

CREATE TABLE books
(id NUMBER
,title varchar2(50)
,author varchar2(50)
,publication_date DATE)

CREATE OR REPLACE FUNCTION submissionXML( id NUMBER ) RETURN CLOB IS
query VARCHAR2(100);
BEGIN
query := 'SELECT *
FROM books
WHERE id = '||id;

RETURN xmlgen.getXML(query);

END;

DROP TABLE stylesheet_tab;
CREATE TABLE stylesheet_tab (id NUMBER, stylesheet XMLType);

INSERT INTO stylesheet_tab
VALUES (1,
XMLType('<?xml version=''1.0''?>
<xsl:stylesheet xmlns:xsl=" http://www.w3.org/TR/WD-xsl" >
<xsl:template match="/">
<HTML>
<BODY>
<h1>BOOKS</h1>
<xsl:for-each select="BOOKS/BOOK">
Rule #
<xsl:value-of select="@id" />

Title of book:
<xsl:value-of select="title" />

Author of book :
<xsl:value-of select="author" />

</xsl:for-each>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>'))

Need help with dbms_xmlquery.getxml

A reader, August 08, 2012 - 4:10 pm UTC

I have a query whose headers are stored in an XML file in DB which will be used by Java application.

My problem is that dbms_xmlquery.getxml() doesn't work ok with Chinese data and stored xml is corrupted. Please help


create table testingXMLgen (xml_data NCLOB);

declare
l_ctx dbms_xmlquery.ctxtype;
l_xml_schema NCLOB;
begin
l_ctx := dbms_xmlquery.newcontext('SELECT * FROM (SELECT "SEC","coretypeid","coreid","Record Type","您ID" FROM (SELECT "您MultiCurrency Object".coreid "SEC","您MultiCurrency Object".coretypeid "coretypeid", "您MultiCurrency Object".coreid "coreid", "您MultiCurrency Object".CoreTypeAssocAliasDescr "Record Type", "您MultiCurrency Object".F_3441961635 "您ID"
FROM ( SELECT SUBSTR(RFN_FlexSQL_Cache.GetCTAliasDescr(CORETYPEID,3441940070,3443315783),0,50) CoreTypeAliasDescr,
SUBSTR(RFN_FlexSQL_Cache.GetCTAssocAliasDescr(CORETYPEID,3441940070,3443315783),0,50) CoreTypeAssocAliasDescr,
SUBSTR(RFN_FlexSQL_Cache.GetCTDescr(CORETYPEID,3441940070,3443315783),0,50) COREDESCRIPTION,
COREID, CORETYPEID ,F_3441961635 FROM CT_3420185025
WHERE coretypeid IN (3420185025)) "您MultiCurrency Object" )) WHERE 1 = 2');
dbms_xmlquery.setraiseexception(l_ctx, TRUE);
dbms_xmlquery.setsqltoxmlnameescaping(l_ctx, TRUE);
-- dbms_xmlquery.setdateformat(l_ctx, l_xml_sformat);
dbms_xmlquery.setencodingtag(l_ctx, 'UTF-8');
l_xml_schema := dbms_xmlquery.getxml(l_ctx,2);
insert into testingXMLgen values (l_xml_schema);
-- :xml_data := dbms_xmlquery.getxml(l_ctx);
dbms_xmlquery.closecontext(l_ctx);
end;
/

Variable output in XMLGEN

Tony Keller, June 12, 2013 - 8:13 pm UTC

Hi Tom,

I have a table that looks like:
ID PhoneType PhoneNumber
Tom HOME 555-555-5501
Tom CELL 555-555-5502
Tom WORK 555-555-5503
Mark Cell 555-555-6601
Tim HOME 555-555-7701
Tim PERM 555-555-7705

Using XMLGen I want the query to return:
<PEOPLE>
<PERSON>
<ID>Tom</ID>
<PHONE>
<PhoneType>HOME</PhoneType>
<PhoneNumber>555-555-5501</PhoneNumber>
</PHONE>
<PHONE>
<PhoneType>CELL</PhoneType>
<PhoneNumber>555-555-5502</PhoneNumber>
</PHONE>
<PHONE>
<PhoneType>WORK</PhoneType>
<PhoneNumber>555-555-5503</PhoneNumber>
</PHONE>
</PERSON>
<PERSON>
<ID>Mark</ID>
<PHONE>
<PhoneType>CELL</PhoneType>
<PhoneNumber>555-555-6601</PhoneNumber>
</PHONE>
</PERSON>
<PERSON>
<ID>Tom</ID>
<PHONE>
<PhoneType>HOME</PhoneType>
<PhoneNumber>555-555-7701</PhoneNumber>
</PHONE>
<PHONE>
<PhoneType>PERM</PhoneType>
<PhoneNumber>555-555-7705</PhoneNumber>
</PHONE>
</PERSON>
</PEOPLE>

The problem is that I have an unknown number of outputs at run time. On the other hand SQL doesn't support an unknown number of output columns at run time, so how do I pass a query to XMLGEN to get this output.

Rewrite Extract query using XMLQuery for retrieving data from XMLType column

Sunny, August 31, 2013 - 8:23 am UTC

Hi,

I have a table having one columns as XMLTYPE being stored with Object-Relational storage. Below is table ddl.

    CREATE TABLE Orders ( Order_id number not null,
                          Order_status Varchar2(100),
                          Order_desc XMLType not null)
                          XMLTYPE Order_desc STORE AS OBJECT RELATIONAL
                          XMLSCHEMA "http://localhost/public/xsd/Orderstore.xsd"
                          ELEMENT "OrderVal";

I have successfully registered the schema to load XSD with XML DB. Below is the XML being loaded into the XMLTYPE column.
    <?xml version="1.0" encoding="utf-8"?>
    <draftorders>
    <OrderSumm>
    <Ordercod>OrderBookings</Ordercod>
    </OrderSumm>
    <Orderattrs>
    <Orderattr Ordername="Order Name">
    <OrderVal>
    <listvalue>Node1_Child1_OrderValue_1</value>
    <Orderattrs>
    <Orderattr Ordername="Node2_Child1">
    <OrderVals>
    <OrderVal>
    <listvalue>Node2_Child1_OrderValue_1</value>
    </OrderVal>
    </OrderVals>
    </Orderattr>
    <Orderattr Ordername="Node2_Child2">
    <OrderVals>
    <OrderVal>
    <listvalue>Node2_Child2_OrderValue_1</value>
    </OrderVal>
    </OrderVals>
    </Orderattr>
    </Orderattrs>
    </OrderVal>
    </OrderVals>
    </Orderattrs>
    </OrderVal>
    </OrderVals>
    </draftorders>

I have the query using "extract" to print the below output:
    SELECT     extract(o.Order_desc,'/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[0]/@Ordername').getStringVal() "Node1",      extract(o.Order_desc,'/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[0]/OrderVals/OrderVal[1]/listvalue/text()').getStringVal() "Node1Child",       extract(o.Order_desc,'/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[1]/@Ordername').getStringVal() "Node2",     extract(c.Order_desc,'/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/listvalue/text()').getStringVal() "Node2Child"
       FROM Orders o;

       OUTPUT:-               
       Node2_Child1
       Node2_Child1_OrderValue_1
       Node2_Child2
       Node2_Child2_OrderValue_1


I want to achieve the same output using XMLQuery, but I am unable to build query to print the child node. Till now, I can only print the node value using XMLQuery as given below:-
    SELECT XMLQuery('/OrderVal[1]/Orderattrs/Orderattr[1]/OrderVals/OrderVal[1]/Orderattrs/Orderattr[0]/@Ordername'
                 PASSING o.Order_desc RETURNING CONTENT
               )
     FROM Orders o;

How can I achieve the same output from using "extract", with "XMLQuery" ?

Thanks.

create xml file

hardik, February 19, 2014 - 7:18 pm UTC

I have a table emp which column are ID , Name, Address,phone
I want a Store procedure to create .xml file in directory
format is below
<?xml version="1.0" encoding="UTF-8"?>
<SequenceData xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="SequenceData.xsd">
<emp>
<ID>1</ID>
<Name>ravi</Name>
<Address>7th road, villa</Address>
<phone>001210012</phone>
</emp>
<emp>
<ID>1</ID>
<Name>ravi</Name>
<Address>7th road, villa</Address>
<phone>001210012</phone>
</emp>
<emp>
<ID>2</ID>
<Name>jone</Name>
<Address>6th road, villa</Address>
<phone>00121012</phone>
</emp>
<emp>
<ID>3</ID>
<Name>ravi</Name>
<Address>8th road, villa</Address>
<phone>0012032512</phone>
</emp>
...........

Great Support

Rupesh, August 20, 2015 - 9:21 am UTC

Hi Tom,

Am keep following your answers and it is very helpful for me and every one. Now am stuck with one design.

<?xml version="1.0" encoding="UTF-8" ?>
<ConsPurchList>
<ConsPurch>
<ConsPurchHdr>
<RegNum></RegNum>
<TranDate></TranDate>
<ConsPurchDtl>
<LinNum></LinNum>
<DocDate></DocDate>
<Qty></Qty>
</ConsPurchDtl>
<ConsPurchDtl>
<LinNum></LinNum>
<DocDate></DocDate>
<Qty></Qty>
</ConsPurchDtl>
</ConsPurchHdr>
</ConsPurch>
<ConsPurch>
<ConsPurchHdr>
<RegNum></RegNum>
<TranDate></TranDate>
<ConsPurchDtl>
<LinNum></LinNum>
<DocDate></DocDate>
<Qty></Qty>
</ConsPurchDtl>
<ConsPurchDtl>
<LinNum></LinNum>
<DocDate></DocDate>
<Qty></Qty>
</ConsPurchDtl>
<ConsPurchList>
How to read this file and insert into table using V_node..

<RegNum></RegNum><TranDate></TranDate> is common for both details . these values can be duplicate but not teh detail.

special character being truncated

spur, July 17, 2019 - 3:44 pm UTC

I have following test case in sqllive

https://livesql.oracle.com/apex/livesql/s/ioon3s8ehchaib1n4jmzr4nrg

If special character exist, output is being truncated from the end. I have tested it in livesql, 12.1.0.2.

I was not able to replicate the behavior when database had single byte encoding. Could you please advise.

Connor McDonald
July 25, 2019 - 3:05 am UTC

That looks like a bug to me. Casting to varchar2 also assists

DECLARE 
   xml_val        clob; 
   char_val       varchar2(100);
BEGIN 
    FOR i IN (SELECT   XMLSERIALIZE (DOCUMENT xmlcol AS CLOB) AS val 
               FROM   tmp_rpt) 
    LOOP 
      xml_val := i.val; 
      select cast(xml_val as varchar2(50)) into char_val from dual;
      DBMS_OUTPUT.put_line ('' || xml_val); --return correct value 
      DBMS_OUTPUT.put_line (xml_val);       -- truncates 2 characters from left 
      DBMS_OUTPUT.put_line (char_val);      --return correct value 
   END LOOP; 
END; 
/


I'll log a bug

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here