Generate XML in desired format
Geetha, August 14, 2002 - 12:38 pm UTC
Amazing... Thanks a lot for the help.
Generate XML in desired format
Nancy, August 14, 2002 - 12:48 pm UTC
Tom,
How to parse data in this format to retrieve back the canonical oracle form to insert into tables?
August 14, 2002 - 4:14 pm UTC
Hi, Sean here again...
If I understand you correctly, you want to know how to use Oracle to take an XML file and use it to insert rows into tables. To do this, we use either the DBMS_XMLSave package (PL/SQL) or the oracle.xml.sql.dml.OracleXMLSave class (Java). In these libraries, there are procedures, functions and methods to insert, update and delete rows of relational and object-relational rows based on an XML document.
There is a "canonical" format that must be used to insert data into a table using the XML SQL Utility. To find this canonical format, use a PL/SQL block such as:
SQL> connect scott/tiger
Connected.
SQL> set long 5000
SQL> var g_xml clob
SQL> declare
2 l_ctx dbms_xmlquery.ctxtype;
3 begin
4 l_ctx := dbms_xmlquery.newcontext('select * from emp where rownum=1');
5 :g_xml := dbms_xmlquery.getxml(l_ctx);
6 dbms_xmlquery.closecontext(l_ctx);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> print g_xml
G_XML
--------------------------------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980 0:0:0</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
</ROWSET>
Obviously, in line 4 above, you'd need to replace the query with a select from the table you are interested in, not EMP. So let's INSERT a row into EMP using the XML SQL Utility:
SQL> select empno, ename
2 from emp
3 where empno > 9000
4 /
no rows selected
SQL> declare
2 l_xml clob;
3 l_rows pls_integer;
4 l_ctx dbms_xmlsave.ctxtype;
5 begin
6 l_xml :=
7 '<?xml version="1.0"?>
8 <ROWSET>
9 <ROW num="1">
10 <EMPNO>9999</EMPNO>
11 <ENAME>DILLON</ENAME>
12 <JOB>JANITOR</JOB>
13 <MGR>9998</MGR>
14 <HIREDATE>01/01/1990 0:0:0</HIREDATE>
15 <SAL>1</SAL>
16 <DEPTNO>20</DEPTNO>
17 </ROW>
18 </ROWSET>';
19 -- identify the table were inserting into
20 l_ctx := dbms_xmlsave.newcontext('EMP');
21
22 -- perform the insert, capture the rows inserted
23 l_rows := dbms_xmlsave.insertxml(l_ctx, l_xml);
24
25 -- close the context, clean up resources
26 dbms_xmlsave.closecontext(l_ctx);
27
28 dbms_output.put_line(l_rows || ' rows inserted...');
29 end;
30 /
1 rows inserted...
PL/SQL procedure successfully completed.
SQL> select empno, ename
2 from emp
3 where empno > 9000
4 /
EMPNO ENAME
---------- ----------
9999 DILLON
For more information on how to do DML using XML and the XML SQL Utility, see:
XDK Developer's Guide Table of Contents:
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96621/toc.htm
XDK Developer's Guide XML SQL Utility Chapter:
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96621/adx08xsu.htm#1000433
Hope that helps!
_smd_
_____________________________________________________________________
Sean Dillon - Senior Technologist, Oracle Corporation
Author "Beginning Oracle Programming"
http://www.amazon.com/exec/obidos/ASIN/186100690X
how to insert if XML not in canonical form
Nancy, August 14, 2002 - 5:24 pm UTC
Sean,
Am sorry for being unclear.How do I form a canonical XML?Say this is the XML I have and I need to insert data in the two tables that you have created.
<?xml version=''1.0''?>
<MASTER>
<INV_NO>1</INV_NO>
<INV_DATE>13-AUG-02</INV_DATE>
<DETAILS>
<ROWSET>
<DETAIL>
<ITEM_NO>1</ITEM_NO>
<QTY>1</QTY>
</DETAIL>
<DETAIL>
<ITEM_NO>2</ITEM_NO>
<QTY>1</QTY>
</DETAIL>
<DETAIL>
<ITEM_NO>2</ITEM_NO>
<QTY>2</QTY>
</DETAIL>
</ROWSET>
</DETAILS>
</MASTER>
August 15, 2002 - 8:55 am UTC
Nancy,
Typically, you will have to transform your XML data into Oracle's canonical XML format. The standard way of doing this is via XML Stylesheet Language Transformations (XSLT). Using XSLT, you can take one input XML document, transform it using what's known as an XSL stylesheet, and make it look like some other XML document. For more information about XSLT, see:
http://www.xml.com/pub/a/2000/08/holman/index.html
That being said, the way we do multi-table inserts into XML is a bit tricky. To do it, we need to use Oracle's object-relational capabilities. We'll have to define an object view (which simulates data stored in a user-defined object, such as our MASTER object we used in this questions answer). Using this object-view, we can both query XML data OUT of the database and insert XML data INTO the database. Let's take this a step at a time...
First, create the object view. Using the types we created earlier (MASTER, DETAIL, DETAILS), we can define an object view as such:
SQL> create or replace view inventory_view of master
2 with object id (inv_no) as
3 select MASTER(m.inv_no, m.inv_date,
4 cast(multiset(select d.item_no, d.qty
5 from inv_detail d
6 where d.inv_no = m.inv_no) as DETAILS))
7 from inv_master m
8 /
View created.
This will let us query from this view (i.e., "select * from inventory_view") and get OBJECTS in our result set instead of standard relational rows. If we do this query in SQL*Plus, it will actually show us the object the best way it knows how:
sdillon@FUNK92.US.ORACLE.COM> select * from inventory_view;
INV_NO INV_DATE INV_DETAIL(ITEM_NO, QTY)
---------- --------- -------------------------------------------------
1 14-AUG-02 DETAILS(DETAIL(1, 1), DETAIL(2, 1), DETAIL(2, 2))
...but let's not get wrapped up in that right now... this is simply a means to an end, inserting complex XML into the database. Next, let's create our "INSTEAD-OF TRIGGER" that will be used for inserting relational rows into the correct tables whenever an object is "inserted" into this object view:
SQL> create or replace trigger inventory_view_bifer
2 instead of insert on inventory_view
3 begin
4 insert into inv_master (inv_no, inv_date)
5 values (:new.inv_no, :new.inv_date);
6
7 for i in 1 .. :new.inv_detail.count loop
8 insert into inv_detail(inv_no, item_no, qty)
9 values (:new.inv_no, :new.inv_detail(i).item_no,
10 :new.inv_detail(i).qty);
11 end loop;
12 end;
13 /
Now, whenever we insert a row into this object view, the rows are inserted into the underlying tables appropriately:
SQL> select m.inv_no, m.inv_date, d.item_no, d.qty
2 from inv_master m, inv_detail d
3 where m.inv_no = d.inv_no
4 /
INV_NO INV_DATE ITEM_NO QTY
---------- --------- ---------- ----------
1 14-AUG-02 1 1
1 14-AUG-02 2 1
1 14-AUG-02 2 2
sdillon@FUNK92.US.ORACLE.COM> insert into inventory_view
2 values (2,sysdate,details(detail(10,10),detail(20,20)));
1 row created.
SQL> select m.inv_no, m.inv_date, d.item_no, d.qty
2 from inv_master m, inv_detail d
3 where m.inv_no = d.inv_no
4 /
INV_NO INV_DATE ITEM_NO QTY
---------- --------- ---------- ----------
1 14-AUG-02 1 1
1 14-AUG-02 2 1
1 14-AUG-02 2 2
2 14-AUG-02 10 10
2 14-AUG-02 20 20
So, using this concept, you can use the XML SQL Utility to insert an XML document (that looks like an "INVENTORY OBJECT") into INVENTORY_VIEW. This will then insert the appropriate records into the underlying table as you saw above. To figure out what an "INVENTORY OBJECT" looks like in XML, we use the test I described above:
SQL> var g_xml clob
SQL> declare
2 l_ctx dbms_xmlquery.ctxtype;
3 begin
4 l_ctx := dbms_xmlquery.newcontext('select * from inventory_view where rownum=1');
5 :g_xml := dbms_xmlquery.getxml(l_ctx);
6 dbms_xmlquery.closecontext(l_ctx);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> print g_xml
G_XML
----------------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<INV_NO>1</INV_NO>
<INV_DATE>8/14/2002 20:41:33</INV_DATE>
<INV_DETAIL>
<INV_DETAIL_ITEM>
<ITEM_NO>1</ITEM_NO>
<QTY>1</QTY>
</INV_DETAIL_ITEM>
<INV_DETAIL_ITEM>
<ITEM_NO>2</ITEM_NO>
<QTY>1</QTY>
</INV_DETAIL_ITEM>
<INV_DETAIL_ITEM>
<ITEM_NO>2</ITEM_NO>
<QTY>2</QTY>
</INV_DETAIL_ITEM>
</INV_DETAIL>
</ROW>
</ROWSET>
So let's use a PL/SQL block to simulate storing this XML document into the database using the XML SQL Utility:
SQL> declare
2 l_xml clob;
3 l_rows pls_integer;
4 l_ctx dbms_xmlsave.ctxtype;
5 begin
6 l_xml :=
7 '<?xml version="1.0"?>
8 <ROWSET>
9 <ROW num="1">
10 <INV_NO>2</INV_NO>
11 <INV_DATE>8/30/2002 20:41:33</INV_DATE>
12 <INV_DETAIL>
13 <INV_DETAIL_ITEM>
14 <ITEM_NO>10</ITEM_NO>
15 <QTY>10</QTY>
16 </INV_DETAIL_ITEM>
17 <INV_DETAIL_ITEM>
18 <ITEM_NO>20</ITEM_NO>
19 <QTY>20</QTY>
20 </INV_DETAIL_ITEM>
21 <INV_DETAIL_ITEM>
22 <ITEM_NO>30</ITEM_NO>
23 <QTY>30</QTY>
24 </INV_DETAIL_ITEM>
25 </INV_DETAIL>
26 </ROW>
27 </ROWSET>';
28
29 -- identify the table were inserting into
30 l_ctx := dbms_xmlsave.newcontext('INVENTORY_VIEW');
31
32 -- perform the insert, capture the rows inserted
33 l_rows := dbms_xmlsave.insertxml(l_ctx, l_xml);
34
35 -- close the context, clean up resources
36 dbms_xmlsave.closecontext(l_ctx);
37
38 dbms_output.put_line(l_rows || ' rows inserted...');
39 end;
40 /
1 rows inserted...
PL/SQL procedure successfully completed.
...resulting in:
SQL> select * from inventory_view;
INV_NO INV_DATE INV_DETAIL(ITEM_NO, QTY)
---------- --------- -------------------------------------------------
1 15-AUG-02 DETAILS(DETAIL(1, 1), DETAIL(2, 1), DETAIL(2, 2))
2 30-AUG-02 DETAILS(DETAIL(10, 10), DETAIL(20, 20), DETAIL(30, 30))
... or more directly:
SQL> select m.inv_no, m.inv_date, d.item_no, d.qty
2 from inv_master m, inv_detail d
3 where m.inv_no = d.inv_no
4 /
INV_NO INV_DATE ITEM_NO QTY
---------- --------- ---------- ----------
1 15-AUG-02 1 1
1 15-AUG-02 2 1
1 15-AUG-02 2 2
2 30-AUG-02 10 10
2 30-AUG-02 20 20
2 30-AUG-02 30 30
6 rows selected.
Hope that helps!
_smd_
_____________________________________________________________________
Sean Dillon - Senior Technologist, Oracle Corporation
Author "Beginning Oracle Programming"
http://www.amazon.com/exec/obidos/ASIN/186100690X
Generate XML in desired format in Oracle 8.1.7 DB
Staline Xavier, October 31, 2002 - 9:03 am UTC
Hi Tom,
The information that you provided for "generate XML in desired format using PL/SQL" was helpful for me.
I am also having exactly similar DB structure in my Oracle 8.1.7 DB. When i was tring your solution in this DB, i was getting the following error;
SQL> ed
Wrote file afiedt.buf
1 select sys_xmlgen(
2 MASTER(m.inv_no, m.inv_date,
3 cast(multiset(select d.item_no, d.qty
4 from inv_detail d
5 where d.inv_no = m.inv_no) as DETAILS)
6 )).getClobVal() as XML_QUERY
7* from inv_master m
SQL> /
)).getClobVal() as XML_QUERY
*
ERROR at line 6:
ORA-00923: FROM keyword not found where expected
SQL>
The following website says that sys_xmlgen() is a new feature in Oracle 9i.
So, I tried the same select query using xmlgen.getxml(), as follows;
CREATE TABLE SAMPLE_XML (
RID NUMBER,
XML_CONTENT CLOB)
/
declare
x varchar2(500);
begin
x:='select MASTER(m.inv_no, m.inv_date,cast(multiset(select d.item_no, d.qty from inv_detail d where d.inv_no = m.inv_no) as DETAILS)) from inv_master m';
INSERT INTO sample_xml VALUES(1,XMLGEN.GETXML(x,0));
EXCEPTION
when others then
dbms_output.put_line(sqlcode||' '||SQLERRM);
END;
/
The following content has been stored in "xml_content" column in sample_xml table, instead of generating the xml document.
<?xml version = '1.0'?>
<ERROR>oracle.xml.sql.OracleXMLSQLException: Character ')' is not allowed in an XML tag name.</ERROR>
Can you please tell me where i went wrong. And can you suggest me how i can generate xml from the about query?
Staline X.
Staline Xavier, October 31, 2002 - 9:18 am UTC
Error using Nested Cursor
Bharath, November 15, 2002 - 6:32 pm UTC
import java.sql.*;
import java.math.*;
import oracle.xml.sql.query.*;
import oracle.jdbc.*;
import oracle.jdbc.driver.*;
public class GenXMl
{
public GenXMl()
{
}
public static void main(String args[]) throws SQLException
{
String tabName = "USER_TBL";
String user = "comps/comps";
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@ds101.dev.global.chase.com:1521:gib_dev7","comps","comps");
OracleXMLQuery qry = new OracleXMLQuery(conn,"select user_id,CURSOR(SELECT USER_ROLE_ID FROM USER_ROLE_XREF WHERE USER_ROLE_XREF.USER_ID = USER_TBL.USER_ID) FROM "
+"USER_TBL where rownum < 5" );
String xmlString = qry.getXMLString();
System.out.println(" OUTPUT IS XML DATA :\n"+xmlString);
// Close the JDBC connection
conn.close();
qry.close();
}
}
When i try to run this i am getting
<ERROR>oracle.xml.sql.OracleXMLSQLException: Character ')' is not allowed in an XML tag name.</ERROR>
Other Details:
Using :Oracle 9i Jdeveloper Version 9.0.3
Database:8.1.7
OS:win 2000
xml conversion
Binu, April 25, 2003 - 6:29 am UTC
i am using oracle production database 8.1.5 and development database 8.1.6 .will i be able to convert the oracle data to xml and vice-versa(i.e.) data present in xml to oracle database in any one of the database .if so please can u guide me.Probably if i get the solution in 8.1.5 it will be of great use
Thank u
April 25, 2003 - 8:00 am UTC
you are hopelessly lost here.
there is no way you can effectively, efficiently, or anyway develop on 816 and deploy to 815.
You have no way to test a single thing.
This is beyond bad, way beyond bad.
You better get 815 installed in development and test (yes, you do need a test system) and then you can figure out what is possible. otn.oracle.com has the xdk for xml, there are some things that will work in 815.
better yet, get your systems onto supported and supportable releases (eg: 8174 at least)
Getting Error: ORA-00904: "XMLGEN"."GETXML": invalid identifier
Dilip, April 25, 2003 - 1:48 pm UTC
SQL> SELECT * FROM INV_MASTER;
INV_NO INV_DATE
---------- ---------
1 25-APR-03
SQL> SELECT * FROM INV_DETAIL;
INV_NO ITEM_NO QTY
---------- ---------- ----------
1 1 1
1 2 1
1 3 1
SQL> select xmlgen.getxml('select m.inv_no, m.inv_date, d.item_no, d.qty from
2 inv_master m, inv_detail d
3 where m.inv_no = d.inv_no') from dual;
select xmlgen.getxml('select m.inv_no, m.inv_date, d.item_no, d.qty from
*
ERROR at line 1:
ORA-00904: "XMLGEN"."GETXML": invalid identifier
SQL>
April 25, 2003 - 3:47 pm UTC
load it in then, otn.oracle.com -> technology -> xml.
not knowing your version et. al. makes things hard, but go there and try to get it loaded up.
any limitation on using oracle's xml utility ?
reader, April 26, 2003 - 2:15 am UTC
tom,
Is there any limitations in creating xml file in oracle 9.0.2. I have three tables each tables might have records of 15000 perday, once xml is generated i shall be deleting the records, this happens at off peak hours, and again down 1 year the records might factor to 30000 perday.
Do you see any potential limitation on using oracle's xml utility and as well on the above scenarion.
Thanks for your terrific job.
April 26, 2003 - 8:25 am UTC
benchmark it. it the only way to be sure it'll meet your needs regardless of what anyone says
xml conversion
Binu, April 28, 2003 - 7:38 am UTC
suppose if i have to use oracle 8.1.5 how to go about?? in converting
the data from oracle to xml as well as xml to oracle
can i do the feature in JAVA i.e. can u provide a solution of getting the data
stored in XML file to oracle.
<?xml version ='1.0'?>
<EMPLOYEE>
<EMPDATA>
<EMPNO> 7369 </EMPNO>
<ENAME> SMITH </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7499 </EMPNO>
<ENAME> ALLEN </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7521 </EMPNO>
<ENAME> WARD </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7566 </EMPNO>
<ENAME> JONES </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7654 </EMPNO>
<ENAME> MARTIN </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7698 </EMPNO>
<ENAME> BLAKE </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7782 </EMPNO>
<ENAME> CLARK </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7788 </EMPNO>
<ENAME> SCOTT </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7839 </EMPNO>
<ENAME> KING </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7844 </EMPNO>
<ENAME> TURNER </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7876 </EMPNO>
<ENAME> ADAMS </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7900 </EMPNO>
<ENAME> JAMES </ENAME>
</EMPDATA>
<EMPDATA >
<EMPNO> 7902 </EMPNO>
<ENAME> FORD </ENAME>
</EMPDATA>
<EMPDATA>
<EMPNO> 7934 </EMPNO>
<ENAME> MILLER </ENAME>
</EMPDATA>
</EMPLOYEE>
April 28, 2003 - 8:30 am UTC
goto the otn.oracle.com website -> technologies -> XML there are java sdks there that you can use in 815 either inside or outside the database.
On 8.1.7
A reader, January 28, 2004 - 2:34 am UTC
Dear Tom/ Sean,
Please have another look at the Staline Xavier's question above, if we do have SYS_XMLGEN() available, what option do we have to get a single XML per row of data selected?
The example taken above on Oracle 8.1.7 database, how to get the same output as with sys_xmlgen on Oracle 9i?
Many thanks
January 28, 2004 - 8:28 am UTC
just select more than one row? our example has but one master row. put two in there.
there is just one row selected here -- the master.
Didn't understand you for a change!
A reader, February 02, 2004 - 8:10 am UTC
Dear Tom,
The XML in 8.1.7 is generated like this
<?XML version='1.0'?>
<ROWSET>
<ROW>
...
</ROW>
<ROW>
...
</ROW>
...
<ROWSET>
This includes all the rows that the query will output.
The requirement is to have like this:
<?XML version='1.0'?>
<ROW>
...
</ROW>
<?XML version='1.0'?>
<ROW>
...
</ROW>
...
That is one XML per row selected. This can be shown to be achived in Oracle 9.2 using SYS_XMLGEN
My request to you is to guide me for a solution on 8.1.7
Regards,
February 02, 2004 - 9:22 am UTC
sorry -- i read your text:
... if we do have
SYS_XMLGEN() available, what option do we have to get a single XML per row of
data selected? ....
and sort of "assumed" you meant what you typed.
In 8i, you'd probably be using a bit of plsql procedural code and dbms_xmlquery.
documentation
Diana, February 04, 2004 - 8:18 am UTC
Does anyone have a suggestion?
When I click on the link for the documentation that you provide, I am redirected to the general index documentation page, at which point I have no idea where to go.
What am I doing wrong?
February 04, 2004 - 8:41 am UTC
generate or process xml file size limit using dbms package.
Richard, February 25, 2004 - 1:06 pm UTC
Hi Tom,
How are you doing today. I remember that oracle dbms_xmlsave, dbms_xmlquery or dbms_xmlgen by default use DOM parser, which will parse the whole xml document into memory, we are about to generate a file over 50,000 typical records?
please advise what will be the size limit for processing (import) or generating (export) an external xml file using dbms_xmlsave, dbms_xmlquery?
Can I elect to choose SAX parser instead of DOM parser through Oracle Package?
Please advise. Thanks for help.
March 08, 2004 - 12:32 pm UTC
I asked Sean Dillon, our XML technologist, to take a look at this and here's what he had to say:
------------------
What version of the database are you using? Keep in mind, when you use the DOM parser to put entire documents into memory, memory starts to REALLY get taxed as documents get larger and larger. I have not benchmarked this operation, I'd encourage you to try a couple of different approaches.
First, if you're on Oracle9i Release2 or higher, don't use any of the DBMS_* packages at all! Instead, use SQLX. SQLX is a set of sql operators you can use in a query to generate XML documents/fragments. I STRONGLY URGE YOU to search for SQLX on AskTom for examples and explanations of SQLX. There's no need to keep rewriting the explanation, just look it up and check it out. You can read all about it in the Oracle9iR2 Database Developer's Guide for XML DB as well (Oracle Documentation can be found on OTN).
The next step I'd try, if you are generating XML documents to be shared with others and you're NOT on 9iR2, and the document will not need to be parsed or whatnot in the transaction you're generating the document from... is basic PL/SQL. Write a custom PL/SQL function to create an XML document programatically in a CLOB, and use the results of the CLOB as your XML document. There's more work to be done here, but there's NO reason to incur that huge in-memory DOM tree if you can avoid it (which is what you'd be using with some of the DBMS_ packages.
Finally, if SQLX or a programattic PL/SQL approach don't work for you, THEN try dbms_xmlquery or dbms_xmlgen. These are what I'd consider last resorts if the other options don't work. Try the other approaches, first! :)
Hope that helps!
suppress tags
Mark, November 16, 2004 - 3:53 pm UTC
In your example, how can I suppress the <ROWSET> and <DETAIL> tags?
Thanks.
SQL> select xmlelement(
2 "MASTER",
3 xmlforest(
4 m.inv_no, m.inv_date,
5 (select sys_xmlagg(
6 xmlelement(
7 "DETAIL",
8 xmlforest(item_no, qty)
9 )
10 )
11 from inv_detail d
12 where d.inv_no = m.inv_no
13 ) "DETAILS"
14 )
15 ) as "XML_QUERY"
16 from inv_master m
17 /
XML_QUERY
--------------------------------------------------------------
<MASTER>
<INV_NO>1</INV_NO>
<INV_DATE>13-AUG-02</INV_DATE>
<DETAILS>
<ROWSET>
<DETAIL>
<ITEM_NO>1</ITEM_NO>
<QTY>1</QTY>
</DETAIL>
<DETAIL>
<ITEM_NO>2</ITEM_NO>
<QTY>1</QTY>
</DETAIL>
<DETAIL>
<ITEM_NO>2</ITEM_NO>
<QTY>2</QTY>
</DETAIL>
</ROWSET>
</DETAILS>
</MASTER>
Relating back to 1st question.
Ger, December 06, 2004 - 2:19 pm UTC
I am trying to generate XML for the following simplified queries:
Master query: Select a.item_code, description,
From table A, table B
Where a.item_code = :from_item
and a.item_code = :to_item;
Detail query: Select c.item_code, c.ingredient_item_code, b.name
from table C, table B, table A
where A.item_code = c.ingredient_item_code;
Select d.item_code, e.xml_element
from table D, table E, table F
where f.label_code = d.label_code
Select item_code, label_code
from table D
where label_code NOT IN (Select label_code
from table D, table E);
Results should ne similar(this is not exact just a small example):
<ItemCode>
<ProductName>
<Description>
<IngredientInfo>
<code>
<Name>
<Ingredientinfo>
<item_code>
Would the best solution be to create a view to incorporate all these queries or is there a way to create xml using the above queries?
December 07, 2004 - 9:40 am UTC
doesn't make sense as presented - where do the THREE details go? I see lots of attributes but no mention of how they fit into the xml there.
Ger, December 07, 2004 - 12:08 pm UTC
Hi,
It should look something like this:
<Product>
<ProductName> --Master query
<Description>
<IngredientInfo> --- 1st detail query
<code>
<Name>
<Ingredientinfo>
<IngredientInfo> --- 2nd detail query
<code>
<Name>
<Ingredientinfo>
<IngredientInfo> --- 3rd detail query
<code>
<Name>
<Ingredientinfo>
<Product>
Does that help?
December 07, 2004 - 8:28 pm UTC
so, basically -- a union all -- use the same technique as the original answer.
Ger, December 08, 2004 - 9:20 am UTC
Hi,
Sorry about the confusion:
But you can't use a union all if the elements of each select are different in the detail. And that is what I am trying to do.
The Master is a product and has attributes. One of the attributes is ingredient information.
So for the 1st detail it should generate elements for item, description and concentration from tables A, B,C
2nd detail - generate item, element name,number_value, meaning, alpha_value, date_value for item_properties(Table D) with corresponding value in properties map(Table E)
3rd detail - generate item, label for item_properties that does not have a corresponding record in the properties map table, i.e. tables D, E
<Product>
<ProductName> --Master query
<Description>
<IngredientInfo> --- 1st detail query
<item>
<Description>
<Concentration>
<Ingredientinfo>
<IngredientInfo> --- 2nd detail query
<Item>
<Name>
<number_Value>
<alpha_value>
<date_value>
<Ingredientinfo>
<IngredientInfo> --- 3rd detail query
<Item>
<Label>
<Ingredientinfo>
<Product>
December 08, 2004 - 10:46 am UTC
sure you can -- just select a consistent list of values, using NULL where appropriate to fill in the blanks.
More info
A reader, December 08, 2004 - 9:26 am UTC
So is there a way to create xml using the above queries? Can I create a master object and each detail objects? But looking at examples it shows emp/dept as a 1-1 relationship and I haven't seen any examples with 1-many?
December 08, 2004 - 10:47 am UTC
dept -> emp is 1:M
emp -> dept is M:1
it is not 1:1
and the original question was about a master detail? all of the examples were master detail?
Ger, December 13, 2004 - 1:37 pm UTC
Sorry I have been out but thanks for the response.
Yes, they are about master-> details. In the examples I have seen an object is created for the master and one for detail. You then replace a column in the master with the detail object. But since I have many details, is this the way to go?
Or maybe use xmlelement for each column in the master? And I've seen xmlforest, is this to get the attributes of this element?
I saw another example of maybe using dbms_xmldom.createElement(doc,'Item');
dbms_xmldom.setAttribute(item_element, 'item_code', item_rec.item_code);
Set each attribute for each element.
doc := dbms_xmldom.newdomdocument;
dbms_xmldom.setversion(doc,'1.0');
root_node := dbms_xmldom.makeNode(doc);
departments_element := dbms_xmldom.createElement(doc, 'deptartments' );
departments_node := dbms_xmldom.appendChild(root_node,dbms_xmldom.makeNode(departments_element));
for dept_rec in ( select * from dept where rownum < 3 ) Loop
department_element := dbms_xmldom.createElement(doc, 'dept' );
dbms_xmldom.setAttribute(department_element, 'deptno' , dept_rec.deptno );
dbms_xmldom.setAttribute(department_element, 'dname' , dept_rec.dname );
dbms_xmldom.setAttribute(department_element, 'location' , dept_rec.loc );
department_node := dbms_xmldom.appendChild(departments_node,dbms_xmldom.makeNode(department_element));
-- Employees
for emp_rec in ( select * from emp where deptno = dept_rec.deptno) Loop
emp_element := dbms_xmldom.createElement(doc, 'emp');
dbms_xmldom.setAttribute(emp_element, 'empno' , emp_rec.empno);
dbms_xmldom.setAttribute(emp_element, 'ename' , emp_rec.ename);
dbms_xmldom.setAttribute(emp_element, 'job' , emp_rec.job);
emp_node := dbms_xmldom.appendChild(department_node,dbms_xmldom.makeNode(emp_element));
emp_text := dbms_xmldom.createTextNode(doc, 'This is text for employee ' || emp_rec.empno );
emp_name_node := dbms_xmldom.appendChild(emp_node,dbms_xmldom.makeNode(emp_text));
end loop;
end loop;
My example would be: I would have Item Element (master cursor) which is the department here and set each attribute for this.
And for the employess(detail cursors) I would have three different For loops for each of my cursors. Is this viable?
I'm trying to find the best solution, txs for ur help
December 13, 2004 - 3:16 pm UTC
Hi Ger, Sean Dillon here.
A couple of quick points. Sure, you can have three different for loops using DOM to programatically add the elements and attributes as needed based on the output of the cursors. In my opinion, this is long winded and more code than I'd want to write when I can accomplish the same result using SQL.
With SQLXML in mind, I'd just write a single SQL query w/ the appropriate sqlxml operators
Try searching AskTom for "XMLAGG". Here's a sample query that does what you're looking for:
select xmlelement( "account" ,
xmlforest( acct_no as "ariaacctno" ),
( select xmlagg( xmlelement( "service",
xmlforest( feature_no as "serviceno",
feature_ind as "serviceprovind" )
)
)
from t
where t.acct_no = t2.acct_no
)
) xmldoc
from ( select distinct acct_no from t ) t2
/
...which results in...
<account>
<ariaacctno>1107</ariaacctno>
<service>
<serviceno>1</serviceno>
<serviceprovind>1</serviceprovind>
</service>
<service>
<serviceno>2</serviceno>
<serviceprovind>0</serviceprovind>
</service>
<service>
<serviceno>3</serviceno>
<serviceprovind>1</serviceprovind>
</service>
<service>
<serviceno>7</serviceno>
<serviceprovind>0</serviceprovind>
</service>
</account>
That one is here: </code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6912626759561 <code>
If you search on XMLAGG, you get a bunch of hits as what you're trying to do is pretty common. If you want to have three separate detail nodes, you can break out the XMLAGG operators into three separate queries. If you want to have one detail node, you can simply union the results in a nested query and use the xmlelements on the result set of the union. There are a variety of ways to do it. Try a few ways out to see if this gives you what you need.
Hope that helps, _smd_
Ger, December 13, 2004 - 3:52 pm UTC
Thanks, I will do that.
sqlx performance
phil, January 18, 2005 - 11:57 pm UTC
SQLX is i think pretty cool, however I am having a performance issue with the below query, using SQLX.
The database is not yet large, stats are up to date. Even so with the amount of data I would hope for a little quicker.
SELECT AA.PK ALERTPK,XMLELEMENT("RTNGetDataResponseV00", XMLATTRIBUTES( AA.PK
AS "RTNReference" , TO_CHAR(SYSDATE,'yyyy-mm-dd"T"hh24:mi:ss"Z"') AS
"issuedAt", AA.DESCRIPTION AS "requestId", '1' AS "xmlns"),
XMLELEMENT("ResponseCode", 0 ), XMLELEMENT("ResponseDetail", 'The request
was processed successfully' ), XMLELEMENT("AccountTransactionSnapshotList",
XMLELEMENT("AccountTransactionSnapshot", XMLELEMENT("Account",
XMLFOREST(B.SERVICER_BIC AS "ServicerBIC" , B.ACCOUNT_NUMBER AS
"AccountNumber", C.ISOCODE AS "CurrencyCode" ) ),
XMLAGG(XMLELEMENT("TransactionSnapshot", XMLATTRIBUTES (MI.PK AS
"RTNReference", TO_CHAR( MI.HUB_ARRIVAL_DATE_TIME,
'yyyy-mm-dd"T"hh24:mi:ss"Z"') AS "recordedByRTNDateTime", D.TYPE AS
"stateCode", TO_CHAR( MI.TRANSACTION_STATUS_DATE_TIME,
'yyyy-mm-dd"T"hh24:mi:ss"Z"') AS "enteredStateDateTime" ), XMLFOREST(
MI.AMOUNT AS "Amount" , TO_CHAR(MI.VALUE_DATE,'yyyy-mm-dd') AS "ValueDate",
TO_CHAR(MI.VALUE_DATE_TIME,'yyyy-mm-dd"T"hh24:mi:ss"Z"') AS "ValueDateTime",
TO_CHAR(MI.ENTRY_DATE,'yyyy-mm-dd') AS "EntryDate",
TO_CHAR(MI.ENTRY_DATE_TIME,'yyyy-mm-dd"T"hh24:mi:ss"Z"') AS "EntryDateTime",
MI.TRANSACTIONTYPE_FK AS "TransactionTypeCode" , MI.TRANSACTION_REFERENCE
AS "TransactionReference", MI.RELATED_REF AS "RelatedReference",
MI.ACCOUNT_OWNER_REF AS "ReferenceForAccountOwner",
MI.SERVICING_INSTITUTION_REF AS "ServicerReference", MI.SUPPLEMENTARY_INFO
AS "SupplementaryDetails", XMLFOREST( SUBSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', 1,
INSTR(MI.INFORMATION_TO_ACCOUNT_OWNER,'_')-1 ) AS "Line", SUBSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', '_') +1,INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', '_', 1, 2 )
-INSTR(MI.INFORMATION_TO_ACCOUNT_OWNER||'__','_')-1 ) AS "Line", SUBSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', '_', 1, 2 )+1, (INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', '_', 1, 3 )-INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__','_',1,2))-1) AS "Line", SUBSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', '_', 1, 3 )+1, (INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', '_', 1, 4 )-INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__','_',1,3))-1) AS "Line", SUBSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', '_', 1, 4 )+1, (INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', '_', 1, 5 )-INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__','_',1,4))-1) AS "Line", RTRIM(SUBSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__', INSTR(
MI.INFORMATION_TO_ACCOUNT_OWNER||'__','_',1,5)+1),'_') AS "Line" ) AS
"InformationToAccountOwner" , XMLFOREST( MI.ORDERINGCUSTOMERACCOUNTNUMBER
AS "Account", MI.ORDERINGCUSTOMERBEI AS "BEI", XMLFOREST( SUBSTR(
MI.ORDERINGCUSTOMERNAMEADDRESS||'__', 1,
INSTR(MI.ORDERINGCUSTOMERNAMEADDRESS,'_')-1 ) AS "Line", SUBSTR(
MI.ORDERINGCUSTOMERNAMEADDRESS||'__', INSTR(
MI.ORDERINGCUSTOMERNAMEADDRESS||'__', '_') +1,INSTR(
MI.ORDERINGCUSTOMERNAMEADDRESS||'__', '_', 1, 2 )
-INSTR(MI.ORDERINGCUSTOMERNAMEADDRESS||'__','_')-1 ) AS "Line", SUBSTR(
MI.ORDERINGCUSTOMERNAMEADDRESS||'__',INSTR(
MI.ORDERINGCUSTOMERNAMEADDRESS||'__', '_', 1, 2 )+1,(INSTR(
MI.ORDERINGCUSTOMERNAMEADDRESS||'__', '_', 1, 3 )-INSTR(
MI.ORDERINGCUSTOMERNAMEADDRESS||'__','_',1,2))-1) AS "Line", RTRIM(SUBSTR(
MI.ORDERINGCUSTOMERNAMEADDRESS||'__', INSTR(
MI.ORDERINGCUSTOMERNAMEADDRESS||'__','_',1,3)+1),'_') AS "Line" ) AS
"NameAndAddress" ) AS "OrderingCustomer", XMLFOREST(
MI.ORDERINGINSTITUTIONIDENTIFIER AS "Id", MI.ORDERINGINSTITUTIONBIC AS
"BIC", XMLFOREST( SUBSTR( MI.ORDERINGINSTITUTIONNAMEADDRESS||'__', 1,
INSTR(MI.ORDERINGINSTITUTIONNAMEADDRESS,'_')-1 ) AS "Line", SUBSTR(
MI.ORDERINGINSTITUTIONNAMEADDRESS||'__', INSTR(
MI.ORDERINGINSTITUTIONNAMEADDRESS||'__', '_') +1,INSTR(
MI.ORDERINGINSTITUTIONNAMEADDRESS||'__', '_', 1, 2 )
-INSTR(MI.ORDERINGINSTITUTIONNAMEADDRESS||'__','_')-1 ) AS "Line", SUBSTR(
MI.ORDERINGINSTITUTIONNAMEADDRESS||'__',INSTR(
MI.ORDERINGINSTITUTIONNAMEADDRESS||'__', '_', 1, 2 )+1,(INSTR(
MI.ORDERINGINSTITUTIONNAMEADDRESS||'__', '_', 1, 3 )-INSTR(
MI.ORDERINGINSTITUTIONNAMEADDRESS||'__','_',1,2))-1) AS "Line",
RTRIM(SUBSTR( MI.ORDERINGINSTITUTIONNAMEADDRESS||'__', INSTR(
MI.ORDERINGINSTITUTIONNAMEADDRESS||'__','_',1,3)+1),'_') AS "Line" ) AS
"NameAndAddress" ) AS "OrderingInstitution", XMLFOREST(
MI.INTERMEDIARYACCOUNTNUMBER AS "Id", MI.INTERMEDIARYBIC AS "BIC",
XMLFOREST(SUBSTR( MI.INTERMEDIARYNAMEADDRESS||'__', 1,
INSTR(MI.INTERMEDIARYNAMEADDRESS,'_')-1 ) AS "Line", SUBSTR(
MI.INTERMEDIARYNAMEADDRESS||'__', INSTR( MI.INTERMEDIARYNAMEADDRESS||'__',
'_') +1,INSTR( MI.INTERMEDIARYNAMEADDRESS||'__', '_', 1, 2 )
-INSTR(MI.INTERMEDIARYNAMEADDRESS||'__','_')-1 ) AS "Line", SUBSTR(
MI.INTERMEDIARYNAMEADDRESS||'__',INSTR( MI.INTERMEDIARYNAMEADDRESS||'__',
'_', 1, 2 )+1,(INSTR( MI.INTERMEDIARYNAMEADDRESS||'__', '_', 1, 3 )-INSTR(
MI.INTERMEDIARYNAMEADDRESS||'__','_',1,2))-1) AS "Line", RTRIM(SUBSTR(
MI.INTERMEDIARYNAMEADDRESS||'__', INSTR( MI.INTERMEDIARYNAMEADDRESS||'__',
'_',1,3)+1),'_') AS "Line" ) AS "NameAndAddress" ) AS
"IntermediaryInstitution", MI.SWIFT_MESSAGE_TYPE_REF AS "RawMessageType" ) )
) )) ) L_XML
FROM
MESSAGEINSTANCE MI,ACCOUNT B,ISOCURRENCY C,TRANSACTIONSTATUSTYPE D,
TRANSACTIONTYPE E, DATASTREAM DS, ALERT AA, DATASTREAM_MEMBER_QUEUE AQ,
DATASTREAM_FREQUENCY DF WHERE MI.ACCOUNT_FK = B.PK AND D.PK =
MI.TRANSACTIONSTATUSTYPE_FK AND E.PK = MI.TRANSACTIONTYPE_FK AND
B.ISOCURRENCY_FK = C.PK AND MI.PK = DS.MESSAGEINSTANCE_FK AND DS.ALERT_FK =
AA.PK AND AQ.PK = AA.DATASTREAM_MEMBER_QUEUE_FK AND AA.FREQUENCY_FK = DF.PK
AND AA.ACTIVE=1 AND AA.LAST_TRIGGER_TIME+(DF.FREQUENCY_SECONDS/86400)<=
SYSDATE AND DS.STATUS = 'I' GROUP BY AA.PK,AA.DESCRIPTION,B.SERVICER_BIC,
B.ACCOUNT_NUMBER, C.ISOCODE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.09 0.11 0 789 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 4 357.22 407.52 582 67949 122018 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 357.32 407.64 582 68738 122018 3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 112 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
3 SORT GROUP BY
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 HASH JOIN
3 NESTED LOOPS
3 NESTED LOOPS
3 TABLE ACCESS BY INDEX ROWID ALERT
3 INDEX FULL SCAN ALERT_IDX1 (object id 64553)
3 INDEX UNIQUE SCAN DATASTREAM_MEMBER_QUEUE_PK (object id 64248)
3 TABLE ACCESS BY INDEX ROWID DATASTREAM_FREQUENCY
3 INDEX UNIQUE SCAN DATASTREAM_FREQUENCY_PK (object id 64252)
8998 PARTITION RANGE ALL PARTITION: 1 8
8998 TABLE ACCESS FULL DATASTREAM PARTITION: 1 8
8998 TABLE ACCESS BY GLOBAL INDEX ROWID MESSAGEINSTANCE PARTITION: ROW LOCATION ROW LOCATION
8998 INDEX UNIQUE SCAN MSGINST_PK_UN (object id 64230)
8998 TABLE ACCESS BY INDEX ROWID TRANSACTIONSTATUSTYPE
8998 INDEX UNIQUE SCAN TRANSACTIONSTATUSTYPE_PK (object id 63938)
8998 INDEX UNIQUE SCAN TRANSACTIONTYPE_PK (object id 63942)
8998 TABLE ACCESS BY INDEX ROWID ACCOUNT
8998 INDEX UNIQUE SCAN ACCOUNT_PK (object id 63822)
8998 TABLE ACCESS BY INDEX ROWID ISOCURRENCY
8998 INDEX UNIQUE SCAN ISOCURRENCY_PK (object id 63880)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 7 0.11 0.30
latch free 6 0.00 0.00
direct path write 4 0.00 0.00
direct path read 42 0.11 1.21
what performance tips are there for returning large xmlypes? Are there any indications to you here how I might improvoe performance? The results return 3 rows , one of which has about "XMLAGG(XMLELEMENT("TransactionSnapshot " 8000 elements with in it.
You help is much appreciated.
January 30, 2005 - 8:19 pm UTC
I asked Sean Dillon to take a look at this, here's what he had to say:
--
Hi Phil,
Yeah, I think SQLXML is pretty cool too. :) What you're running into isn't uncommon, and you've no doubt run into it before, but now it has a veil of "SQLXML" on top of it so it looks like a different animal.
It's the relational query behind the sqlxml operators that's causing your performance problems.
You have quite a few joins in your query. Yes, SQLXML will add some performance load to your query, but not orders of magnitude. The SQLXML operators don't change the way we go after the data from the tables, it changes the way we display it in the result set. The underlying query that is executing is where the performance lag is occurring, you'll have to tune the data access on the tables in order to get better results.
You might want to break out the query above and try to reproduce it as a relational query first. Tune that query because it's something you understand, then add the SQLXML operators after the fact. You'll notice a dip in the performance between the two, but as I said it's not going to be that much.
Hope that helps.
_smd_
Follow on from above and XML performance
phil, January 19, 2005 - 12:30 am UTC
Hi Tom
I have done further investigation on my above question and have become more confused. I have 2 queries which are "almost" identical the difference is what is in an xmlforest. I do not understand why they can be so different in response times. Also the differences in plans ?
Please could you offer some advice on my original question above and this issue. I suspect they are related ?
>>query one contains xmlforest(mi.information_to_account_owner as "test") as "TEST", information_to_account_owner is VARCHAR2(420) :
select aa.pk,aa.description,b.servicer_bic,b.account_number,
c.isocode,xmlagg(xmlelement("pk",
xmlforest(
mi.Amount as "Amount" ,
to_char(mi.value_date,'yyyy-mm-dd') as "ValueDate",
to_char(mi.value_date_time,'yyyy-mm-dd"T"hh24:mi:ss"Z"') as "ValueDateTime",
to_char(mi.entry_date,'yyyy-mm-dd') as "EntryDate",
to_char(mi.entry_date_time,'yyyy-mm-dd"T"hh24:mi:ss"Z"') as "EntryDateTime",
mi.transactiontype_fk as "TransactionTypeCode" ,
mi.transaction_reference as "TransactionReference",
mi.related_ref as "RelatedReference",
mi.account_owner_ref as "ReferenceForAccountOwner",
mi.servicing_institution_ref as "ServicerReference",
mi.supplementary_info as "SupplementaryDetails" ,
xmlforest(mi.information_to_account_owner as "test") as "TEST"
)
))
from messageinstance mi,account b,isocurrency c,transactionstatustype d,transactiontype e,
datastream ds,
alert aa, datastream_member_queue aq,
datastream_frequency df
where mi.account_fk = b.pk
and d.pk = mi.transactionstatustype_fk
and e.pk = mi.transactiontype_fk
and b.isocurrency_fk = c.pk
and mi.pk = ds.messageinstance_fk
and ds.alert_fk = aa.pk
and aq.pk = aa.datastream_member_queue_fk
and aa.frequency_fk = df.pk
and aa.active=1
and aa.last_trigger_time+(df.frequency_seconds/86400)<=sysdate
and ds.status = 'I'
group by aa.pk,aa.description,b.servicer_bic,b.account_number,
c.isocode
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.33 0.33 0 80 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 30.87 32.51 303 53438 67361 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 31.20 32.84 303 53518 67361 3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 112
Rows Row Source Operation
------- ---------------------------------------------------
3 SORT GROUP BY
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 HASH JOIN
3 NESTED LOOPS
3 NESTED LOOPS
3 TABLE ACCESS BY INDEX ROWID ALERT
3 INDEX FULL SCAN ALERT_IDX1 (object id 64553)
3 INDEX UNIQUE SCAN DATASTREAM_MEMBER_QUEUE_PK (object id 64248)
3 TABLE ACCESS BY INDEX ROWID DATASTREAM_FREQUENCY
3 INDEX UNIQUE SCAN DATASTREAM_FREQUENCY_PK (object id 64252)
8998 PARTITION RANGE ALL PARTITION: 1 8
8998 TABLE ACCESS FULL DATASTREAM PARTITION: 1 8
8998 TABLE ACCESS BY GLOBAL INDEX ROWID MESSAGEINSTANCE PARTITION: ROW LOCATION ROW LOCATION
8998 INDEX UNIQUE SCAN MSGINST_PK_UN (object id 64230)
8998 INDEX UNIQUE SCAN TRANSACTIONSTATUSTYPE_PK (object id 63938)
8998 INDEX UNIQUE SCAN TRANSACTIONTYPE_PK (object id 63942)
8998 TABLE ACCESS BY INDEX ROWID ACCOUNT
8998 INDEX UNIQUE SCAN ACCOUNT_PK (object id 63822)
8998 TABLE ACCESS BY INDEX ROWID ISOCURRENCY
8998 INDEX UNIQUE SCAN ISOCURRENCY_PK (object id 63880)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
db file scattered read 3 0.04 0.07
latch free 2 0.00 0.00
direct path write 4 0.00 0.00
direct path read 20 0.10 0.34
********************************************************************************
>> query two contains xmlforest(mi.Amount as "test") as "TEST" , the amount field is a number
select aa.pk,aa.description,b.servicer_bic,b.account_number,
c.isocode,xmlagg(xmlelement("pk",
xmlforest(
mi.Amount as "Amount" ,
to_char(mi.value_date,'yyyy-mm-dd') as "ValueDate",
to_char(mi.value_date_time,'yyyy-mm-dd"T"hh24:mi:ss"Z"') as "ValueDateTime",
to_char(mi.entry_date,'yyyy-mm-dd') as "EntryDate",
to_char(mi.entry_date_time,'yyyy-mm-dd"T"hh24:mi:ss"Z"') as "EntryDateTime",
mi.transactiontype_fk as "TransactionTypeCode" ,
mi.transaction_reference as "TransactionReference",
mi.related_ref as "RelatedReference",
mi.account_owner_ref as "ReferenceForAccountOwner",
mi.servicing_institution_ref as "ServicerReference",
mi.supplementary_info as "SupplementaryDetails" ,
xmlforest(mi.Amount as "test") as "TEST"
)
))
from messageinstance mi,account b,isocurrency c,transactionstatustype d,transactiontype e,
datastream ds,
alert aa, datastream_member_queue aq,
datastream_frequency df
where mi.account_fk = b.pk
and d.pk = mi.transactionstatustype_fk
and e.pk = mi.transactiontype_fk
and b.isocurrency_fk = c.pk
and mi.pk = ds.messageinstance_fk
and ds.alert_fk = aa.pk
and aq.pk = aa.datastream_member_queue_fk
and aa.frequency_fk = df.pk
and aa.active=1
and aa.last_trigger_time+(df.frequency_seconds/86400)<=sysdate
and ds.status = 'I'
group by aa.pk,aa.description,b.servicer_bic,b.account_number,
c.isocode
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.33 0.33 0 731 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 5.12 5.66 396 53629 71125 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 5.45 5.99 396 54360 71125 3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 112
Rows Row Source Operation
------- ---------------------------------------------------
3 SORT GROUP BY
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 NESTED LOOPS
8998 HASH JOIN
3 NESTED LOOPS
3 NESTED LOOPS
3 TABLE ACCESS BY INDEX ROWID OBJ#(63843)
3 INDEX FULL SCAN OBJ#(64553) (object id 64553)
3 INDEX UNIQUE SCAN OBJ#(64248) (object id 64248)
3 TABLE ACCESS BY INDEX ROWID OBJ#(64251)
3 INDEX UNIQUE SCAN OBJ#(64252) (object id 64252)
8998 PARTITION RANGE ALL PARTITION: 1 8
8998 TABLE ACCESS FULL OBJ#(64691) PARTITION: 1 8
8998 TABLE ACCESS BY GLOBAL INDEX ROWID OBJ#(64212) PARTITION: ROW LOCATION ROW LOCATION
8998 INDEX UNIQUE SCAN OBJ#(64230) (object id 64230)
8998 INDEX UNIQUE SCAN OBJ#(63938) (object id 63938)
8998 INDEX UNIQUE SCAN OBJ#(63942) (object id 63942)
8998 TABLE ACCESS BY INDEX ROWID OBJ#(63821)
8998 INDEX UNIQUE SCAN OBJ#(63822) (object id 63822)
8998 TABLE ACCESS BY INDEX ROWID OBJ#(63879)
8998 INDEX UNIQUE SCAN OBJ#(63880) (object id 63880)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
db file scattered read 7 0.03 0.08
direct path write 4 0.00 0.00
direct path read 24 0.09 0.17
SQLXML Performance
phil, January 31, 2005 - 12:27 am UTC
Hi Sean ...
Thanks for your reponse.
In it you said that you will not see a performnace dip that is "orders of magnitude". Now, I have learnt from using and reading this site never to question the "Oracles" answers but to go away work on them and think about them, however in my examples I am seeing what I see as a dip of "orders of magnitude"
>> first with no SQLXML ...
select mi.pk
from messageinstance mi,account b,isocurrency c, datastream ds
where mi.account_fk = b.pk
and b.isocurrency_fk = c.pk
and mi.pk = ds.messageinstance_fk
and ds.alert_fk = 1
and ds.status = 'I'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.17 0.20 0 51 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 588 0.18 0.19 0 28920 0 8798
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 590 0.35 0.40 0 28971 0 8798
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 112
Rows Row Source Operation
------- ---------------------------------------------------
8798 NESTED LOOPS
8798 HASH JOIN
279 INDEX FULL SCAN OBJ#(63966) (object id 63966)
8798 NESTED LOOPS
8799 TABLE ACCESS BY GLOBAL INDEX ROWID OBJ#(64691) PARTITION: ROW LOCATION ROW LOCATION
8799 INDEX RANGE SCAN OBJ#(64728) (object id 64728)
8798 TABLE ACCESS BY GLOBAL INDEX ROWID OBJ#(64212) PARTITION: ROW LOCATION ROW LOCATION
8798 INDEX UNIQUE SCAN OBJ#(64230) (object id 64230)
8798 INDEX UNIQUE SCAN OBJ#(63880) (object id 63880)
and then with SQLXML
select xmlagg(xmlelement("pk",mi.pk))
from messageinstance mi,account b,isocurrency c, datastream ds
where mi.account_fk = b.pk
and b.isocurrency_fk = c.pk
and mi.pk = ds.messageinstance_fk
and ds.alert_fk = 1
and ds.status = 'I'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.35 0.31 0 680 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1.77 1.78 0 26584 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.12 2.09 0 27264 0 1
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 112
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
8798 NESTED LOOPS
8798 HASH JOIN
279 INDEX FULL SCAN OBJ#(63966) (object id 63966)
8798 NESTED LOOPS
8799 TABLE ACCESS BY GLOBAL INDEX ROWID OBJ#(64691) PARTITION: ROW LOCATION ROW LOCATION
8799 INDEX RANGE SCAN OBJ#(64728) (object id 64728)
8798 TABLE ACCESS BY GLOBAL INDEX ROWID OBJ#(64212) PARTITION: ROW LOCATION ROW LOCATION
8798 INDEX UNIQUE SCAN OBJ#(64230) (object id 64230)
8798 INDEX UNIQUE SCAN OBJ#(63880) (object id 63880)
I understand that there is some aggregation of sorts to do. Is the dip I am seeing expected and not in orders of magnitude?
You suggest that one should go away and work on the query first. However are the two queries above actually comparable to be able to do that - probably not - , if not what should be a comparable query to doing the XMLAGG?
kind regards
phil
February 01, 2005 - 9:42 am UTC
Hi Phil, Sean here. Could you send me an e-mail offline, I'd like to discuss this with you further. I'm at sean.dillon@oracle.com. Thanks.
For Sean
Jeff, February 26, 2005 - 2:36 am UTC
Any Update on this?
Thanks for sharing you expertise (you too Tom).
February 26, 2005 - 7:56 am UTC
update on what?
Previous review
Jeff, February 26, 2005 - 11:15 am UTC
Sorry Tom,
I was referring the the previous post. The other person was seeing some performance degradation and Sean took it off line. Just wondering if there was an explanation regarding the previous post. Thanks again,
March 03, 2005 - 10:56 am UTC
Hi Jeff, not yet. I'm trying to get some data so the developers can reproduce the problem and figure out what's going on. In standard tests using normal data, they weren't able to reproduce it.
write file from XSl and XML
A reader, March 04, 2005 - 2:38 pm UTC
To, or anyone who knows this...
I am wanting to create a file from an xml data file...
Lets say I have 36 records in the xml file. when I apply my style sheet onb to the xml file I want a file created that has 6 records on one line and then six records on another line.
My limit of having number of records on one line is 6.
for example in my xml I have
<body>
<Row num="1">
<transactioncode>111</transactioncode>
<paymentamount>1111111</paymentamount>
<paymentdate>111111111</paymentdate>
<filler>=</filler>
</Row >
<Row num="2">
<transactioncode>222</transactioncode>
<paymentamount>2222222</paymentamount>
<paymentdate>2222222</paymentdate>
<filler>=</filler>
</Row >
<Row num="3">
<transactioncode>333</transactioncode>
<paymentamount>3333333</paymentamount>
<paymentdate>333333</paymentdate>
<filler>=</filler>
</Row >
<Row num="4">
<transactioncode>444</transactioncode>
<paymentamount>4444444</paymentamount>
<paymentdate>444444</paymentdate>
<filler>=</filler>
</Row >
<Row num="5">
<transactioncode>555</transactioncode>
<paymentamount>5555555</paymentamount>
<paymentdate>555555</paymentdate>
<filler>=</filler>
</Row >
<Row num="6">
<transactioncode>666</transactioncode>
<paymentamount>6666666</paymentamount>
<paymentdate>666666</paymentdate>
<filler>=</filler>
</Row >
<Row num="7">
<transactioncode>777</transactioncode>
<paymentamount>7777777</paymentamount>
<paymentdate>777777</paymentdate>
<filler>=</filler>
</Row >
</Body>
I need to knwo what i have to do in the style sheet so that when I apply a style sheet to the xml above this is the result i get in a file
11111111111111222222222222222333333333333333444444444444444555555555555555666666666666666777777777777777
records each line should not be more than 6..... I cannot have more than 6 records per line...
I need to know what code I can write n the style sheet.... cannot find and do not knwo how..... does anybody know???
Your kind response with the answer will be appreciated.
Thanks sadia
Generate XML tags dynamically based on column value.
A reader, May 18, 2005 - 10:15 am UTC
Hi Tom/Sean,
I want to generate xml in the following way using SQLXoperators.
But only problem is when i use the operator, the tag name should be supplied before in hand as parameter.
So it works well when the data for the tags are stored in seperate columns then its pretty simple.
But lets say the whole hierarchy of the nodes is stored in one table with ID-->PARENTID relationship and the text is stored in a column called description.
Now using CONNECT BY PRIOR i get the hierarchy and now based on the description value i have to generate the tags and then fill in the other details.
This is just a summary of what i want. the below sample has much more than that and actual data comes from more than one table.
But if i get the solution for what i asked before i think i can build the test of the thing.
<?xml version="1.0" encoding="UTF-8"?>
<SequenceData xmlns:xsi="</code>
http://www.w3.org/2001/XMLSchema-instance" <code>xsi:noNamespaceSchemaLocation="SequenceData.xsd">
<Header>
<TemplateName>Template(XML)</TemplateName>
<TemplateVersion>1.1</TemplateVersion>
<CreatedUser>rsh2kor</CreatedUser>
<CreatedDate>17-06-2005 12.12.22</CreatedDate>
</Header>
<List>
<LoadPoints>
<LoadPoint description="Loadpoint1_in_list(level one)">
<SetPhase>
<SetPhaseVariables>
<SetPhaseVariable>
<Name>pRail</Name>
<Unit>bar</Unit>
<Value>100</Value>
<SettlingTime>0.0</SettlingTime>
</SetPhaseVariable>
</SetPhaseVariables>
<MonitoredVariables>
<MonitoredVariable>
<ID>12344<ID>
<Name>MeasPoint</Name>
<Unit>pascal</Unit>
<Limit>10</Limit>
<Tolerance>0.0</Tolerance>
<Operator>></Operator>
<AlertType>STOP</AlertType>
</MonitoredVariable>
</MonitoredVariables>
</SetPhase>
<WaitPhase>
<WaitingTime>10</WaitingTime>
<MonitoredVariables>
<MonitoredVariable>
<Name>MeasPoint</Name>
<Unit>pascal</Unit>
<Limit>10</Limit>
<Tolerance>0.0</Tolerance>
<Operator>></Operator>
<AlertType>STOP</AlertType>
</MonitoredVariable>
</MonitoredVariables>
</WaitPhase>
<MeasPhase>
<MeasPhaseVariables>
<MeasPhaseVariable>
<Name>MeasPoint</Name>
<Unit>pascal</Unit>
<Result>0</Result>
</MeasPhaseVariable>
</MeasPhaseVariables>
</MeasPhase>
</LoadPoint>
</LoadPoints>
</List>
<Loop Iteration="5">
<LoadPoints>
<LoadPoint description="Loadpoint2_in_Loop">
<SetPhase>
<SetPhaseVariables>
<SetPhaseVariable LoopCount="1">
<Name>pRail</Name>
<Unit>bar</Unit>
<Value>10</Value>
<SettlingTime>0.0</SettlingTime>
</SetPhaseVariable>
<SetPhaseVariable LoopCount="2">
<Name>pRail</Name>
<Unit>bar</Unit>
<Value>20</Value>
<SettlingTime>0.0</SettlingTime>
</SetPhaseVariable>
<SetPhaseVariable LoopCount="3">
<Name>pRail</Name>
<Unit>bar</Unit>
<Value>30</Value>
<SettlingTime>0.0</SettlingTime>
</SetPhaseVariable>
<SetPhaseVariable LoopCount="4">
<Name>pRail</Name>
<Unit>bar</Unit>
<Value>40</Value>
<SettlingTime>0.0</SettlingTime>
</SetPhaseVariable>
<SetPhaseVariable LoopCount="5">
<Name>pRail</Name>
<Unit>bar</Unit>
<Value>50</Value>
<SettlingTime>0.0</SettlingTime>
</SetPhaseVariable>
</SetPhaseVariables>
<MonitoredVariables>
<MonitoredVariable>
<Name>MeasPoint</Name>
<Unit>pascal</Unit>
<Limit>10</Limit>
<Tolerance>0.0</Tolerance>
<Operator>></Operator>
<AlertType>STOP</AlertType>
</MonitoredVariable>
</MonitoredVariables>
</SetPhase>
<WaitPhase>
<WaitingTime>10</WaitingTime>
<MonitoredVariables>
<MonitoredVariable>
<Name>MeasPoint</Name>
<Unit>pascal</Unit>
<Limit>10</Limit>
<Tolerance>0.0</Tolerance>
<Operator>></Operator>
<AlertType>STOP</AlertType>
</MonitoredVariable>
</MonitoredVariables>
</WaitPhase>
<MeasPhase>
<MeasPhaseVariables>
<MeasPhaseVariable>
<Name>MeasPoint</Name>
<Unit>pascal</Unit>
<Result>0</Result>
</MeasPhaseVariable>
</MeasPhaseVariables>
</MeasPhase>
</LoadPoint>
</LoadPoints>
<List>
<LoadPoints>
<LoadPoint description="Loadpoint3_in_list(level two)">
<MonitoredVariables/>
<SetPhase>
<SetPhaseVariables>
<SetPhaseVariable>
<Name>pRail</Name>
<Unit>bar</Unit>
<Value>100</Value>
<SettlingTime>0.0</SettlingTime>
</SetPhaseVariable>
</SetPhaseVariables>
<MonitoredVariables>
<MonitoredVariable>
<Name>MeasPoint</Name>
<Unit>pascal</Unit>
<Limit>10</Limit>
<Tolerance>0.0</Tolerance>
<Operator>></Operator>
<AlertType>STOP</AlertType>
</MonitoredVariable>
</MonitoredVariables>
</SetPhase>
<WaitPhase>
<WaitingTime>10</WaitingTime>
<MonitoredVariables>
<MonitoredVariable>
<Name>MeasPoint</Name>
<Unit>pascal</Unit>
<Limit>10</Limit>
<Tolerance>0.0</Tolerance>
<Operator>></Operator>
<AlertType>STOP</AlertType>
</MonitoredVariable>
</MonitoredVariables>
</WaitPhase>
<MeasPhase>
<MeasPhaseVariables>
<MeasPhaseVariable>
<Name>MeasPoint</Name>
<Unit>pascal</Unit>
<Result>0</Result>
</MeasPhaseVariable>
</MeasPhaseVariables>
</MeasPhase>
</LoadPoint>
</LoadPoints>
</List>
</Loop>
</SequenceData>
Pls help as soon as possible.
Thanks.
A reader, May 22, 2005 - 10:49 pm UTC
Hi Tom/Sean, Please see the imm above post for generating xml by generating the tag names dynamically.
Can you pls look into this ASAP.
May 23, 2005 - 8:26 am UTC
we cannot and do not look at each and every followup and review, you should not count on any comments being made here.
you don't even give a simple SMALL, yet 100% complete example (as small repeat SMALL as possible) to play with.
Generate XML
A reader, May 24, 2005 - 12:13 am UTC
Hey Thomas cool down. Very sorry about it that i did not place a sample for you to play with.
okay here we go. I have tried to prepare a sample data.
create table xml_data
(
id number
,type varchar2(200)
,description varchar2(200)
,parentid number
)
/
insert into xml_data values(1, 'Root', null, null)
/
insert into xml_data values(2, 'LI', 'List of LP' ,1)
/
insert into xml_data values(3, 'LO', 'Loop',1)
/
insert into xml_data values(4, 'LP', 'LoadPoint to initialise', 2)
/
insert into xml_data values(5, 'LP','LoadPoint to initialise', 2)
/
insert into xml_data values(6, 'LP','LoadPoint to initialise', 3)
/
insert into xml_data values(7, 'LP', 'LoadPoint to initialise',3)
/
insert into xml_data values(8, 'LI', 'List of LP',3)
/
insert into xml_data values(4, 'LP', 'LoadPoint to initialise',8)
/
insert into xml_data values(5, 'LP', 'LoadPoint to initialise',8)
/
set linesize 1000
select lpad(' ',2*level)||type
from xml_data
connect by prior id = parentid
start with parentid is null
/
Here is the desired output which i want to achieve using SQLX operators.
Basically from the type column value, the tags should be generated.
LI - List
LP - LoadPoint
LO - Loop
Data in the tags are simulated so it might not make sense at the moment.
<ROOT>
<LIST>
<LOADPOINT>LoadPoint to initialise</LOADPOINT>
<LOADPOINT>LoadPoint to initialise</LOADPOINT>
</LIST>
<LOOP>
<LOADPOINT>LoadPoint to initialise<LOADPOINT>
<LOADPOINT>LoadPoint to initialise<LOADPOINT>
<LIST>
<LOADPOINT>LoadPoint to initialise</LOADPOINT>
<LOADPOINT>LoadPoint to initialise</LOADPOINT>
</LIST>
</LOOP>
</ROOT>
Pls help asap.
May 24, 2005 - 7:41 am UTC
cool down? Just pointing out that one needs a SMALL example (like the page you used to submit this says......). To you this problem is very clear (it is your problem after all, but to the rest of us, it is "not very well understood". Add to that "look at this ASAP"?
Here's what Sean had to say:
--
Looking at your requirements, I have a couple of comments/suggestions I can make.
First, the XMLELEMENT SQLX operator is incapable of doing what you're asking. In the documentation, it reads :
"The identifier (the first parameter to XMLELEMENT) does not need to be a column name or column reference, and it cannot be an expression."
Basically, that boils down to making it difficult for you to use dynamic element names using SQLX. For your particular case, you might not be out of luck w/ regard to the dynamic element names, however. With a limited number of values, you can DECODE the xmlelement call.
scott@BOOGIE> select xmlelement("root",
2 decode(2,1,xmlelement("name",'value'),
3 xmlelement("othername",'othervalue')
4 )
5 ) as result
6 from dual
7 /
RESULT
----------------------------------------------
<root><othername>othervalue</othername></root>
Obviously, this isn't very useful unless you're doing it at the leaf nodes of your query. In 10gR2, you can look for a new but unsupported as-of-yet EVAL clause to the XMLELEMENT function which allows you to resolve an expression for the element name.
select xmlelement(EVAL colname, col2)
from ...
Next, a connect by query is very good at traversing a parent-child association and including rows in a FLAT result set based on the row values and relationships. What I mean by that is.. in your query you'll notice the LPAD function provides a way to simulate nesting, the SQL engine doesn't create a true nested result. For this kind of XML generation, you may want to create a custom object that has a nested parent-child relationship that you can build up from your data, then pass the object to DBMS_XMLGEN or something.
I'm looking to see if there's an elegant way to use SQLXML to do connect by's (or something like it).
A reader, May 25, 2005 - 1:45 am UTC
Thanks Sean,
I will wait for your reply to see if you can provide me something more useful related to this topic.
Just to make a note that my database version is 9206 and i cannot use 10g at the moment. So the generation should be possible with 9206 only. I have read the article of TOM about the new clauses of CONNECT BY in 10g.
Another point to make if i create objects can i represent the hierarchy using CONNECT BY in it. Can you pls provide me with sample way to do it.
Thanks a lot.
Trouble with XMLAGG
Me, May 26, 2005 - 1:48 pm UTC
I am having trouble formatting my XML generated from SQLX.
I would like a result as:
<Provider version="9.01">
<HeaderInfo>
cmd_seq
corps_id
dea_id
entity_seq
facility_uic
</HeaderInfo>
<Detail>
paygrade
rate
ssn
start_date
stop_date
username
sensitive
</Detail>
<HeaderInfo>
......
</HeaderInfo>
<Detail>
....
</Detail>
....... Header and Detail for each row
</Provider>
my SQLX is:
SQL> select xmlelement( "provider",
2 xmlattributes( '09.01.00' as "version"),
3 xmlagg(
4 xmlelement("HeaderInfo",
5 xmlforest(cmd_seqas "cmd_seq",
6 corps_id as "corps_id",
7 dea_id as "dea_id",
8 entity_seq as "entity_seq",
9 facility_uic as "facility_uic")),
10 xmlelement("Detail",
11 xmlforest(fname||' '||lname||''||suffix as "Name",
12 paygrade_id as "Paygrade",
13 rate as "Rate",
14 ssn as "SSN",
15 start_date as "Start Date",
16 stop_date as "Stop Date",
17 username as "Username",
18 view_sens_ind as "Sensitive")
19 )
20 )),
21 from sams.provider;
from sams.provider
*
ERROR at line 21:
ORA-00936: missing expression
I had it grouping all headers then all detail but obviously thats not what I want. I dont think I understand the use of XMLAGG accurately.
Thank you for your time it is appreciated.
May 26, 2005 - 2:40 pm UTC
you have a trailing comma on line 20 to start with...
Thank, Corrected and xmlagg is ....
A reader, May 26, 2005 - 2:54 pm UTC
.... still a problem
SQL> select xmlelement( "provider",
2 xmlattributes( '09.01.00' as "version"),
3 xmlagg(
4 xmlelement("HeaderInfo",
5 xmlforest(cmd_seq as "cmd_seq",
6 corps_id as "corps_id",
7 dea_id as "dea_id",
8 entity_seq as "entity_seq",
9 facility_uic as "facility_uic")),
10 xmlelement("Detail",
11 xmlforest(fname||' '||lname||''||suffix as "Name",
12 paygrade_id as "Paygrade",
13 rate as "Rate",
14 ssn as "SSN",
15 start_date as "Start Date",
16 stop_date as "Stop Date",
17 username as "Username",
18 view_sens_ind as "Sensitive")
19 )
20 ))
21 from sams.provider;
xmlagg(
*
ERROR at line 3:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'SYS_IXMLAGG'
my table format is like
CREATE TABLE PROVIDER
(
CMD_SEQ NUMBER,
CORPS_ID VARCHAR2(3),
DEA_ID VARCHAR2(9),
ENTITY_SEQ NUMBER NOT NULL,
FACILITY_UIC VARCHAR2(8) NOT NULL,
FNAME VARCHAR2(20) NOT NULL,
LNAME VARCHAR2(20) NOT NULL,
MNAME VARCHAR2(20),
PAYGRADE_ID VARCHAR2(4) NOT NULL,
PROV_SEQ NUMBER NOT NULL,
RATE VARCHAR2(6) NOT NULL,
SERVICE_ID VARCHAR2(6) NOT NULL,
SSN VARCHAR2(9) NOT NULL,
START_DATE DATE NOT NULL,
STOP_DATE DATE,
SUFFIX VARCHAR2(4),
USERNAME VARCHAR2(40),
VIEW_SENS_IND VARCHAR2(1) DEFAULT 'N' NOT NULL,
VIEW_STD_IND VARCHAR2(1) DEFAULT 'N' NOT NULL,
VIEW_SUPPLY_SETUP_IND VARCHAR2(1) DEFAULT 'N' NOT NULL
)
May 27, 2005 - 8:35 am UTC
Hi reader,
You're passing two XMLELEMENT parameters into XMLAGG... i.e.
...
xmlagg(
xmlelement("HeaderInfo"),
xmlelement("Detail")
)
...
So we can reproduce it with...
SQL> select xmlelement( "employees_by_department",
2 xmlattributes( sysdate as "generated_on" ),
3 xmlagg(
4 xmlelement("Departments",
5 xmlforest(deptno as "Number",
6 dname as "Name",
7 loc as "Location"
8 )
9 )
10 )
11 )
12 from dept
13 /
XMLELEMENT("EMPLOYEES_BY_DEPARTMENT",XMLATTRIBUTES(SYSDATEAS"GENERATED_ON"),XMLAGG(XMLELEMENT("DEPAR
----------------------------------------------------------------------------------------------------
<employees_by_department generated_on="27-MAY-05">
<Departments>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Location>NEW YORK</Location>
</Departments>
... <cut for brevity> ...
<Departments>
<Number>40</Number>
<Name>OPERATIONS</Name>
<Location>BOSTON</Location>
</Departments>
</employees_by_department>
SQL> -- now you'll notice I pass xmlement("Departments") *AND*
SQL> -- xmlelement("Employees") to XMLAGG()...
SQL> select xmlelement( "employees_by_department",
2 xmlattributes( sysdate as "generated_on" ),
3 xmlagg(
4 xmlelement("Departments",
5 xmlforest(d.deptno as "Number",
6 d.dname as "Name",
7 d.loc as "Location"
8 )
9 ),
10 xmlelement("Employees",
11 xmlforest(e.empno as "Number",
12 e.ename as "Name",
13 e.job as "Job"
14 )
15 )
16 )
17 )
18 from dept d,
19 emp e
20 where e.deptno = d.deptno
21 /
xmlagg(
*
ERROR at line 3:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'SYS_IXMLAGG'
Often times, when you have a parent-child relationship and you're trying to create a single XML document, you need to use two XMLAGG's. One to rollup the parent objects into a single document (in this case, our DEPT rows)... and then another XMLAGG to aggregate the EMP rows for each DEPT row. This might look like this:
SQL> select xmlelement( "employees_by_department",
2 xmlattributes( sysdate as "generated_on" ),
3 xmlagg(
4 xmlelement("Department",
5 xmlforest(d.deptno as "Number",
6 d.dname as "Name",
7 d.loc as "Location"
8 ),
9 xmlelement("Employees",
10 (select xmlagg(
11 xmlelement("Employee",
12 xmlforest(e.empno as "Number",
13 e.ename as "Name",
14 e.job as "Job"
15 )
16 )
17 )
18 from emp e
19 where e.deptno = d.deptno
20 )
21 )
22 )
23 )
24 )
25 from dept d
26 /
XMLELEMENT("EMPLOYEES_BY_DEPARTMENT",XMLATTRIBUTES(SYSDATEAS"GENERATED_ON"),XMLAGG(XMLELEMENT("DEPAR
----------------------------------------------------------------------------------------------------
<employees_by_department generated_on="27-MAY-05">
<Department>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Location>NEW YORK</Location>
<Employees>
<Employee>
<Number>7782</Number>
<Name>CLARK</Name>
<Job>MANAGER</Job>
</Employee>
<Employee>
<Number>7839</Number>
<Name>KING</Name>
<Job>PRESIDENT</Job>
</Employee>
<Employee>
<Number>7934</Number>
<Name>MILLER</Name>
<Job>CLERK</Job>
</Employee>
</Employees>
</Department>
<Department>
<Number>20</Number>
<Name>RESEARCH</Name>
<Location>DALLAS</Location>
<Employees>
<Employee>
<Number>7369</Number>
<Name>SMITH</Name>
<Job>CLERK</Job>
</Employee>
<Employee>
<Number>7566</Number>
<Name>JONES</Name>
<Job>MANAGER</Job>
</Employee>
</Employees>
</Department>
... <cut for brevity> ...
</employees_by_department>
Just remember, you can only pass one XMLELEMENT to XMLAGG. Hope that helps!
_smd_
I am 90% there just dont get XMLagg....
A reader, May 26, 2005 - 3:48 pm UTC
..... Im thinking a need to xmlconcat() to queries to achieve what I want
SQL> select xmlelement( "provider",
2 xmlattributes( '09.01.00' as "version"),
3 xmlelement("HeaderInfo",
4 xmlforest(cmd_seq as "cmd_seq",
5 corps_id as "corps_id",
6 dea_id as "dea_id",
7 entity_seq as "entity_seq",
8 facility_uic as "facility_uic")),
9 xmlelement("Detail",
10 xmlforest(fname||' '||lname||''||suffix as "Name",
11 paygrade_id as "Paygrade",
12 rate as "Rate",
13 ssn as "SSN",
14 start_date as "Start Date",
15 stop_date as "Stop Date",
16 username as "Username",
17 view_sens_ind as "Sensitive")))
18 from sams.provider order by cmd_seq;
XMLELEMENT("PROVIDER",XMLATTRIBUTES('09.01.00'AS"VERSION"),XMLELEMENT("HEADERINF
--------------------------------------------------------------------------------
<provider version="09.01.00"><HeaderInfo><cmd_seq>860</cmd_seq><corps_id>HC</cor
ps_id><entity_seq>861</entity_seq><facility_uic>00000000</facility_uic></HeaderInfo><Detail><Name>CHRIS BOYLES</Name><Paygrade>CIV</Paygrade><Rate>CIV</Rate><SS
N>001010001</SSN><Start_x0020_Date>01-JAN-00</Start_x0020_Date><Username>QUACK_1
67</Username><Sensitive>Y</Sensitive></Detail></provider>
Using sys_xmlagg without the rowset tags
Claude, May 27, 2005 - 9:35 am UTC
Hi Tom/Sean,
My preference is for the sys_xmlagg method. The problem is that I can't get rid of the <rowset> tag layer. In the example below, how would I get rid of it? Is it possible.
Thanks
SQL> select xmlelement(
2 "MASTER",
3 xmlforest(
4 m.inv_no, m.inv_date,
5 (select sys_xmlagg(
6 xmlelement(
7 "DETAIL",
8 xmlforest(item_no, qty)
9 )
10 )
11 from inv_detail d
12 where d.inv_no = m.inv_no
13 ) "DETAILS"
14 )
15 ) as "XML_QUERY"
16 from inv_master m
17 /
XML_QUERY
--------------------------------------------------------------
<MASTER>
<INV_NO>1</INV_NO>
<INV_DATE>13-AUG-02</INV_DATE>
<DETAILS>
<ROWSET> <<--- Want to remove
<DETAIL>
<ITEM_NO>1</ITEM_NO>
<QTY>1</QTY>
</DETAIL>
<DETAIL>
<ITEM_NO>2</ITEM_NO>
<QTY>1</QTY>
</DETAIL>
<DETAIL>
<ITEM_NO>2</ITEM_NO>
<QTY>2</QTY>
</DETAIL>
</ROWSET> <<--- Want to remove
</DETAILS>
</MASTER>
May 27, 2005 - 10:04 am UTC
I asked Sean Dillon to take a look at this, here's what he had to say:
--
Hi Claude,
<documentation_snippet book="XML DB Developer's Guide>
SYS_XMLAGG Function
SQL function SYS_XMLAGG aggregates all XML documents or fragments represented
by expr and produces a single XML document. It adds a new enclosing element with a
default name, ROWSET. To format the XML document differently, use the fmt
parameter.
</documentation_snippet>
You can use the fmt XMLFormat object to *change* the ROWSET tag, or you might investigate just using XMLAGG...
scott@BOOGIE> select xmlelement( "employees_by_department",
2 xmlattributes( sysdate as "generated_on" ),
3 sys_xmlagg(
4 xmlelement("Departments",
5 xmlforest(deptno as "Number",
6 dname as "Name",
7 loc as "Location"
8 )
9 )
10 )
11 )
12 from dept
13 /
XMLELEMENT("EMPLOYEES_BY_DEPARTMENT",XMLATTRIBUTES(SYSDATEAS"GENERATED_ON"),SYS_XMLAGG(XMLELEMENT("D
----------------------------------------------------------------------------------------------------
<employees_by_department generated_on="27-MAY-05"><ROWSET>
<Departments>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Location>NEW YORK</Location>
</Departments>
... <clipped for brevity> ...
<Departments>
<Number>40</Number>
<Name>OPERATIONS</Name>
<Location>BOSTON</Location>
</Departments>
</ROWSET>
</employees_by_department>
scott@BOOGIE> select xmlelement( "employees_by_department",
2 xmlattributes( sysdate as "generated_on" ),
3 xmlagg(
4 xmlelement("Departments",
5 xmlforest(deptno as "Number",
6 dname as "Name",
7 loc as "Location"
8 )
9 )
10 )
11 )
12 from dept
13 /
XMLELEMENT("EMPLOYEES_BY_DEPARTMENT",XMLATTRIBUTES(SYSDATEAS"GENERATED_ON"),XMLAGG(XMLELEMENT("DEPAR
----------------------------------------------------------------------------------------------------
<employees_by_department generated_on="27-MAY-05">
<Departments>
<Number>10</Number>
<Name>ACCOUNTING</Name>
<Location>NEW YORK</Location>
</Departments>
... <clipped for brevity> ...
<Departments>
<Number>40</Number>
<Name>OPERATIONS</Name>
<Location>BOSTON</Location>
</Departments>
</employees_by_department>
Hope that helps!
_smd_
Thank you Sean....
CG, May 27, 2005 - 9:56 am UTC
..... I am closer.
Forget the "90%" post after your post. I had not read it before I posted that.
Your example helps.
Only difference is that Im trying to form a parent child relationship from one table.
"Splitting out" the header columns from the detail.
Ill keep grinding at it.
Creating large XML file from SQL*Plus
Claude, May 31, 2005 - 12:30 pm UTC
Hi Tom/Sean,
The XML files produced here so far using xmlforest, xmlagg, xmlelement are pretty small.
I am trying to write the contents of a giant (>4K) XML, generated from an SQL query, and spool it to an XML file.
The problem is that the XML output generated from this query is so large (up to 50KB) that most of the XML segments (fetched rows of xmltype) get truncated at around 4KB.
I've used getClobVal() and dbms_lob.substr, instr, etc to parse and print it out. However the clob generated from getClobVal removes all of the line breaks that the xmlType has, making the xml hard to read. getStringVal doesn't work because it limits me to 4K chars.
Is there a way to generate large xml files (>4KB) from sqlplus using this method, while preserving the formatting of the original XMLType? If so how? I'm using 9.2.0.5.
Thanks
May 31, 2005 - 3:41 pm UTC
Claude, it has to do with the generation method, not the display method. You're assuming the SQLXML operators are creating "pretty-printed" XML, when in fact they're not...
Consider the example on this question w/ the INV_MASTER and INV_DETAIL. SYS_XMLAGG *GENERATES* the pretty-printed xml:
SQL> select sys_xmlgen(
2 MASTER(m.inv_no, m.inv_date,
3 cast(multiset(select d.item_no, d.qty
4 from inv_detail d
5 where d.inv_no = m.inv_no) as DETAILS)
6 )).getClobVal() as XML_QUERY
7 from inv_master m
8 /
XML_QUERY
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROW>
<INV_NO>1</INV_NO>
<INV_DATE>31-MAY-05</INV_DATE>
<INV_DETAIL>
<DETAIL>
<ITEM_NO>1</ITEM_NO>
<QTY>1</QTY>
</DETAIL>
<DETAIL>
<ITEM_NO>2</ITEM_NO>
<QTY>1</QTY>
</DETAIL>
<DETAIL>
<ITEM_NO>2</ITEM_NO>
<QTY>2</QTY>
</DETAIL>
</INV_DETAIL>
</ROW>
... whereas XMLELEMENT does not ...
SQL> select xmlelement("ROW",
2 xmlforest(m.inv_no, m.inv_date)
3 )
4 from inv_master m
5 /
XMLELEMENT("ROW",XMLFOREST(M.INV_NO,M.INV_DATE))
--------------------------------------------------------------------------------
<ROW><INV_NO>1</INV_NO><INV_DATE>31-MAY-05</INV_DATE></ROW>
Whether you're using getStringVal or getClobVal, the xml data comes out in SQL*Plus w/ whitespace intact:
SQL> declare
2 l_xml xmltype;
3 begin
4 select xmlelement("ROW",
5 xmlforest(m.inv_no, m.inv_date)
6 )
7 into l_xml
8 from inv_master m;
9 --
10 dbms_output.put_line(l_xml.getStringVal());
11 dbms_output.put_line(l_xml.getClobVal());
12 end;
13 /
<ROW><INV_NO>1</INV_NO><INV_DATE>31-MAY-05</INV_DATE></ROW>
<ROW><INV_NO>1</INV_NO><INV_DATE>31-MAY-05</INV_DATE></ROW>
PL/SQL procedure successfully completed.
...so you're question remains, re: >4KB elements, etc. The same rules from above apply, but getStringVal() will not work as it only accomodates 4K of character data. Additionally, if you use DBMS_OUTPUT your lines can't exceed 255 characters. Then, if you use the SQL*Plus PRINT command, you're dealing with LINESIZE and LONG environment variables in plus...
SQL> set long 50000
SQL> set linesize 50
SQL> select t.x.getClobVal()
2 from tmpxml t
3 /
T.X.GETCLOBVAL()
--------------------------------------------------
<Test>
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
{a bunch of 50 character lines clipped for brevity}
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xx
</Test>
Now, if we set the LONG param to a lower value than the total character length of the XMLType value being displayed, SQL*Plus *WILL* break the output on that LONG+1, and the remaining character data will start on the next line. (sorry, you probably know all about SQL*Plus variables and I'm rambling, but it pertains to your question so I wanted to be explicit).
...so, a lot to think about there I know :). To be honest, unless this is just a learning too you're using for educating people about XML, SQLXML, or generation... people *generally* don't like to read XML in it's native text format, and you might consider using XSLT to transform it into something human-readable (</soapbox mood=":)">) I hope that helped!
Sean Dillon
Creating large XML file from SQL*Plus
Claude, June 01, 2005 - 10:37 am UTC
Sean,
Thanks for your help. Long story short, it turns out that set long 50000 solved my problem. Guess I need to brush up on my SQL*report commands. :)
As a side, I found that using .getclobval() in some cases produces funny results. For example:
spool myxml.xml
select xmlelement(
"MASTER",
xmlforest(
m.inv_no, m.inv_date,
(select xmlagg(
xmlelement(
"DETAIL",
xmlforest(item_no, qty)
)
)
from inv_detail d
where d.inv_no = m.inv_no
) "DETAILS"
)
) as "XML_QUERY"
from inv_master m;
spool off
produces:
<MASTER>
<INV_NO>1</INV_NO>
<INV_DATE>01-JUN-05</INV_DATE>
<DETAILS>
<DETAIL>
<ITEM_NO>3</ITEM_NO>
<QTY>81</QTY>
</DETAIL>
<DETAIL>
<ITEM_NO>45</ITEM_NO>
<QTY>16</QTY>
</DETAIL>
<DETAIL>
<ITEM_NO>56</ITEM_NO>
<QTY>13</QTY>
</DETAIL>
<DETAIL>
...
</DETAIL>
</MASTER>
in ASCII text, no tabs, perfectly clean. Whereas adding the .getClobVal() method yields gobbledygook:
<MASTER><INV_NO>1</INV_NO><INV_DATE>01-JUN-05</INV_DATE><DETAILS><DETAIL><ITEM_N
O>3</ITEM_NO><QTY>81</QTY></DETAIL><DETAIL><ITEM_NO>45</ITEM_NO><QTY>16</QTY></D
ETAIL><DETAIL><ITEM_NO>49</ITEM_NO><QTY>34</QTY></DETAIL><DETEM_NO>100</ITEM_NO><QTY>99</QTY></DETAIL>
...
</MASTER>
I found out that the reason for this is because of sys_xmlagg. If I use sys_xmlagg instead of xmlagg, then getClobVal() returns the nicely formatted resutls. sys_xmlagg yields nice results with or without getClobVal(). This isn't a problem for me, but I thought I'd pass it onto the readers.
FYI here are the tables and data that I used to play around with:
SQL> create table inv_master(inv_no number(10),
2 inv_date date);
Table created.
SQL> create table inv_detail(inv_no number(10),
2 item_no number(10),
3 qty number);
Table created.
SQL> insert into inv_master select rownum, sysdate from all_objects
2 where rownum <= 5;
5 rows created.
SQL> insert into inv_detail select round(dbms_random.value(1, 5)), rownum, round(dbms_random.value(1, 100))
2 from all_objects
3 where rownum <= 1000;
1000 rows created.
Generate XML in desired format
John Binny, June 07, 2005 - 8:50 am UTC
Hi Tom,
Just i follow the example what you given in this page, but i didn't get the result as you got. Can you pl. explain where i made wrong? i am not getting the detail info, i am getting only the master in xml query
create table inv_master(
inv_no number(10),
inv_date date)
create table inv_detail(
inv_no number(10),
item_no number(10),
qty number)
insert into inv_master values (1,sysdate)
insert into inv_detail values (1,1,1)
insert into inv_detail values (1,2,1)
insert into inv_detail values (1,3,1)
insert into inv_detail values (1,2,2)
create type DETAIL as object(
item_no number(10),
qty number)
create type DETAILS as table of DETAIL
create type xml_MASTER as object(
inv_no number(10),
inv_date date,
inv_detail DETAILS)
select sys_xmlgen(
XML_MASTER(m.inv_no, m.inv_date,
cast(multiset(select d.item_no, d.qty
from inv_detail d
where d.inv_no = m.inv_no) as DETAILS)
)).getClobVal() as XML_QUERY
from inv_master m
XML_QUERY
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROW>
<INV_NO>1</INV_NO>
<INV_DATE>07-06-2005</INV_DATE>
June 07, 2005 - 8:54 am UTC
SQL> set long 50000
else sqlplus just shows a bit of the big strings.
DBMS_XMLSAVE
reader, June 10, 2005 - 9:52 am 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
June 10, 2005 - 10:41 am UTC
don't know - but hey, take a large document, measure the memory before and after and see if there will be an issue or not.
found answer in another thread
CG, June 10, 2005 - 12:04 pm UTC
Thanks Tom for your response,
Found another thread you were working on the past few days and it was informative. I posted an response to it for the reader.
He was using Java to load multiple tables from one XML document using a SAX parser.
( Upon advice from Sean and Mark ( Java expert ? ) )
So looks like if I use SAX I will have to right my own code or try my luck with DBMS_XMLSAVE and DOM.
June 10, 2005 - 3:41 pm UTC
Sean = XML
Mark = Java
Storing formatted XML in CLOB column
Stephen, June 29, 2005 - 7:54 am UTC
Great Thread.
I need to take this one step farther and I can't seem to get all the pieces together.
I need to use PL/SQL to generate a multi-tier XML (e.g. COUNTRIES with miltiple DIVISIONS with multiple DEPARTMENTS with multiple EMPLOYEES) and store it in a CLOB column.
Bases on all my searches of this site, and lots of trial and error, I believe I need to use XMLElement and Agg etc. Since XMLGEN and XMLQUERY (Even with Multiset or CURSOR syntax) can only get two levels of nesting.
I can generate the XML fine, but it generates a XML variable type.
If I use getClobVal() it removes the cariage returns.
Is there a simple way to convert the XML variable to a CLOB without losing the pretty formatting??
June 29, 2005 - 9:15 am UTC
why are carriage returns relevant in XML? whatever you use to view the XML will take care of that entirely?
Generate XML which conform to XSD
John, July 01, 2005 - 9:49 am UTC
How would I generate an XML document from XML-DB which conform to a registered Schema? My tables and xsd are as follows:
Customer table
==============
create table cust
( id number primary key,
last_name varchar2(50),
first_name varchar2(50),
fullname varchar2(50),
dob date
);
Cust_address table
==================
create table cust_address
(addr_id number primary key,
address_1 varchar2(100),
address_2 varchar2(100),
county varchar2(4),
home_ph varchar2(20),
office_ph varchar2(20),
cust_id number references cust(id) );
Some data
SQL> insert into cust
2 values( 1, 'Harvey','John','Jeff M Harvey','18-jun-80');
1 row created.
insert into cust_address
values ( 1,' 21 West Trophy Road','Memphis, TN','0009','901-6782345','901-768234',1);
My XMLSchema is as follows:
===========================
<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XMLSpy v2005 rel. 3 U (
http://www.altova.com by John -->
<xs:schema xmlns="
http://www.oaws.org/CAR" xmlns:xs="
http://www.w3.org/2001/XMLSchema" targetNamespace="
http://www.oaws.org/CAR" elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="CARData">
<xs:annotation>
<xs:documentation>Customer Contact information etc </xs:documentation>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element name="Name" type="NameType"/>
<xs:element name="Address" type="AddressType"/>
<xs:element name="PlayerContact">
<xs:complexType>
<xs:sequence>
<xs:element name="Name" type="NameType"/>
<xs:element name="Address" type="AddressType"/>
<xs:element name="PlayCntySher" type="xs:string"/>
<xs:element name="PlayCntyClk" type="xs:string"/>
<xs:element name="PlayAlsTyp" type="xs:string"/>
<xs:element name="Play" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="BankContact">
<xs:complexType>
<xs:sequence>
<xs:element name="BKName" type="xs:string"/>
<xs:element name="BKAddr1" type="xs:string"/>
<xs:element name="BKAddr2" type="xs:string"/>
<xs:element name="BKAddrfnl" type="xs:string"/>
<xs:element name="BKCNTY" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="LTRDT" type="xs:date"/>
</xs:complexType>
</xs:element>
<xs:complexType name="AddressType">
<xs:sequence>
<xs:element name="playaddr1">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:length value="100"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="playaddr2">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:length value="100"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="playaddrfnl" type="xs:string"/>
<xs:element name="playhomecomm">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:length value="100"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="playwrkcomm">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:length value="100"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="playcnty">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:length value="25"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="NameType">
<xs:sequence>
<xs:element name="PlayFullName" type="xs:string"/>
<xs:element name="PlayFName" type="xs:string"/>
<xs:element name="PlayLName" type="xs:string"/>
<xs:element name="PlayDefName" type="xs:string"/>
<xs:element name="PlayIDNum">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:length value="12"/>
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="PlayDob" type="xs:date"/>
</xs:sequence>
</xs:complexType>
<xs:simpleType name="money">
<xs:restriction base="xs:decimal">
<xs:totalDigits value="14"/>
<xs:fractionDigits value="2"/>
</xs:restriction>
</xs:simpleType>
<xs:complexType name="CollectorType">
<xs:sequence>
<xs:element name="CollectorNumber" type="xs:integer"/>
<xs:element name="CarFullName" type="xs:string"/>
<xs:element name="CollectorPhone" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:schema>
I have registered this schema in to my database schema
SQL> begin
2 dbms_xmlschema.registerSchema
3 (
4 '
http://tien.oaws.silver:8080/home/CAR/xsd/', 5 xdbURIType('/home/CAR/xsd/CARSchema.xsd').getClob(),
6 True,True,False,True
7 );
8 End;
9 /
PL/SQL procedure successfully completed.
Now I need to generate an XML which include all elements from CUST and CUST_ADDRESS table and must conform to the XML Schema, need your help.
Thank you.
John
July 01, 2005 - 10:35 am UTC
sorry to say that Sean is no longer with us at Oracle... So, I'll be referring XML specific questions elsewhere for now...
otn.oracle.com -> discussion forums perhaps.
Tom use your....
A reader, July 01, 2005 - 11:34 am UTC
..... great influence to get another XML guru.
If you get him a book deal it will sell. The most recent book for Oracle and XML is 3 years old. ( Dont like Oracle Press Books )
The XML forum on OTN is wanting.
great loss
John, July 01, 2005 - 12:20 pm UTC
Sean was very helpful to us. I wish him all the best in his career.
John
How to include CLOB column data in the whole XML document
A reader, July 21, 2005 - 8:43 am UTC
I know how to store a XML document as a CLOB and object relationally.
I know how to generate an XML document from a query.
What I want to know is when I generate a document from a query, and the table I queried has a clob column how can I get that column to be represented with the actual data inside the xml document instead of a tag that just says
<column>(CLOB)</column>
Do I have to make it a CDATA section? Can I do that with dbms_xmlquery.getXML?
Some twist to the XML result
Dinesh Kumar Dubey, July 26, 2005 - 3:50 am UTC
hi sean/tom,
following is the out put of the following query:-
select dbms_xmlgen.getxml('select * from emp e,dept d where e.deptno=d.deptno AND e.deptno=10 and e.job=''PRESIDENT''') from dual;
output:--
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-NOV-81</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</ROW>
</ROWSET>
But we need an output as below:-
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMP>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-NOV-81</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</EMP>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</DEPT>
</ROW>
</ROWSET>
THAT IS WE NEED THE XML OUTPUT AS SEPARATE FOR EACH TABLE.
how it is possible.
FYI ,we are using 9.2 database and w2k os.
Thanks
July 26, 2005 - 8:09 am UTC
scott@ORA9IR2> create or replace type empType as object
2 ( empno number,
3 ename varchar2(10),
4 job varchar2(14)
5 )
6 /
Type created.
scott@ORA9IR2>
scott@ORA9IR2> create or replace type deptType as object
2 ( deptno number,
3 dname varchar2(10),
4 loc varchar2(10)
5 )
6 /
Type created.
scott@ORA9IR2>
scott@ORA9IR2> select
2 dbms_xmlgen.getxml('
3 select empType(e.empno,e.ename,e.job) EMP,
4 deptType(d.deptno,d.dname,d.loc) DEPT
5 from emp e,dept d
6 where e.deptno=d.deptno
7 AND e.deptno=10
8 and e.job=''PRESIDENT''
9 ') from dual;
DBMS_XMLGEN.GETXML('SELECTEMPTYPE(E.EMPNO,E.ENAME,E.JOB)EMP,DEPTTYPE(D.DEPTNO,D
-------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMP>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
</EMP>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</DEPT>
</ROW>
</ROWSET>
Thanks
Dinesh Kumar Dubey, July 26, 2005 - 9:25 am UTC
Thanks a lot,
to both of you.
Thanks
Some more formating to the xml
Dinesh Kumar Dubey, July 26, 2005 - 9:40 am UTC
Hi Tom/Sean
I have changed the where clause of my previous query by removing the where job='PRESIDENT' part now the query is as below:-
SELECT
DBMS_XMLGEN.GETXML('
SELECT EMPTYPE(E.EMPNO,E.ENAME,E.JOB) EMP,
DEPTTYPE(D.DEPTNO,D.DNAME,D.LOC) DEPT
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.DEPTNO=10
') FROM DUAL
and its out put is :
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMP>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
</EMP>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</DEPT>
</ROW>
<ROW>
<EMP>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
</EMP>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</DEPT>
</ROW>
<ROW>
<EMP>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
</EMP>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</DEPT>
</ROW>
</ROWSET>
The following part is repeating a number of times it is fetching record from emp:
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</DEPT>
How can i have it once at top of the reultset.
Thanks a lot.
July 26, 2005 - 10:49 am UTC
scott@ORA9IR2> select
2 dbms_xmlgen.getxml('
3 select null EMP,
4 deptType(d.deptno,d.dname,d.loc) DEPT
5 from dept d
6 union all
7 select empType(e.empno,e.ename,e.job) EMP,
8 null DEPT
9 from emp e
10 ') from dual;
DBMS_XMLGEN.GETXML('SELECTNULLEMP,DEPTTYPE(D.DEPTNO,D.DNAME,D.LOC)DEPTFROMDEPTD
-------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</DEPT>
</ROW>
<ROW>
<DEPT>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
<LOC>DALLAS</LOC>
</DEPT>
</ROW>
<ROW>
<DEPT>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
<LOC>CHICAGO</LOC>
</DEPT>
</ROW>
<ROW>
<DEPT>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
<LOC>BOSTON</LOC>
</DEPT>
</ROW>
<ROW>
<EMP>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
</EMP>
</ROW>
<ROW>
<EMP>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
</EMP>
</ROW>
<ROW>
.......
Is object is the only way to group the data
Dinesh Kumar Dubey, July 26, 2005 - 10:33 am UTC
Hi Tom/Sean,
Thanks for the above solution, but is object is the only way to group the data table wise. Is there any other method instead of object that i can use to have the same output.
Thanks
XML is bourbon plague
Mikito Harakiri, July 26, 2005 - 1:03 pm UTC
The evolution of IT industry is a scaled down history of the civilization. Once upon a time an infectious decease spreads across the world. It is important for the IT working bees to develop an immunity.
Grouping XML without object
Dinesh Kumar Dubey, July 27, 2005 - 2:05 am UTC
Hi sean/Tom,
Thanks for your response. I got one more query, where i am not using object. The procedure is as below:
create or replace procedure xmlgentest4 as
VRET CLOB;
ctx dbms_xmlgen.ctxhandle;
begin
ctx:=dbms_xmlgen.newcontext('select cursor(Select ename,job,deptno from emp where deptno=d.deptno AND job=e.job) EMP,cursor(select * from dept d where e.deptno=d.deptno) DEPT from emp e,dept d where e.deptno=d.deptno AND e.deptno=10 AND e.job=''PRESIDENT''');
-- dbms_xmlgen.setrowtag(ctx,null);
vret:=dbms_xmlgen.getxml(ctx);
insert into a4 values(VRET,19);
COMMIT;
exception
when others then
dbms_output.put_line(SQLERRM);
End;
SQL> desc a4
Name Type Nullable Default Comments
---- ------ -------- ------- --------
C CLOB Y
N NUMBER Y
And the output from a4 table for 19 is as below:-
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMP>
<EMP_ROW>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<DEPTNO>10</DEPTNO>
</EMP_ROW>
</EMP>
<DEPT>
<DEPT_ROW>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</DEPT_ROW>
</DEPT>
</ROW>
</ROWSET>
This is perectly giving what i was looking for except two tags : <EMP_ROW> and <DEPT_ROW>.
Is there any method that i can remove these from my output.
Thanks and Regards
Dinesh
The XML QUERY
Dinesh Kumar Dubey, July 28, 2005 - 6:17 am UTC
Hi Sean/Tom,
This is a query that is giving me my expected result:-
SELECT XMLELEMENT("ROOT",XMLCONCAT(XMLELEMENT("DEPT",
XMLFOREST(D.DEPTNO,D.DNAME)),
XMLELEMENT("EMP",
XMLFOREST(E.EMPNO,E.ENAME)))) AS XMLDOC
FROM EMP E,DEPT D
WHERE
E.DEPTNO=D.DEPTNO
AND E.JOB='PRESIDENT'
AND D.DEPTNO=10
/
the output is:
<ROOT>
<DEPT>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
</DEPT>
<EMP>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
</EMP>
</ROOT>
here evrything is ok,but i need an <xml> tag at the top.How can i do that?
Thanks
Dinesh
Reverse the XML string from SYS_XMLGEN to a table
A reader, October 18, 2005 - 11:25 pm UTC
Hi Tom,
Is there a way to reverse the XML string generated from SYS_XMLGEN to its original table/grid format?
Thanks,
Peter
re: Reverse the XML string from SYS_XMLGEN to a table
A reader, October 18, 2005 - 11:48 pm UTC
For example, how can I convert the following XML document to 4 rows with 5 columns?
Thanks,
Peter
<?xml version="1.0"?>
<ROWSET>
<ROW>
<COMPANYNAME>MB</COMPANYNAME>
<WTD>10000</WTD>
<MTD>15000</MTD>
<QTD>30000</QTD>
<YTD>30000</YTD>
</ROW>
<ROW>
<COMPANYNAME>BMW</COMPANYNAME>
<WTD>5000</WTD>
<MTD>10000</MTD>
<QTD>60000</QTD>
<YTD>70000</YTD>
</ROW>
<ROW>
<COMPANYNAME>Jaguar</COMPANYNAME>
<WTD>3000</WTD>
<MTD>7000</MTD>
<QTD>10000</QTD>
<YTD>10000</YTD>
</ROW>
<ROW>
<COMPANYNAME>Audi</COMPANYNAME>
<WTD>10000</WTD>
<MTD>15000</MTD>
<QTD>15000</QTD>
<YTD>15000</YTD>
</ROW>
</ROWSET>
How to Change format whith xml utilities
JLU, December 21, 2005 - 5:47 am UTC
Hi Tom,
I don't find solution to change format whith xml utilities (my database is 8.1.7.4 with full JAVA package installation)...
My program is :
declare
fo UTL_FILE.FILE_TYPE;
v_context DBMS_XMLQuery.CTXTYPE;
v_document CLOB;
v_offset NUMBER := 1;
v_chunk VARCHAR2(4000);
v_chunk_size NUMBER := 4000;
begin
fo1 := UTL_FILE.FOPEN('/chemin','XML-TEST' || to_char(sysdate, 'YYYYMMDD-HH24') ||'.xml','w');
v_context:= DBMS_XMLQuery.NEWCONTEXT('
SELECT xxxxxx
from yyy
where zzz;
');
DBMS_XMLQuery.setRowsetTag(v_context,'purgePorteur');
DBMS_XMLQuery.setRowTag(v_context,'accepteur');
DBMS_XMLQuery.setRowIdAttrName(v_context,'term');
v_document := DBMS_XMLQuery.getXML(v_context);
WHILE( v_offset < DBMS_LOB.getLength( v_document))
LOOP
v_chunk := DBMS_LOB.SUBSTR(v_document,v_chunk_size,v_offset );
UTL_FILE.PUT(fo1, v_chunk );
v_offset := v_offset + v_chunk_size;
END LOOP;
DBMS_XMLQuery.closeContext(V_context);
end;
i obtain following file :
<?xml version="1.0" ?>
- <purgePorteur>
- <accepteur term="1">
<societe>41</societe>
- <PORTEUR>
- <PORTEUR_ROW num="1">
<contrat>6690644</contrat>
<paiement>P</paiement>
</PORTEUR_ROW>
</PORTEUR>
</accepteur>
- <accepteur term="2">
<societe>47</societe>
- <PORTEUR>
- <PORTEUR_ROW num="1">
<contrat>1467183</contrat>
<paiement>M</paiement>
</PORTEUR_ROW>
- <PORTEUR_ROW num="2">
<contrat>1467000</contrat>
<paiement>M</paiement>
</PORTEUR_ROW>
- <PORTEUR_ROW num="3">
<contrat>6002365</contrat>
<paiement>M</paiement>
</PORTEUR_ROW>
</PORTEUR>
</accepteur>
</purgePorteur>
But i want to obtain something like that :
<?xml version="1.0" ?>
- <purgePorteur sequence="33">
- <accepteur acc="9999999" term="001" devise="978">
<societe>41</societe>
- <PORTEUR id="1">
<contrat>6690644</contrat>
<paiement>P</paiement>
</PORTEUR>
Any idea to do that ?
Thank a lot in advance for your help.
any update to :Generate XML May 24, 2005 ?
Ali, February 27, 2006 - 8:12 am UTC
Was there an update to this? - Would be very interested as I am trying to do something very similar.
EG. I have a result set from all objects for tables and indexes, and an internal (pl/sql) tree which defines indexes as 'belonging' to tables and would like to nest index results within the table that they belong to?
Sure it must be possible to do it with xmlagg/concat/forest/element, but can't work it out yet?
cheers ali...
A reader, March 08, 2006 - 5:53 pm UTC
In your example above at the beginning of the page where you create a type DETAIL and a table DETAILS of that type and then a table MASTER...
Now I have a similar requirement but my query is going to contain 100 of fields returned, so will I create like 100 columns in table MASTER and also it is going to be dynamic ie., depending on the satisfied criteria, the fields returned may vary. How will I handle that?
Please explain.
Thanks.
March 09, 2006 - 12:56 pm UTC
in your code? I don't know what you mean....
A reader, March 08, 2006 - 5:58 pm UTC
Hi Tom,
I have a table as follows:
create table tab1
(
id number,
ln varchar2(20),
fn varchar2(20),
mn varchar2(20)
);
create table tab2
(
id number,
aln varchar2(20),
afn varchar2(20),
amn varchar2(20)
);
insert into tab1 values (1, 'LN1', 'FN1', 'MN1');
insert into tab1 values (2, 'LN2', 'FN2', 'MN2');
insert into tab1 values (3, 'LN3', 'FN3', 'MN3');
insert into tab2 values (1, 'ALN1', 'AFN1', 'AMN1');
insert into tab2 values (2, 'ALN2', 'AFN2', 'AMN2');
insert into tab2 values (3, 'ALN3', 'AFN3', 'AMN3');
commit;
If I write a piece of code to display the results in XML Format as follows:
declare
tctx dbms_xmlquery.ctxtype;
tqry varchar2(32767);
tres clob;
begin
tqry := ' select a.id, a.ln, a.fn, a.mn, b.aln, b.afn, b.amn '
|| ' from tab1 a, tab2 b where a.id = b.id and a.id = 1 ';
tctx:= dbms_xmlquery.newcontext(tqry);
tres:= dbms_xmlquery.getxml(tctx);
dbms_xmlquery.closecontext(tctx);
printclobout (tres);
end;
/
I get the result as
| <?xml version = '1.0'?>
| <ROWSET>
| <ROW num="1">
| <ID>1</ID>
| <LN>LN1</LN>
| <FN>FN1</FN>
| <MN>MN1</MN>
| <ALN>ALN1</ALN>
| <AFN>AFN1</AFN>
| <AMN>AMN1</AMN>
| </ROW>
| </ROWSET>
But now there is a new requirement where the table is defined such that now I have multiple entries in tab2 for aln, afn and amn like
insert into tab2 values (1, 'ALN1-2', 'AFN1-2', 'AMN1-2');
insert into tab2 values (1, 'ALN1-3', 'AFN1-3', 'AMN1-3');
commit;
Now when I execute the same piece of code I get the result as
| <?xml version = '1.0'?>
| <ROWSET>
| <ROW num="1">
| <ID>1</ID>
| <LN>LN1</LN>
| <FN>FN1</FN>
| <MN>MN1</MN>
| <ALN>ALN1</ALN>
| <AFN>AFN1</AFN>
| <AMN>AMN1</AMN>
| </ROW>
| <ROW num="2">
| <ID>1</ID>
| <LN>LN1</LN>
| <FN>FN1</FN>
| <MN>MN1</MN>
| <ALN>ALN1-2</ALN>
| <AFN>AFN1-2</AFN>
| <AMN>AMN1-2</AMN>
| </ROW>
| <ROW num="3">
| <ID>1</ID>
| <LN>LN1</LN>
| <FN>FN1</FN>
| <MN>MN1</MN>
| <ALN>ALN1-3</ALN>
| <AFN>AFN1-3</AFN>
| <AMN>AMN1-3</AMN>
| </ROW>
| </ROWSET>
But I would like the result to be
| <?xml version = '1.0'?>
| <ROWSET>
| <ROW num="1">
| <ID>1</ID>
| <LN>LN1</LN>
| <FN>FN1</FN>
| <MN>MN1</MN>
| <ALN1>ALN1</ALN1>
| <AFN1>AFN1</AFN1>
| <AMN1>AMN1</AMN1>
| <ALN2>ALN1-2</ALN2> --- see this second aln here and similarly for n number of matching records
| <AFN2>AFN1-2</AFN1>
| <AMN2>AMN1-2</AMN1>
| </ROW>
| </ROWSET>
How do I get the result like this or something similar to this ??
Please help.
Thanks.
A reader, March 09, 2006 - 12:58 pm UTC
I am having a similar problem like above. Please help.
A reader, March 09, 2006 - 5:58 pm UTC
When I am using dbms_xmlquery to generate xml from an Oracle database I am getting an error
| <ERROR>oracle.xml.sql.OracleXMLSQLException: Internal Error: Image is not a collection
image</ERROR>
Please let me know what this means.
March 10, 2006 - 11:56 am UTC
internal error = contact support?
No response ????
A reader, March 10, 2006 - 12:53 pm UTC
Hi Tom,
On Mar 8, 2006 I posted a question in this thread about 2 tables and how to generate xml result but I have not got any reply from you.
Please reply.
Thanks.
March 10, 2006 - 8:28 pm UTC
"reader"
sorry? I don't see them all, i do them fast, I skip some I cannot answer immediately.
likely means I didn't really have the time to study the problem and answer the new question - where followups and clarifications are to be posted (xml ain't my cup of tea)
PLSQL and XML Ungly
marc, March 13, 2006 - 4:16 pm UTC
Using sql in a string is very ungly(in my eyes). is there any way i can pass the result set through a ref cursor or some other container to the xml functions?
March 14, 2006 - 9:56 am UTC
Kato, April 27, 2006 - 4:31 pm UTC
Hello Tom:
I am generating XML with iso-8859-1 data.
How Can I change XML encode?
I want to generate this header:
<?xml version = '1.0' encoding="iso-8859-1"?>
but DBMS_XMLQuery generates this header:
<?xml version = '1.0'?>
I use this code:
v_ctx := DBMS_XMLQuery.newContext(cQuery);
-- Set parameters
DBMS_XMLQuery.setRowsetTag(v_ctx, 'ROWSET');
DBMS_XMLQuery.setRowTag(v_ctx, 'ROW');
DBMS_XMLQuery.setTagCase(v_ctx, DBMS_XMLQuery.LOWER_CASE);
-- Add an IE specfic XSL
DBMS_XMLQuery.setStylesheetHeader(v_ctx, 'Style.xsl', 'text/xsl');
-- Create the XML document.
v_xml := DBMS_XMLQuery.GetXML(v_ctx);
DBMS_XMLQuery.closeContext(v_ctx);
Thanks a lot.
Kato.
BLOB and XML
A reader, April 29, 2006 - 5:53 pm UTC
Is there any way to have blob columns included within XML generated from table content?
Bharat Kumaran, May 23, 2006 - 3:24 am UTC
That was great.But unfortunately,I am encountering the following problem.I have written a PL/SQL code that makes use of a CLOB variable.
l_clob clob := '<?xml version = "1.0"?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>SEANDILLON</ENAME>
<JOB>DBDUDE</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980 0:0:0</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
Though,it seems a simple declare stmt,but when I compile it, it gives me the following error
PLS-00382: expression is of wrong type
And the intersting part is even if I write it as a simple stmt as l_clob clob := 'Hello World';, the error persists.
I am workin on Oracle 8.1.7.Can you please guide me thru.
May 23, 2006 - 7:34 am UTC
In 8.1.7, you'll have to
declare the clob
dbms_lob.createtemporary
dbms_lob.write* to it.
clobs work more like varchar2's in software written this century.
Help with Nested tables and XML generation
Rahul, June 30, 2006 - 8:45 pm UTC
Hi Tom,
I have this problem:
--Version is oracle 9.2.0.5
CREATE OR REPLACE
TYPE edi_850ref_seg_type as object( ref01 varchar2(3),ref03 varchar2(39))
/
CREATE OR REPLACE
TYPE edi_850_refseg AS TABLE OF edi_850ref_seg_type
/
CREATE TABLE edi_850hdr
(headerid NUMBER(10,0),
column1 VARCHAR2(15),
refseg EDI_850_REFSEG)
NESTED TABLE refseg STORE AS edi_850_ref_nt
/
-------I inserted two rows into the table---
select * from edi_850hdr;
HEADERID COLUMN1 REFSEG(REF01, REF03)
-------- -------------- --------------------------------------------------
215544 123456789 EDI_850_REFSEG(EDI_850REF_SEG_TYPE('ZZ', 'L/T'), EDI_850REF_SEG_TYPE('ZZ', 'xxxxx'), EDI_850REF_SEG_TYPE('ZZ', 'SHPMTS'))
215545 987654321 EDI_850_REFSEG(EDI_850REF_SEG_TYPE('ZZ', 'L/T'), EDI_850REF_SEG_TYPE('ZZ', 'yyyyy'), EDI_850REF_SEG_TYPE('ZZ', 'SHPMTS'))
---Now, I use this xml to generate the xml right below this select statement for the regular columns in the table----
SELECT XMLELEMENT
("Record",
xmlattributes (ROWNUM AS "Index"),
XMLELEMENT ("ValueMark",
xmlattributes (14 AS "Index"),
XMLELEMENT ("SubValueMark",
xmlattributes (1 AS "Index",
'SomeColumn' AS "Name",
column1 AS "Value"
)
)
)
).getstringval () as "Result"
FROM edi_850hdr t;
Result
---I pretty printed the xmlelements (two rows) generated----
<Record Index="1">
<ValueMark Index="14">
<SubValueMark Index="1" Name="SomeColumn" Value="123456789"/>
</ValueMark>
</Record>
<Record Index="2">
<ValueMark Index="14">
<SubValueMark Index="1" Name="SomeColumn" Value="987654321"/>
</ValueMark>
</Record>
This is well and fine, but, I need an output for something like this:
--pretty printed again----
<Record Index="1">
<ValueMark Index="14">
<SubValueMark Index="1" Name="SomeColumn" Value="123456789"/>
</ValueMark>
<ValueMark Index="23">
<SubValueMark Index="1" Name="Comments" Value="L/T"/>
<SubValueMark Index="2" Name="Comments" Value="xxxxx"/>
<SubValueMark Index="3" Name="Comments" Value="SHPMTS"/>
</ValueMark>
</Record>
<Record Index="2">
<ValueMark Index="14">
<SubValueMark Index="1" Name="SomeColumn" Value="987654321"/>
</ValueMark>
<ValueMark Index="23">
<SubValueMark Index="1" Name="Comments" Value="L/T"/>
<SubValueMark Index="2" Name="Comments" Value="yyyyy"/>
<SubValueMark Index="3" Name="Comments" Value="SHPMTS"/>
</ValueMark>
</Record>
I tried many things until I got stuck here
SELECT XMLELEMENT
("Record",
xmlattributes (ROWNUM AS "Index"),
XMLELEMENT ("ValueMark",
xmlattributes (14 AS "Index"),
XMLELEMENT ("SubValueMark",
xmlattributes (1 AS "Index",
'SomeColumn' AS "Name",
column1 AS "Value"
)
)
),
XMLELEMENT
("ValueMark",
xmlattributes (23 AS "Index"),
XMLELEMENT ("SubValueMark",
xmlattributes (CURSOR (SELECT ROWNUM
FROM TABLE (refseg)) AS "Index",
'Comments' AS "Name",
CURSOR (SELECT ref03
FROM TABLE (refseg)) AS "Value"
)
)
)
).getstringval ()
FROM edi_850hdr t;
I get this error:
xmlattributes (CURSOR (SELECT ref01
*
ORA-22902: CURSOR expression not allowed
Is this even possible using sqlx functions? If not, please guide me the route I should take.
Thank you,
Rahul
Answer for the above question
Rahul, July 02, 2006 - 1:43 pm UTC
Tom,
Would you be able to take a look at the question above? Appreciate it.
Got the answer
Rahul, July 03, 2006 - 4:40 pm UTC
Tom,
Never mind. I got the answer to this one.
SELECT XMLELEMENT
("Record",
xmlattributes (ROWNUM AS "Index"),
XMLELEMENT ("ValueMark",
xmlattributes (14 AS "Index"),
XMLELEMENT ("SubValueMark",
xmlattributes (1 AS "Index",
'SomeColumn' AS "Name",
column1 AS "Value"
)
)
),
XMLELEMENT
("ValueMark",
(SELECT XMLAGG (XMLELEMENT ("SubValueMark",
xmlattributes (ROWNUM AS "Index",
'Comments' AS "Name",
m.ref03 AS "Value"
)
)
)
FROM TABLE (refseg) m)
)
).getstringval ()
FROM edi_850hdr t;
Carriage Returns
Yuan, September 20, 2006 - 12:23 pm UTC
I saw you enter this quote from above in this thread:
<quote>why are carriage returns relevant in XML? whatever you use to view the XML will
take care of that entirely? </quote>
They are relevant to me because my requirement is to generate XML files from Oracle 9.2. I use SQLX to create the XML then UTL_FILE to write out the clob value to a file. When I hit the 32K limitation, I get:
ORA-19011: Character string buffer too small
If I put in a new line arbitrarily every 32K, it would likely cause a problem. So I need to know how to make the XML returned from SQLX "pretty." Either that or find another way to write an XMLTYPE to a file.
Please don't ask me why we need to generate such large XML files.
September 20, 2006 - 3:41 pm UTC
or use a more "sophisticated api" like java to write the OS file - that'll be the only logical suggest I have (java stored procedure or external java program)
Never mind!
Yuan, September 20, 2006 - 3:30 pm UTC
Never mind, I found an alternative to UTL_FILE.
DBMS_XMLDOM.WRITETOFILE
Making sqlx pretty.
Rahul, October 04, 2006 - 6:37 pm UTC
>So I need to know how to make the XML returned from SQLX "pretty."
extract('/*') will do it for you.
SQL> select xmltype('<root><leaf1>hi</leaf1><leaf2>hey</leaf2></root>') from dual;
XMLTYPE('<ROOT><LEAF1>HI</LEAF1><LEAF2>HEY</LEAF2></ROOT>')
--------------------------------------------------------------------------------------------------------------------------------------------
<root><leaf1>hi</leaf1><leaf2>hey</leaf2></root>
SQL> select xmltype('<root><leaf1>hi</leaf1><leaf2>hey</leaf2></root>').extract('/*') from dual;
XMLTYPE('<ROOT><LEAF1>HI</LEAF1><LEAF2>HEY</LEAF2></ROOT>').EXTRACT('/*')
--------------------------------------------------------------------------------------------------------------------------------------------
<root>
<leaf1>hi</leaf1>
<leaf2>hey</leaf2>
</root>
Grouping on JOB
Dinesh, October 11, 2006 - 2:56 am UTC
Hi Tom/Sean,
Please help me on the following :
The below query is giving me the following output -
select dbms_xmlgen.getxml('SELECT * FROM EMP WHERE DEPTNO=10') FROM DUAL
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>09-JUN-81</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>17-NOV-81</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>MANAGER</JOB>
<MGR>7782</MGR>
<HIREDATE>23-JAN-82</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
</ROWSET>
But the need is a group by on JOB,as the above output is having two MANAGER , can we have these MANAGER in a single node.
Thank You
Regards
cleaned up text from xml
sara, October 11, 2006 - 11:48 am UTC
I have an XML content like the following:
<USAGE>
<BASE_PRICE>450</BASE_PRICE>
<CATEGORY categoryID='94'>Product</CATEGORY>
<USAGE UsageID='156'>Calendar</USAGE>
<RULE_GROUP>118</RULE_GROUP>
<RULE_GROUP_COUNTRY>USA</RULE_GROUP_COUNTRY>
<OPTION>
<OPTION_TEXT>Duration</OPTION_TEXT>
<VALUE valueID='2062'>Up to 1 Year</VALUE>
<MODIFIER></MODIFIER>
</OPTION>
<OPTION>
<OPTION_TEXT>Placement</OPTION_TEXT>
<VALUE valueID='2058'>Inside</VALUE>
<MODIFIER></MODIFIER>
</OPTION>
<OPTION>
<OPTION_TEXT>Image Size</OPTION_TEXT>
<VALUE valueID='2061'>Up to Full Page</VALUE>
<MODIFIER></MODIFIER>
</OPTION>
<OPTION>
<OPTION_TEXT>Circulation</OPTION_TEXT>
<VALUE valueID='2053'>Up to 25,000</VALUE>
<MODIFIER></MODIFIER>
</OPTION>
<REGIONS>
<REGION regionID='22'>
<REGION_NAME>Worldwide</REGION_NAME>
<REGION_MODIFIER></REGION_MODIFIER>
</REGION>
</REGIONS>
<COUNTRY />
<INDUSTRIES>
<INDUSTRY industryID='287' lastUpdatedID='1' lastUpdatedTS='12/1/2003 3:06:40 PM'>
<INDUSTRY_NAME>All</INDUSTRY_NAME>
<INDUSTRY_DESC></INDUSTRY_DESC>
<PARENT>
<INDUSTRIES>
<INDUSTRY industryID='286' lastUpdatedID='0' lastUpdatedTS='12/1/2003 3:16:42 PM'>
<INDUSTRY_NAME>Retail</INDUSTRY_NAME>
<INDUSTRY_DESC></INDUSTRY_DESC>
</INDUSTRY>
</INDUSTRIES>
</PARENT>
</INDUSTRY>
</INDUSTRIES>
<START_DATE>
<START_MONTH>6</START_MONTH>
<START_DAY>1</START_DAY>
<START_YEAR>2007</START_YEAR>
</START_DATE>
<END_DATE>
<END_MONTH>12</END_MONTH>
<END_DAY>31</END_DAY>
<END_YEAR>2008</END_YEAR>
</END_DATE>
<IMG_VARIANCE>
<RESOLUTION>HIGH</RESOLUTION>
<DPI>300</DPI>
<IMG_HEIGHT>4086</IMG_HEIGHT>
<IMG_WIDTH>5161</IMG_WIDTH>
<FILE_SIZE>63263538</FILE_SIZE>
<ITEM_PRICE_RULE_NBR>2</ITEM_PRICE_RULE_NBR>
</IMG_VARIANCE>
</USAGE>
I created an XSL stylesheet to get the cleaned up text format like below:
Size: 5161 x 4086 @ 300 dpi, high-res (60.33 Mb)
Dates: 1-JUN-2007 to 31-DEC-2008
Usage: Product/Calendar
Usage Details: Duration: Up to 1 Year; Placement: Inside; Image Size: Up to Full Page; Circulation: Up to 25,000
Regions: Worldwide
Industries: Retail/All
But is there any other way to get the cleared text without using the style sheet. I tried using dbms_smlsave which doesnt seem to work.
Thanks in advance
- Sara
Thanks
Nikolay, January 05, 2007 - 8:11 am UTC
These examples were very useful. Thank you Tom and the others.
DBMS_XMLDOM and namespaces
Mike Friedman, February 01, 2007 - 2:52 am UTC
Tom,
We're trying to create an MS Excel spreadsheet (in XML) using DBMS_XMLDOM in the database but namespaces are giving us coniptions.
10gR1
How can I generate something like this using DBMS_XMLDOM?
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Version>11.5606</Version>
</DocumentProperties>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1">
</Table>
</Worksheet>
</Workbook>
DBMS_XMLDOM
Yuan, March 01, 2007 - 11:40 am UTC
I'm having a lot of trouble using DBMS_XMLDOM. Here is an example of the trouble that I am having.
DECLARE
ldParent DBMS_XMLDOM.DOMDOCUMENT := DBMS_XMLDOM.NEWDOMDOCUMENT(XMLTYPE('<Parent/>'));
lnParent DBMS_XMLDOM.DOMNODE := DBMS_XMLDOM.MAKENODE(ldParent);
ldChild DBMS_XMLDOM.DOMDOCUMENT := DBMS_XMLDOM.NEWDOMDOCUMENT(XMLTYPE('<Child>abc</Child>'));
lnChild DBMS_XMLDOM.DOMNODE := DBMS_XMLDOM.MAKENODE(ldChild);
ln DBMS_XMLDOM.DOMNODE;
lv VARCHAR2(4000);
BEGIN
ln := DBMS_XMLDOM.IMPORTNODE(ldParent, lnChild, TRUE);
lnParent := DBMS_XMLDOM.APPENDCHILD(lnParent, ln);
DBMS_XMLDOM.WRITETOBUFFER(lnParent, lv);
DBMS_OUTPUT.PUT_LINE(lv);
END;
The output that I am looking for is:
<Parent>
<Child>abc</Child>
</Parent>
In 9.2.0.7, I am getting:
<Child>abc</Child>
In 10.2.0.3, I am getting ORA-31185: DOM Nodes do not belong to the same DOM Document, even though I imported the node to the same document. By the way, the documentation (
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xmldom.htm#i1098435 ) for ImportNode says:
Syntax
DBMS_XMLDOM.IMPORTNODE(
doc IN DOMDOCUMENT,
importedNode IN DOMNODE,
deep IN BOOLEAN)
RETURN DOMNODE;
Parameters
Table 124-86 IMPORTNODE Function Parameters
Parameter Description
doc Document from which the node is imported
importedNode Node to import
deep Setting for recursive import.
I believe that the description of doc should be Document into which the node is imported. Otherwise, how will it know where you're importing? In case I was wrong, I tried switching my call to use ldChild rather than ldParent and got the same error.
Please help!
DBMS_XMLDOM (continued)
Yuan, March 01, 2007 - 4:01 pm UTC
Well I think I've made some progress. This works in 9.2.0.7, but in 10.2.0.3 I still get that same error.
DECLARE
ldocParent DBMS_XMLDOM.DOMDOCUMENT;
ldocChild DBMS_XMLDOM.DOMDOCUMENT;
lelementParent DBMS_XMLDOM.DOMELEMENT;
lelementChild DBMS_XMLDOM.DOMELEMENT;
lelementParentNew DBMS_XMLDOM.DOMELEMENT;
lelementChildNew DBMS_XMLDOM.DOMELEMENT;
ln DBMS_XMLDOM.DOMNODE;
lv VARCHAR2(4000);
BEGIN
ldocParent := DBMS_XMLDOM.NEWDOMDOCUMENT(XMLTYPE('<Parent/>'));
ldocChild := DBMS_XMLDOM.NEWDOMDOCUMENT(XMLTYPE('<Child>abc</Child>'));
lelementParent := DBMS_XMLDOM.GETDOCUMENTELEMENT(ldocParent);
lelementChild := DBMS_XMLDOM.GETDOCUMENTELEMENT(ldocChild);
lelementParentNew := DBMS_XMLDOM.MAKEELEMENT(DBMS_XMLDOM.MAKENODE(lelementParent));
lelementChildNew := DBMS_XMLDOM.MAKEELEMENT(DBMS_XMLDOM.MAKENODE(lelementChild));
ln := DBMS_XMLDOM.APPENDCHILD(DBMS_XMLDOM.MAKENODE(lelementParentNew), DBMS_XMLDOM.MAKENODE(lelementChildNew));
DBMS_XMLDOM.WRITETOBUFFER(DBMS_XMLDOM.MAKENODE(lelementParentNew), lv);
DBMS_OUTPUT.PUT_LINE(lv);
END;
I still find AppendChild very confusing. The desired results seems to be in the first parameter, rather than the return value.
Figured it out
Yuan, March 08, 2007 - 10:18 am UTC
I figured out what I was doing wrong. DBMS_XMLDOM had me very confused because it seems to treat variables differently. For example, a variable of type DOMNode is not a free-floating node, but a pointer to the node in the DOMDocument's XML. Manipulating the node variables actually manipulates the DOMDocument's XML.
To Mike Friedman
Yuan, March 08, 2007 - 10:49 am UTC
Mike, I had a lot of trouble with namespaces as well, but was able to get it to work. If you give some sample code that is failing, maybe I can help you with it.
xml
A reader, May 28, 2008 - 7:32 pm UTC
issue with xmltYPE.transform
Vaibhav, October 09, 2008 - 6:06 am UTC
Hi Tom,
i know you are not in xml. it would be great if can get this one answered from your xml gurus.
I have the following:
create table xsl_to_xml
(
tran_id varchar2(1000) primary key,
service_id varchar2(1000),
payload xmltype
);
CREATE OR REPLACE PROCEDURE xml2tab(p_xml_in XMLType,
p_xsl_in XMLType,
p_table IN VARCHAR2 ) AS
v_context DBMS_XMLStore.ctxType;
v_rows NUMBER;
BEGIN
--Open a new context, required for these procedures
v_context := DBMS_XMLStore.newContext(p_table);
-- This is the meat of the procedure. See below for an explanation.
v_rows := DBMS_XMLStore.insertXML(
v_context,
XMLType.transform(p_xml_in, p_xsl_in));
-- Close the context
DBMS_XMLStore.closeContext(v_context);
commit;
END;
/
DECLARE
v_xml XMLType;
v_xsl XMLType;
BEGIN
v_xml := XMLType('<Message>
<Header>
<tran_id>AAAA-BBBB-CCCC-DDDD</tran_id>
<interaction_id>urn:spine:interactionid:onsmatcheddeathresponse</interaction_id>
<messageId>ONS88881111-65D3-EC42-BC31-62522532BIND</messageId>
<originating_party_id>urn:spine:tmsclient:PDS</originating_party_id>
</Header>
<Body>
<payload><tag1>gdfgdfgdfgdfgfd</tag1></payload>
</Body>
</Message>');
v_xsl := XMLType('<xsl:stylesheet version="2.0" xmlns:xsl="
http://www.w3.org/1999/XSL/Transform" >
<xsl:template match="/">
<ROWSET>
<ROW>
<TRAN_ID>
<xsl:value-of select="/Message/Header/tran_id"/>
</TRAN_ID>
<SERVICE_ID>
<xsl:value-of select="/Message/Header/service_id"/>
</SERVICE_ID>
</ROW>
</ROWSET>
</xsl:template>
</xsl:stylesheet>');
xml2tab(v_xml, v_xsl, 'xsl_to_xml');
commit;
END;
/
Select * from xsl_to_xml;
1 row selected;
truncate table xsl_to_xml;
DECLARE
v_xml XMLType;
v_xsl XMLType;
BEGIN
v_xml := XMLType('<Message>
<Header>
<tran_id>AAAA-BBBB-CCCC-DDDD</tran_id>
<interaction_id>urn:spine:interactionid:onsmatcheddeathresponse</interaction_id>
<messageId>ONS88881111-65D3-EC42-BC31-62522532BIND</messageId>
<originating_party_id>urn:spine:tmsclient:PDS</originating_party_id>
</Header>
<Body>
<payload><tag1>gdfgdfgdfgdfgfd</tag1></payload>
</Body>
</Message>');
v_xsl := XMLType('<xsl:stylesheet version="2.0" xmlns:xsl="
http://www.w3.org/1999/XSL/Transform" >
<xsl:template match="/">
<ROWSET>
<ROW>
<TRAN_ID>
<xsl:value-of select="/Message/Header/tran_id"/>
</TRAN_ID>
<SERVICE_ID>
<xsl:value-of select="/Message/Header/service_id"/>
</SERVICE_ID>
<PAYLOAD>
<xsl:copy-of select="/Message/Body/payload"/>
</PAYLOAD>
</ROW>
</ROWSET>
</xsl:template>
</xsl:stylesheet>');
xml2tab(v_xml, v_xsl, 'xsl_to_xml');
commit;
END;
/
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00222: error received from SAX callback function
ORA-01008: not all variables bound
ORA-06512: at "SYS.DBMS_XMLSTORE", line 78
ORA-06512: at "SYS.DBMS_XMLSTORE", line 88
ORA-06512: at "SYS.XML2TAB", line 14
why does this fail...
my requirement is that i should parse the header tag of the message, get my desired values. store them in the table.
now i go to the body tag, then to the payload tag...and copy everything that is there in it.
i.e. i want the entire xml message present in the payload tag to go in the payload column
hence, i hv used copy-of and declared the payload column as XMLType
is this a bug...y does it give aot all variables bound
i am using oracle 10gR1..
please help me...its urgent...
thanks in advance...
October 09, 2008 - 10:48 am UTC
otn.oracle.com has an XML discussion forum....
XML
A reader, October 10, 2008 - 11:20 pm UTC
Creating XML to comply with an unhelpful schema
Alex Elsworth, October 21, 2009 - 4:34 am UTC
Hi Tom
I'm having a problem in creating XML to comply with an industry standard XSD.
We have users and loans to those users:
create table users
(barcode varchar2(50)
,fname varchar2(50)
,sname varchar2(50)
);
create table loans
(barcode varchar2(50)
,title varchar2(100)
,issue_date date);
/
insert into users values ('A1', 'Bob', 'Jones');
insert into loans values ('A1', 'Title 1', sysdate-20);
insert into loans values ('A1', 'Title 2', sysdate-10);
insert into loans values ('A1', 'Title 3', sysdate-5);
I need to produce XML for the user and their loan items which would look like this:
<User>
<Barcode>A1</Barcode>
<Fname>Bob</Fname>
<Sname>Jones</Sname>
<LoanedItem>
<Title>Title 1</Title>
<IssueDate>01-OCT-2009</IssueDate>
</LoanedItem>
<LoanedItem>
<Title>Title 2</Title>
<IssueDate>11-OCT-2009</IssueDate>
</LoanedItem>
<LoanedItem>
<Title>Title 3</Title>
<IssueDate>16-OCT-2009</IssueDate>
</LoanedItem>
</User>
I've tried various approaches using objects and sys_xmlgen or various combinations of xmlelement, xmlforest, xmlagg etc but all of the approaches I've tried nest the loaned items inside the user details so we end up with something like this:
<User>
<Barcode>A1</Barcode>
<Fname>Bob</Fname>
<Sname>Jones</Sname>
<LoanedItems>
<LoanedItem>
<Title>Title 1</Title>
<IssueDate>01-OCT-2009</IssueDate>
</LoanedItem>
<LoanedItem>
<Title>Title 2</Title>
<IssueDate>11-OCT-2009</IssueDate>
</LoanedItem>
<LoanedItem>
<Title>Title 3</Title>
<IssueDate>16-OCT-2009</IssueDate>
</LoanedItem>
</LoanedItems>
</User>
This is a perfectly sensible thing to do in terms of the structure of the data, but it is not what the schema requires and the schema will not be changed.
Can you see any way of creating XML in the required format?
Many thanks
Alex
October 23, 2009 - 11:22 am UTC
suggest you hit the XML forums on otn.oracle.com
I don't do XML.
Found a way, but...
Alex Elsworth, October 21, 2009 - 5:30 am UTC
Hi Tom
Before you waste any of your time responding to my previous post, I have found a method which works.
I have created a function which creates the XML for the user data and another one which creates the XML for the loaned items and am using xmlconcat to join them together.
Thanks
Alex
Oracle XML or Java
Arvind Mishra, November 26, 2009 - 9:04 am UTC
Hello Tom,
I am working on an application in which we have to read data from Oracle 10g rel 1 database and generate xml and give it to a third part reporting tool to generate graphs. I can choose pl/sql or Java to generate the xml. Which one will be good from performance point of view?
Thanks,
Arvind
November 28, 2009 - 1:16 pm UTC
you can choose sql or java you mean.
You might start with SQL, you might be able to generate 100% of what you need via a view.
My RSS feeds are all "views" for example.
Java or Sql
Arvind Mishra, November 30, 2009 - 5:26 pm UTC
Thanks Tom
A reader, February 08, 2010 - 3:32 am UTC
SELECT dbms_xmlgen.getxml('select * T_TEST') from dual
ID NAME SAL
---------- --------- ----------
1 JON 35
2 RON
<?xml version="1.0" ?>
<ROWSET>
<ROW>
<ID>1</ID>
<NAME>JON</NAME>
<SAL>35</SAL>
</ROW>
<ROW>
<ID>1</ID>
<NAME>RON</NAME>
</ROW> <<---- <SAL></SAL>
</ROWSET>
how do make plase
February 15, 2010 - 10:02 am UTC
You would have to use the dbms_xmlgen.SETNULLHANDLING routine.
ops$tkyte%ORA11GR2> declare
2 l_ctx dbms_xmlgen.ctxHandle := dbms_xmlgen.newContext('select ename, comm from scott.emp where ename like ''A%''');
3 l_lob clob;
4 begin
5 dbms_xmlgen.setNullHandling( l_ctx, dbms_xmlgen.EMPTY_TAG );
6 l_lob := dbms_xmlgen.getXml( l_ctx );
7 dbms_output.put_line( l_lob );
8 dbms_lob.freeTemporary( l_lob );
9 end;
10 /
<?xml version="1.0"?>
<ROWSET>
<ROW>
<ENAME>ALLEN</ENAME>
<COMM>300</COMM>
</ROW>
<ROW>
<ENAME>ADAMS</ENAME>
<COMM/>
</ROW>
</ROWSET>
PL/SQL procedure successfully completed.
Creating XML
Harsha, April 12, 2011 - 8:01 am UTC
Hi Tom, I just wondering this post started 2002, now with 10.2 or 11, what would be the most effective way to do the 2002 question (Reading from Xml and inset into a table and reading from it)?
(I try to create a web service, so looking into crate xml from oracle pl/sql)
Regards,
Harsha
NULL handling using sys_xmlgen
Nilanjan Ray, January 05, 2012 - 9:13 am UTC
Hi Tom,
I know we can handle null values using dbms_xmlgen, but is there a way to create empty tags
using sys_xmlgen and user defined types?
My test case is below.
create or replace type test_typ as object
(id number
,name varchar2(50)
);
/
create or replace type test_typ_tab as varray(10) of test_typ;
/
Declare
l_test test_typ_tab := test_typ_tab();
l_clob clob;
l_xml xmltype;
Begin
For i in 1..10
Loop
l_test.extend;
if mod(i,2) <> 0 then
l_test(i) := test_typ(i,NULL);
Else
l_test(i) := test_typ(i,'Name'||i);
end if;
End loop;
select sys_xmlgen(l_test)
into l_xml
from dual;
l_clob := l_xml.getclobval();
dbms_output.put_line(l_clob);
End;
/
Output (NAME tag is missing wherever the value is NULL)
------
<ROW>
<TEST_TYP>
<ID>1</ID>
</TEST_TYP>
<TEST_TYP>
<ID>2</ID>
<NAME>Name2</NAME>
</TEST_TYP>
<TEST_TYP>
<ID>3</ID>
</TEST_TYP>
<TEST_TYP>
<ID>4</ID>
<NAME>Name4</NAME>
</TEST_TYP>
<TEST_TYP>
<ID>5</ID>
</TEST_TYP>
<TEST_TYP>
<ID>6</ID>
<NAME>Name6</NAME>
</TEST_TYP>
<TEST_TYP>
<ID>7</ID>
</TEST_TYP>
<TEST_TYP>
<ID>8</ID>
<NAME>Name8</NAME>
</TEST_TYP>
<TEST_TYP>
<ID>9</ID>
</TEST_TYP>
<TEST_TYP>
<ID>10</ID>
<NAME>Name10</NAME>
</TEST_TYP>
</ROW>
Thanks
Regards
Nilanjan
framing XML using oracle plsql takes time- How to do faster ?
Kushik, December 05, 2012 - 7:14 am UTC
Hi TOM,
I understand from some of the database specialist of my project says framing XMLs using oracle pl/sql will result in degrading the system performance consiming more of oracles memory. How can I build complex xmls fastly using oracle pl/sql.
Requirement : to return CLOB as XML (output of a complex query using multiple search conditions which can be decided during run time only based on the user input).
Please suggest how to do faster retrival from oracle DB by building XML.