Amazing... Thanks a lot for the help.

Tom,

How to parse data in this format to retrieve back the canonical oracle form to insert into tables?

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: 

XDK Developer's Guide XML SQL Utility Chapter: 

Hope that helps!

_smd_
_____________________________________________________________________
Sean Dillon - Senior Technologist, Oracle Corporation
Author "Beginning Oracle Programming" 

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>

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:  

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" 

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.
 

Sorry Tom, i forgot to give the link where its specified that "sys_xmlgen()" function is 9i feature. here is;

</code> http://otn.oracle.com/products/oracle9i/daily/sept14.html <code>

Thanks,
Staline.X

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

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

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>  

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.

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>

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

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,

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?

is the pointer to the XML documentation -- all of the links were into those docs.

here are the "updated" links.

SYS_XMLGEN: 

SYS_XMLGEN with objects: 

SYS_XMLAGG: 

Generating XML data from the database (The SQL XML stuff): 

To do DOM parsing, you'd use the XML Developer's Kits.  Here's the table of 
contents to the XDK Dev Guide: 

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.

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>
 

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?

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?

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>

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?

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

Thanks, I will do that.

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.

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

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

Any Update on this?

Thanks for sharing you expertise (you too Tom).

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,

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

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>&gt;</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>&gt;</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>&gt;</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>&gt;</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>&gt;</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>&gt;</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.

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.

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.

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.

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. 

.... 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
) 

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_
 

..... 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> 

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>

 
 

..... 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.

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

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 

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.
 

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>

SQL> set long 50000

else sqlplus just shows a bit of the big strings. 

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

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.

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??

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 

..... 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.

Sean was very helpful to us. I wish him all the best in his career.

John

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?

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

Thanks a lot,
to both of you.

Thanks

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.

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

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.

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 

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

Hi Tom,

Is there a way to reverse the XML string generated from SYS_XMLGEN to its original table/grid format?

Thanks,
Peter

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>

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.

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...

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.

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.

I am having a similar problem like above. Please help.

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.

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.

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?

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.

Is there any way to have blob columns included within XML generated from table content?

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.

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

Tom,

Would you be able to take a look at the question above? Appreciate it.

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;

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.

Never mind, I found an alternative to UTL_FILE.

DBMS_XMLDOM.WRITETOFILE

>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>

 

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

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

These examples were very useful. Thank you Tom and the others.

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>

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!

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.

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.

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.

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...

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

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

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

Thanks Tom

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

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.

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

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

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.