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.
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.
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
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,
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,
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 !!
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
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
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
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?
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!
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...
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!
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...
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!
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?
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
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!
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
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
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
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
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
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
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.
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.
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?
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
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.
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
A reader
A, September 01, 2005 - 8:50 am UTC
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?
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.
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...
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
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
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
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
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
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.
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.
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.
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.
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.
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.
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'
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
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.
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?
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
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?
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?
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.
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
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
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,
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
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?
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?
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
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.
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
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;
/
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
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