Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Vipin.

Asked: August 10, 2003 - 8:43 pm UTC

Last updated: August 05, 2007 - 12:36 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

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

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



and Tom said...

I asked Sean Dillon, our local XML technologist, to take a look at this and here's what he had to say:

Hi Vipin,

SYS_XMLGEN is a native SQL function that returns as an XML document the results of a passed-in SQL query. SYS_XMLGEN is a part of the XML Developer's Kits (XDKs), which are back-portable to Oracle 8.1.6.
XMLGEN was a package for inserting XML into tables and generating XML from queries. XMLGEN is deprecated. Use DBMS_XMLSave and DBMS_XMLQuery now, which are packages shipped with the XDKs.
DBMS_XMLGEN is a supplied package with Oracle9i. It converts the results of a SQL query to a cononical XML format. It takes an arbitrary SQL query as input, and converts it to XML format and returns as a CLOB. It's much the same as the DBMS_XMLQuery package in the XDK, but it's linked to the C libraries and compiled into the database kernel, offering faster speeds (XMLQuery uses Java under the covers).
SQLX is a set of operators that can be included in a relational query meant to return an XML document (or XML fragment). SQLX, in my opinion, should be used above all else for generating XML as it is also linked into the kernel, is used in a SQL statement (which is typically faster than invoking a PL/SQL stack), and is FAR more flexible than DBMS_XMLQuery or DBMS_XMLGEN in terms of the XML structure you may want to create.

According to the info you submitted, you're on Oracle8.1.7. This means you cannot use SQLX or DBMS_XMLGEN. Your only option would be to download the latest stable version of the XDKs from: </code> http://otn.oracle.com/tech/xml/xdkhome.html
As an example of SQLX, check out: 
http://asktom.oracle.com/~sdillon/rss.html
This is an example of using SQLX to produce an RSS report of Ask Tom news feeds.  I include the SQLX statement used to create one of the reports, and this gives you an idea of how to make a fairly simple XML document.  This report, however, would be pretty difficult to make from DBMS_XMLQuery or DBMS_XMLGEN (I don't think it's impossible, just difficult ;-)).  I hope that helps.

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

Rating

  (54 ratings)

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

Comments

Few more doubts

Vipin, August 11, 2003 - 11:41 am UTC

Thanks Sean,

That was wonderful explanation.

Could you clear the following doubts also:-

1. Is SYS_XMLGEN SQL function written using SQLX function or are they C based routines like DBMS_XMLGEN.

2. Isn't DBMS_XMLQuery written using JAVA, which pulls them a step back from DBMS_XMLGen which is written in C(performance wise.) I think the most apt place for choosing DBMS_XMLQuery is when you need bind variables in your query(the resultset of which has to converted to XML.)

3. From your AskTom RSS examples it makes very clear that SQLX can just acheive anything you want and from your explanation it is understood that we need to go for it whenever possible since these can used in SQL queries. But my doubts here is if you have a situation where in you need to use a nested table structure? i.e. a requirement where in you have a set of items for tag something like DEPT tag holding EMPLOYEE LIST tag which include some employees , their names, their hiredates as separate tags.

In this scenario shouldn't we use objects, nested tables and SYS_XMLGEN function to generate the XML or else still SQLX functions can acheive it?

I am scared to use nested tables or varrays (becuase there might be some performance impact.) , Hide this statement from Tom ;)





Vipin, August 13, 2003 - 2:33 pm UTC

Hi Tom/Sean


any help regarding this?????

Tom Kyte
August 14, 2003 - 4:12 pm UTC

That was wonderful explanation.

Could you clear the following doubts also:-

1. Is SYS_XMLGEN SQL function written using SQLX function or are they C based
routines like DBMS_XMLGEN.

. SYS_XMLGEN is C based and linked into the kernel.

2. Isn't DBMS_XMLQuery written using JAVA, which pulls them a step back from
DBMS_XMLGen which is written in C(performance wise.) I think the most apt place
for choosing DBMS_XMLQuery is when you need bind variables in your query(the
resultset of which has to converted to XML.)

. Well, a couple of things here. You can use DBMS_XMLGEN with bind variables. . This is done by creating your context from a PL/SQL REF CURSOR. The REF
. CURSOR can be opened w/ bind variables...
.
. open c for 'select * from emp where ename = :x' using p_name;
.
. OR you can use cursor_sharing=force which causes Oracle to try to make
. everything bind variables in the SQL you submit. This comes with it's own
. sets of pros and cons, so don't just enable it arbitrarily, but it's an option
. and will give you faster execution w/ DBMS_XMLGEN w/o having to incur the
. penalty of using the JVM stack.

3. From your AskTom RSS examples it makes very clear that SQLX can just acheive
anything you want and from your explanation it is understood that we need to go
for it whenever possible since these can used in SQL queries. But my doubts here
is if you have a situation where in you need to use a nested table structure?
i.e. a requirement where in you have a set of items for tag something like DEPT
tag holding EMPLOYEE LIST tag which include some employees , their names, their
hiredates as separate tags.

. Using nested tables and varrays in your SQLX queries would definitely
. be pretty complex, whereas "select * from table {with a nested table or
. varray}" translates quite nicely w/ DBMS_XMLGEN and/or DBMS_XMLQUERY. That
. doesn't mean it's impossible though. I would strive to get your queries and
. desired output via SQLX if at all possible before moving on to the
. "potentially easier" solution. IMO, easier doesn't necessarily mean better
. or faster (and usually means quite the opposite).

In this scenario shouldn't we use objects, nested tables and SYS_XMLGEN function
to generate the XML or else still SQLX functions can acheive it?

I am scared to use nested tables or varrays (becuase there might be some
performance impact.) , Hide this statement from Tom ;)

. Heheh, Tom reads pretty much EVERYTHING that comes across this site.
. You don't think I'd actually HIDE it from him do you? ;-).

Very useful.

Kashif, August 14, 2003 - 4:36 pm UTC


Ampersand in XML

Sasa, September 03, 2003 - 3:18 am UTC

Hi Tom,

1.I have a problem during generate XML with special chars.
See following query:

select xmltype.getclobval(
xmlelement(
"TEST",
xmlforest(
id, testamp
)
)
) as xmlpayload
FROM (select 1 as id, '1ST&2ND' as testamp from dual) ;

It returns:
XMLPAYLOAD
----------------------------------------------------
<TEST><ID>1</ID><TESTAMP>1ST&amp;2ND</TESTAMP></TEST>

You could see that "amp;" is added to the end of "&" sign.

Could you reccomend any workaround for that as using escape sequence didn' help (or I did something wrong).

Maybe the next question is for Sean Dillon.
2. If we have a field which name contains "#" when we create XML we got next:

select xmltype.getclobval(
xmlelement(
"TEST",
xmlforest(
TEST#SIGN
)
)
) as xmlpayload
FROM (select 1 as TEST#SIGN from dual)

It returns:
XMLPAYLOAD
----------------------------------------------------
<TEST><TEST_x0023_SIGN>1</TEST_x0023_SIGN></TEST>

You see that instead of "TEST#SIGN ", got "TEST_x0023_SIGN".

Any workaround for that or just to change naming convetion.

Thanks,


Tom Kyte
September 03, 2003 - 6:59 am UTC

&amp; is the only correct way for it to be in XML. & is a entity flag.


seems you might need to change the naming convention to conform to XML itself. # has special meaning in XML as well.

How about CDATA?

Du&amp;#353;an, September 03, 2003 - 10:23 am UTC

OK, & is a special charachter, but surely CDATA is meant to go around that (and allow you to store binary and other XML syntax-incorrect stuff).

Now, this is a correct piece of XML:

<TEST>
<ID>1</ID>
<TESTAMP>![CDATA[1ST&2ND]]</TESTAMP>
</TEST>

Open it up in any XML editor, and voila, "&" is a literal part of CDATA.

But:

1. How do I generate XML with CDATA sections? Tried this:

SELECT
xmlelement(
"TEST",
xmlforest(
id, testamp
)
).getstringval()
AS xmlpayload
FROM (SELECT 1 AS id, '<![CDATA[1ST&2ND]]>' AS testamp FROM dual) ;

but got this output:

<TEST><ID>1</ID><TESTAMP>&lt;![CDATA[1ST&amp;2ND]]&gt;</TESTAMP></TEST>

Obviously, I have to "tell" xmltype that this thing is CDATA.

After reading through the documentation, I got to the point where I found about makeCDataSection and createCDataSection (DBMS_XMLDOM), but as usual, there is no sign of living examples. Am I on the right trail?

2. If you can demonstrate how to do 1, please demonstrate how to do the opposite, to get "columns" from CDATA XML elements, with no <![CDATA[...]]> around my string (I am hopeful that there is another way except trimming out the string using string functions, which is obvious).

Many thanks

Is Sean Dillon around?

Du&amp;#353;an, October 02, 2003 - 9:59 am UTC

Tom,
I still haven't found a way to deal with CDATA :(
I believe Sean Dillon was away at the time I submitted the last posting in this thread. Could you ask Sean to provide some advice with CDATA and the APIs I mentioned?
Many thanks

ORA-03115

Yogesh, October 15, 2003 - 4:25 pm UTC

Hi,

I'm running on 8.1.7, but when I execute following query

select xmlgen.getxml('select Empno, Ename, d.deptno from emp e, dept d where e.deptno=d.deptno') from dual

I'm getting following error
ORA-03115 unsupported network datatype or representation

its explaination in oracle docs says "Upgrade the older version of Oracle and try again."....

If I can describe the package, why it is not allowing me to use it ?

Tom Kyte
October 16, 2003 - 9:55 am UTC

the sqlplus client is what version?

I need also solution for CDATA

msc, October 16, 2003 - 6:19 pm UTC

Hi,

I have same question the Dušan has.

How to create CDATA sections with xml* functions?

I have been digging around quite a lot but no luck... I hope there is a solution.


Tom Kyte
October 30, 2003 - 12:48 pm UTC

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

Hiya MSC,

In order to create CDATA sections in your XML, you're going to have to use the DBMS_XMLDOM package or corresponding Java classes. There is a XDB_XMLDOM type called DOMCharacterData, DBMS_XMLDOM constant called CDATA_SECTION_NODE, and procs/funcs in the DBMS_XMLDOM package:

. makeCDataSection()
. makeCharacterData()

These generally take a DBMS_XMLDOM.DOMNode and cast it to a CDATA section. Hope that helps!

Extract and Extractvalue

Sasa, October 24, 2003 - 7:27 am UTC

Hi,

I have no question this time, maybe only "answer" to my question posted in this thread and to all interested in this matter:

I asked how to avoid "&" to show in XML as "&amp;'".
Tom answered me that is legal as "&" is special char so it must be treated separately.
I lived with that as used some string function after extracting from XML.

Example:
SELECT
xmlelement(
"TEST",
xmlforest(
id, testamp
)
).getstringval()
AS xmlpayload
FROM (SELECT 1 AS id, '1ST&2ND' AS testamp FROM dual) ;

got:
<TEST><ID>1</ID><TESTAMP>1ST&amp;2ND</TESTAMP></TEST>

I tried to extract xml with:

SELECT
extract(value(d),'/TEST/TESTAMP/text()').getstringval() as TESTAMP
FROM TABLE(XMLSequence(xmltype('<TEST><ID>1</ID><TESTAMP>1ST&amp;2ND</TESTAMP></TEST>').extract('/TEST'))) d;

It worked but got this output:

1ST&amp;2ND

Of course this was not same I started with:
1ST&2ND

But digging through the Oracle doc I found that exists also extractvalue function, states in doc:
" extractValue() permits you to extract the desired value more easily than when using the equivalent extract function. It is an ease-of-use and shortcut function. So instead of using:

extract(x,'path/text()').get(string|num)val()
you can replace extract().getStringVal() or extract().getnumberval() with extractValue() as follows:

extractValue(x, 'path/text()')

With extractValue() you can leave off the text(), but ONLY if the node pointed to by the 'path' part has only one child and that child is a text node. Otherwise, an error is thrown.

extractValue() syntax is the same as extract()."

Applying extractvalue function to previous:

SELECT
extractvalue(value(d),'/TEST/TESTAMP/text()') as TESTAMP
FROM TABLE(XMLSequence(xmltype('<TEST><ID>1</ID><TESTAMP>1ST&amp;2ND</TESTAMP></TEST>').extract('/TEST'))) d;

got correct:

1ST&2ND

Also, it returns correctly all other "problematic" character: > , < , ", ' .

I think this is probably intended to Sean Dillon who should explain this different behaviour.

Thanks


Thanks, Sean!

msc, October 30, 2003 - 5:29 pm UTC


"Unnesting" XML

vll, January 30, 2004 - 12:43 pm UTC

Hi, Tom!
Oracle 9i is a miracle, and you are even more than a miracle! Absolutely honestly :-)))

Question:
Let's say we have XML like this:
<SET>
<ORDER id="1">
<ITEM id="A"/>
<ITEM id="B"/>
</ORDER>
<ORDER id="2">
<ITEM id="C"/>
<ITEM id="D"/>
</ORDER>
</SET>

Is there any way to query such a document and get results like this:

ORDER ITEM
1 A
1 B
2 C
2 D

Thank you very much for all your help!!!

RSS feed request

Ferenc, April 14, 2004 - 4:23 am UTC

Hi Tom!

I'd like to ask for a new RSS feed.

I'm useing www.bloglines.com to read your site. Your feeds give me just the title of the topic and two additional lines. Sometimes this is not enough information to decide whether I'm interested in that topic or not.

I think it would be cool to have another feed which gives me the title, the latest review and your followup.

Sorry for posting to this forum, but I haven't found a subject dedicated to RSS.

Thanks!

Ferenc

Tom Kyte
April 29, 2004 - 1:53 pm UTC

I asked Sean Dillon, my RSS guy, to take a look at this and here's what he had to say:

Hi Ferenc, I'll try to get this published as soon as possible. I have a couple of requests for RSS work, so I'll attach your request to those and see what I can get done for you. Thanks for the idea.

_smd_

Special Language Characters in XML

Jens, May 17, 2004 - 7:30 am UTC

I'm learning Oracle-XMLDB features. It's great! But now I have a problem:

I create a xml-output via SYS_XMLTYPE- and XML_ELEMENT-functions. In the column values I select, special XML-Characters like the ampersand will be automatically replaced through their equivalent escape code (&amp) - But our special german characters (ä, ü, ö, ß) won't be replaced. Is there a function to replace them or do I have to write my own function that generates the hex-codes?

Bye,

Jens


Back to CDATA again!

Martin, November 17, 2004 - 4:32 am UTC

Hello,

we have a large number of SQL/XML queries which work amazingly well, however, one of the queries now has to allow the use of CDATA sections as the value of an XMLELEMENT. Now, I know you've mentioned the use of DBMS_XMLDOM to do this, but I was wondering if you know of a mechanism within the existing SQL/XML functionality or standard ( existing or proposed ) for allowing us to specify easily if a value should be contained in a CDATA section?

Thanks in advance

Tom Kyte
November 17, 2004 - 9:39 am UTC

Hi, Sean here.

I know there's been discussion about including CDATA functionality in upcoming releases, but nothing we can talk about. Keep your eyes open for upcoming releases, you may like what you see :) For now, though, you're stuck w/ DOM or string manipulation...

_smd_

code using multiple tables

Sap, February 15, 2005 - 6:13 am UTC

Hi Tom,
How can i generate code for query that uses multiple tables like the following using SQLX functions.

SELECT gsb.name c_company
, fsp.inventory_organization_id c_organization_id
, gsb.currency_code GL_CURRENCY
, gsb.chart_of_accounts_id STRUCTURE_ACC
, mdv.structure_id STRUCTURE_CAT
, fc.precision C_CURRENCY_PRECISION
, mdv.category_set_id c_category_set_id
, flo1.meaning c_yes
, flo2.meaning c_no
FROM gl_sets_of_books gsb
, financials_system_parameters fsp
, mtl_default_sets_view mdv
, fnd_lookups flo1
, fnd_lookups flo2
, fnd_currencies fc
WHERE gsb.set_of_books_id = fsp.set_of_books_id
AND mdv.functional_area_id = 2
AND flo1.lookup_type = 'YES_NO'
AND flo1.lookup_code = 'Y'
AND flo2.lookup_type = 'YES_NO'
AND flo2.lookup_code = 'N'
AND fc.currency_code = gsb.currency_code


Tom Kyte
February 15, 2005 - 3:28 pm UTC

if we

create view v as <your_select>

would you be able to do it?


a query is just a "table"

select *
from (YOUR_QUERY_AS_ABOVE)


works as if it were a single table. soooo, SQL doesn't care if you "from a_table" or "from many, tables, in, a, list"



Can i do subtotal & grand total using SQLX functions

sap, February 17, 2005 - 1:12 am UTC

Hi Tom,
I am very much thankful to you, your answers have been to the point and very much helpful to me.
Tom, i have a new issue, can you please tell how to perform concepts like subtotal & grand toal, using formulas while generating XML output using SQLX functions. For example, consider Department wise employee listing. In that if i want subtotal at department level and a grand total at the end. And i want to write a formula to get total salary (i.e. salary + commission).How can i go about this? Can you please explain with sample code?

Tom Kyte
February 17, 2005 - 8:27 am UTC

SQL does that.


scott@ORA9IR2> select decode( grouping(deptno)|| grouping(empno),
2 '00', null,
3 '01', 'dept total',
4 '11', 'grand total' ) label,
5 deptno,
6 empno,
7 sum(sal),
8 sum(comm),
9 sum(sal+nvl(comm,0)) tot_comp
10 from emp
11 group by rollup( deptno, empno )
12 /

LABEL DEPTNO EMPNO SUM(SAL) SUM(COMM) TOT_COMP
----------- ---------- ---------- ---------- ---------- ----------
10 7782 2450 2450
10 7839 5000 5000
10 7934 1300 1300
dept total 10 8750 8750
20 7369 800 800
20 7566 2975 2975
20 7788 3000 3000
20 7876 1100 1100
20 7902 3000 3000
dept total 20 10875 10875
30 7900 950 950
30 7499 1600 300 1900
30 7521 1250 500 1750
30 7654 1250 1400 2650
30 7698 2850 2850
30 7844 1500 0 1500
dept total 30 9400 2200 11600
grand total 29025 2200 31225

18 rows selected.



XML Publisher

Ashly, February 18, 2005 - 2:56 am UTC

Hi Tom,

I have a query regarding XML publisher ,
How can i capture report parameters (runtime parameters), of an existing RDF having output type XML into the XML code that is being generated by the concurrent program that runs the report, which i will be making use of in my XML Publisher.

thanks in advace,
Ashly




Tom Kyte
February 18, 2005 - 8:03 am UTC

i've never used reports, try otn.oracle.com => discussion forums.

There is a group for developer products there.

Nair, February 18, 2005 - 3:05 am UTC

Hello Tom,
I have been following your answers regarding SQLX.Tom, i have a query, can i make it any better.

select XMLElement("DEPT_EMP_TOP",
XMLElement("Grand", (select sum(sal) from emp)),
(select XMLElement("DEPT_EMP",
XMLAgg(
XMLElement("DEPT",
XMLElement("Deptno", D.DEPTNO) ,
XMLElement("Name", D.DNAME) ,
XMLElement("Sumsal", Sum(E.SAL)) ,
XMLAgg(XMLElement("emp",
XMLElement("Number", E.EMPNO),
XMLElement("Name", E.ENAME),
XMLElement("Job", E.JOB),
XMLElement("Salary", E.SAL)
)))))
from DEPT D, EMP E
where E.DEPTNO = D.DEPTNO
group by d.deptno, d.dname))
from dual


Tom Kyte
February 18, 2005 - 8:04 am UTC

what is wrong with it?

how to format ?

sap, March 09, 2005 - 2:06 am UTC

Hi Tom,
        I have a query,when i am trying to run SQLX functions the output is not formateed, can you tell me why?
       Here is the scenario.

SQL> select XMLElement("DEPT_EMP", 
  2          XMLAgg(
  3            XMLElement("DEPT",
  4                        XMLElement("Deptno", d.ceptno) ,
  5            XMLElement("Name", D.DNAME) ,
  6            XMLElement("Sumsal", Sum(E.SAL)) ,
  7            XMLAgg(XMLElement("emp", 
  8                    XMLElement("Number", E.EMPNO),
  9                  XMLElement("Name", E.ENAME),
 10            XMLElement("Job", E.JOB),
 11                  XMLElement("Salary", E.SAL)
 12            )))))
 13  from DEPT D, EMP E
 14  where  E.DEPTNO = d.ceptno
 15  group by d.ceptno, d.dname
 16  /

XMLELEMENT("DEPT_EMP",XMLAGG(XMLELEMENT("DEPT",XMLELEMENT("DEPTNO",D.CEPTNO),XML
--------------------------------------------------------------------------------
<DEPT_EMP><DEPT><Deptno>10</Deptno><Name>Account</Name><Sumsal>15395.5</Sumsal><
emp><Number>5555</Number><Name>TEST USER</Name><Job>SW</Job><Salary>1001</Salary
></emp><emp><Number>7782</Number><Name>CLARK</Name><Job>MANAGER</Job><Salary>296
4.5</Salary></emp><emp><Number>7839</Number><Name>KING</Name><Job>PRESIDENT</Job
><Salary>10000</Salary></emp><emp><Number>7934</Number><Name>MILLER</Name><Job>C
LERK</Job><Salary>1430</Salary></emp></DEPT><DEPT><Deptno>20</Deptno><Name>Compu
ters</Name><Sumsal>15002.4</Sumsal><emp><Number>7369</Number><Name>tushar</Name>
<Job>CLERK</Job><Salary>1064.8</Salary></emp><emp><Number>7566</Number><Name>JON
ES</Name><Job>MANAGER</Job><Salary>3599.75</Salary></emp><emp><Number>7788</Numb
er><Name>SCOTT</Name><Job>ANALYST</Job><Salary>3000</Salary></emp><emp><Number>7
844</Number><Name>TURNER</Name><Job>SALESMAN</Job><Salary>1863.4</Salary></emp><

XMLELEMENT("DEPT_EMP",XMLAGG(XMLELEMENT("DEPT",XMLELEMENT("DEPTNO",D.CEPTNO),XML
--------------------------------------------------------------------------------
emp><Number>7900</Number><Name>JAMES</Name><Job>CLERK</Job><Salary>1264.45</Sala
ry></emp><emp><Number>7902</Number><Name>MILLER</Name><Job>ANALYST</Job><Salary>
3000</Salary></emp><emp><Number>7876</Number><Name>ADAMS</Name><Job>CLERK</Job><
Salary>1210</Salary></emp></DEPT><DEPT><Deptno>30</Deptno><Name>HRD</Name><Sumsa
l>3509</Sumsal><emp><Number>7499</Number><Name>ALLEN</Name><Job>SALESMAN</Job><S
alary>1984.4</Salary></emp><emp><Number>7521</Number><Name>WARD</Name><Job>SALES
MAN</Job><Salary>1560.9</Salary></emp><emp><Number>7654</Number><Name>MARTIN</Na
me><Job>SALESMAN</Job><Salary>-12.1</Salary></emp><emp><Number>7698</Number><Nam
e>BLAKE</Name><Job>MANAGER</Job><Salary>-24.2</Salary></emp></DEPT></DEPT_EMP>


    As you can see here the output is not fomatted? How can i achieve it? 

Tom Kyte
March 09, 2005 - 7:41 am UTC

output certainly looks formatted for computer use?

XML Generation for XML Publisher

ashly, March 14, 2005 - 6:55 am UTC

Hi Tom,
I am currently working on Oracle Applications XML Publisher module.Can you please tell me which is the best out of the 10 ways for XML Generation for fresh generation of XML Publisher reports? SQLX or DBMS_XMLGen?
My next question may be for Sean. When i am trying to register SQL containing SQLX or DBMS_XMLGen as a concurrent program in Applications which is throwing an error. It will be greatly helpful if you can help me out with this issue.
Thanks in advance.


Tom Kyte
March 14, 2005 - 8:10 am UTC

if there was a best way, there would be one way.

Sorry -- but neither Sean or I are applications "savvy"

How to group XML output by Object Type?

max, March 26, 2005 - 10:33 am UTC

the following query generates XML from the content of User_Objects:

select '<?xml version="1.0" encoding="utf-8"?>' ||
xmlelement(
"UserObjects",
xmlagg(
xmlelement(
"MyRow",
xmlattributes(
Object_ID "ObjID"
),
xmlforest(
Object_Type "ObjType",
Object_Name "ObjName",
Created "CreateDt"
)
)
)
).getCLOBVal() MyCLOB
from User_Objects ;

could you please help me to modify this statement to group all objects of same type within an XML element named according to that object type (e.g. all functions within <function>...</function>)?

Tom Kyte
April 05, 2005 - 1:24 pm UTC

Hi Max, Sean Dillon here. I'm Tom's "XML Guy"...

With the caveat that there are plenty of ways to accomplish what you're trying to do, here's one such way:

sys@JBLADE> select xmlelement("UserObjects",
2 xmlelement( "Functions",
3 ( select xmlagg(
4 xmlelement("Function",
5 xmlattributes(object_id as "ObjectID"),
6 xmlforest(
7 object_name as "ObjectName",
8 created as "Created",
9 status as "Status"
10 )
11 )
12 )
13 from all_objects
14 where object_type = 'FUNCTION'
15 and rownum < 6
16 )
17 ),
18 xmlelement( "Procedures",
19 ( select xmlagg(
20 xmlelement("Procedure",
21 xmlattributes(object_id as "ObjectID"),
22 xmlforest(
23 object_name as "ObjectName",
24 created as "Created",
25 status as "Status"
26 )
27 )
28 )
29 from all_objects
30 where object_type = 'PROCEDURE'
31 and rownum < 6
32 )
33 )
34 ) as result
35 from dual
36 /

RESULT
-----------------------------------------------------------------------------------------------------------------------------------
<UserObjects>
<Functions>
<Function ObjectID="4039">
<ObjectName>GETTVOID</ObjectName>
<Created>04-MAR-05</Created>
<Status>VALID</Status></Function>
<Function ObjectID="4168">
<ObjectName>TIMESTAMP_TO_SCN</ObjectName>
<Created>04-MAR-05</Created>
<Status>VALID</Status>
</Function>
<Function ObjectID="4169">
<ObjectName>SCN_TO_TIMESTAMP</ObjectName>
<Created>04-MAR-05</Created>
<Status>VALID</Status>
</Function>
<Function ObjectID="4194">
<ObjectName>SYS$RAWTOANY</ObjectName>
<Created>04-MAR-05</Created>
<Status>VALID</Status>
</Function>
<Function ObjectID="4813">
<ObjectName>SCHEDULER$_JOBPIPE</ObjectName>
<Created>04-MAR-05</Created>
<Status>VALID</Status>
</Function>
</Functions>
<Procedures>
<Procedure ObjectID="3787">
<ObjectName>PSTUBT</ObjectName>
<Created>04-MAR-05</Created>
<Status>VALID</Status>
</Procedure>
<Procedure ObjectID="3788">
<ObjectName>PSTUB</ObjectName>
<Created>04-MAR-05</Created>
<Status>VALID</Status>
</Procedure>
<Procedure ObjectID="3791">
<ObjectName>SUBPTXT2</ObjectName>
<Created>04-MAR-05</Created>
<Status>VALID</Status>
</Procedure>
<Procedure ObjectID="3792">
<ObjectName>SUBPTXT</ObjectName>
<Created>04-MAR-05</Created>
<Status>VALID</Status>
</Procedure>
<Procedure ObjectID="4072">
<ObjectName>ODCIINDEXINFOFLAGSDUMP</ObjectName>
<Created>04-MAR-05</Created>
<Status>VALID</Status>
</Procedure>
</Procedures>
</UserObjects>



How to get BLOB in XML ?

Randy, March 28, 2005 - 11:39 am UTC

Tom/Sean,

I am trying to generate base64 encoded xml out of blob data. We have a table called test which has blob column called content. I am using following sql
select dbms_xmlgen.getxml('select utl_raw.cast_to_varchar2(utl_encode.base64_encode(content)) content from test where object_id = &1') from dual;

It works fine for smaller object but for bigger object (> 10k) I am getting following error.

ERROR:
ORA-19202: Error occurred in XML processing
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_ENCODE", line 243
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1

I tried using the same sql in a java wrapper, I am getting same error. What I am doing wrong ? Can you let me know an alternative ?

Thanks in advance.

Tom Kyte
April 05, 2005 - 10:57 am UTC

Hi Randy, Sean Dillon here.

I don't think you're running into an XML issue at all. In trying to reproduce this, I can't get UTL_ENCODE to take anything longer than a 2000 byte RAW without hitting your ORA-6502. It might be a base64 encoding issue. Can you base 64 encode the data w/o the XMLGEN?

If you're willing to look down the Java path, I know there's alternative ways to base64 encode w/ Java, then you can use the Oracle XML Developer's Kits to wrap this into an XML document.

Hope that helps!

_smd_

How to get BLOB in XML ?

Randy, March 28, 2005 - 11:41 am UTC

Tom/Sean,

Forgot to mention.. I am using 10g.



Changing ROWSETTAG in XML

Kan, April 01, 2005 - 12:35 pm UTC

Tom,
I am running the following pl/sql to generate XML from a couple of tables (i'm using EMP table as example)

Table EMP:
-------------
CREATE TABLE EMP
(
EMPNO NUMBER(5),
ENAME VARCHAR2(20 BYTE),
SAL INTEGER,
DEPTNO INTEGER
)

INSERT INTO EMP ( EMPNO, ENAME, SAL, DEPTNO ) VALUES (
1234, 'XXXX', 2500, 10);
INSERT INTO EMP ( EMPNO, ENAME, SAL, DEPTNO ) VALUES (
2345, 'John', 4567, 10);
INSERT INTO EMP ( EMPNO, ENAME, SAL, DEPTNO ) VALUES (
5555, 'Mary', 4537, 60);
INSERT INTO EMP ( EMPNO, ENAME, SAL, DEPTNO ) VALUES (
2222, 'Kate', 2345, 60);
COMMIT;

Table EMP2:
-----------
CREATE TABLE EMP2
(
EMPNO NUMBER(5),
ENAME VARCHAR2(20 BYTE),
SAL INTEGER,
DEPTNO INTEGER
)

INSERT INTO EMP2 ( EMPNO, ENAME, SAL, DEPTNO ) VALUES (
1345, 'YYYY', 2550, 10);
INSERT INTO EMP2 ( EMPNO, ENAME, SAL, DEPTNO ) VALUES (
2356, 'JohnXX', 4500, 10);
INSERT INTO EMP2 ( EMPNO, ENAME, SAL, DEPTNO ) VALUES (
5554, 'Mary23', 4000, 60);
INSERT INTO EMP2 ( EMPNO, ENAME, SAL, DEPTNO ) VALUES (
2211, 'Kate45', 2000, 60);
COMMIT;

--Pl/sql code
declare

sql_qry varchar2(2000):='';
p_cur sys_refcursor;
get_inp varchar2(2000):='';

begin

sql_qry:='select * from EMP1 union all select * from EMP2';

open p_cur for select dbms_xmlgen.getxml(sql_qry) from dual;
fetch p_cur into get_inp;
dbms_output.put_line(get_inp);
end;
----------------------------------------------------
OUTPUT
=========

<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>1234</EMPNO>
<ENAME>Matt</ENAME>
<SAL>2500</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>2345</EMPNO>
<ENAME>John</ENAME>
<SAL>4567</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>5555</EMPNO>
<ENAME>johnson</ENAME>
<SAL>4537</SAL>
<DEPTNO>60</DEPTNO>
</ROW>
<ROW>
<EMPNO>2222</EMPNO>
<ENAME>Margaret</ENAME>
<SAL>2345</SAL>
<DEPTNO>60</DEPTNO>
</ROW>
<ROW>
<EMPNO>1345</EMPNO>
<ENAME>YYYY</ENAME>
<SAL>2550</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>2356</EMPNO>
<ENAME>Johnxxx</ENAME>
<SAL>4500</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW>
<EMPNO>5554</EMPNO>
<ENAME>Mary23</ENAME>
<SAL>4000</SAL>
<DEPTNO>60</DEPTNO>
</ROW>
<ROW>
<EMPNO>2211</EMPNO>
<ENAME>Kate45</ENAME>
<SAL>2000</SAL>
<DEPTNO>60</DEPTNO>
</ROW>
</ROWSET>



How do I change my ROWSETTAG to a default Name like 'Table structure' and and ROWTAG to corresponding Table Name?

I have read about DBMS_XMLGEN.SETROWSETTAG() and DBMS_XMLGEN.SETROWTAG() , but could not manage it for this particular one.

Thanks for your suggestions



Changing ROWSETTAG in XML

Kan, April 04, 2005 - 10:19 am UTC

Tom,
Can you please provide answer to this? I am really stuck with this one.

Tom Kyte
April 05, 2005 - 10:37 am UTC

Hey Kan, Sean Dillon here.

There are a couple of answers to what you're trying to do. First, let me show you how to use those setrowsettag() and setrowtag() procs... In order to use those, you have to declare a context that you can pass to the procedures. This lets the package track your preferences for a particular context. Otherwise, the DBMS_XMLGEN package's GETXML function would have to be massively overloaded to accomodate all the incarnations of the params that could/would be passed in.

You calling GETXML and passing in a SQL statement works fine, but it's a quick fix and generally not very customizable. Here's how I augmented your PL/SQL to customize our XML output:

declare
sql_qry varchar2(2000):='';
p_cur sys_refcursor;
get_inp varchar2(8000):='';

l_ctx dbms_xmlgen.ctxhandle;
l_idx pls_integer := 1;

begin
sql_qry:='select * from EMP union all select * from EMP2';

-- Create a context
l_ctx := dbms_xmlgen.newcontext(sql_qry);

-- Customize rowsettag, rowtag
dbms_xmlgen.setrowsettag(l_ctx,'TableStructure');
dbms_xmlgen.setrowtag(l_ctx,'Emps');

-- This isn't necessary, dbms_xmlgen can just retrieve the clob
-- open p_cur for select dbms_xmlgen.getxml(sql_qry) from dual;
-- fetch p_cur into get_inp;

get_inp := dbms_xmlgen.getxml(l_ctx);

-- I do this to avoid potential linesize issues w/ dbms_output
for i in 1 .. ceil(length(get_inp)/255) loop
dbms_output.put_line(substr(get_inp,l_idx,255));
l_idx := l_idx + 255;
end loop;
end;
/

Which yields...

<?xml version="1.0"?>
<TableStructure>
<Emps>
<EMPNO>1234</EMPNO>
<ENAME>XXXX</ENAME>
<SAL>2500</SAL>
<DEPTNO>10</DEPTNO>
</Emps>
<Emps>
<EMPNO>2345</EMPNO>
<ENAME>John</ENAME>
<SAL>4567</SAL>
<DEPTNO>10</DEPTNO>
</Emps>
...
</TableStructure>

As you can see, even though we can customize some of these tags, this is still somewhat limiting. In your case, you wanted the row tag to have the table name, but w/ DBMS_XMLGEN you can't specify different rowtags by row, you can only set one rowtag value for the entire document. In your case, you'd want to write some SQLXML. Before I write out a huge example of a SQLXML query, search AskTom for SQLX or SQLXML and you'll find 50 examples.

Hope that helps!

_smd_

Changing ROWSETTAG in XML

Kan, April 05, 2005 - 10:45 am UTC

Thanks Sean , I'll search this site for more SQLXML.
Appreciate all your help.



Please, don't move, Sean ....

max, April 05, 2005 - 11:02 am UTC

could you please take a look at the sample below:

drop table PersonCLOB ;

create table PersonCLOB as
select xmltype(
'<?xml version="1.0" encoding="UTF-8"?>
<PersonList>
<Person>
<Name>Max</Name>
<Birthday>01.01.1990</Birthday>
<Balance>123.45</Balance>
<Address>
<City>Max Town</City>
<Street>Max Street</Street>
<Number>10</Number>
</Address>
</Person>
<Person>
<Name>Ruth</Name>
<Birthday>01.01.1990</Birthday>
<Balance>-1000</Balance>
<Address>
<City>Ruth Town</City>
<Street>Ruth Street</Street>
<Number>42</Number>
</Address>
</Person>
</PersonList>' ).getCLOBVal() Content from dual ;

drop table PersonXML ;

create table PersonXML of xmltype ;
insert into PersonXML select xmltype( Content ) from PersonCLOB ;

select extractvalue( value( p ), '/Person/Name' ) Name,
extractvalue( value( p ), '/Person/Birthday' ) Birthday,
extractvalue( value( p ), '/Person/Balance' ) Balance,
extractvalue( value( p ), '/Person/Address/City' ) AddressCity,
extractvalue( value( p ), '/Person/Address/Street' ) AddressStreet,
extractvalue( value( p ), '/Person/Address/Number' ) AddressNumber
from PersonXML x,
table( xmlsequence( extract( value( x ), '/PersonList/Person' ) ) ) p ;

is it possible to replace the table PersonXML by a table function at all (i didn't get it to work)?

Tom Kyte
April 05, 2005 - 12:20 pm UTC

so what did you try?

well, i 've just tried it once more ...

max, April 06, 2005 - 2:33 pm UTC

... and finally my pipelined table function returned a table of xml docs ... ;o)

but there 's another question ...

max, April 09, 2005 - 7:20 pm UTC

could you *please* show how to replace the xmltype table in the very last SELECT statement below by the SELECT used to INSERT test data into that table?

drop table MyXMLTab ;
create table MyXMLTab of xmltype ;

-- generating and inserting test data
insert into MyXMLTab
select XMLType.createXML(
'<PersonList>
<Person>
<Name>Max</Name>
<Birthday>01.01.1990</Birthday>
<Balance>123.45</Balance>
<Address>
<City>Max Town</City>
<Street>Max Street</Street>
<Number>10</Number>
</Address>
</Person>
<Person>
<Name>Ruth</Name>
<Birthday>01.01.1990</Birthday>
<Balance>-1000</Balance>
<Address>
<City>Ruth Town</City>
<Street>Ruth Street</Street>
<Number>42</Number>
</Address>
</Person>
</PersonList>' ) MyXML from dual ;

-- querying XML content
select extractvalue( value( p ), '/Person/Name' ) Name,
extractvalue( value( p ), '/Person/Birthday' ) Birthday,
extractvalue( value( p ), '/Person/Balance' ) Balance,
extractvalue( value( p ), '/Person/Address/City' ) AddressCity,
extractvalue( value( p ), '/Person/Address/Street' ) AddressStreet,
extractvalue( value( p ), '/Person/Address/Number' ) AddressNumber
from MyXMLTab x, -- how to replace the xmltype table by the above select?
table( xmlsequence( extract( value( x ), '/PersonList/Person' ) ) ) p ;

... if this is possible at all ...

Tom Kyte
April 09, 2005 - 7:26 pm UTC

why? I mean, you have something *working*, why?

You are trying to flatten a structure right? that is what we use to do that.

As for "How to group XML output by Object Type?"

max, April 09, 2005 - 7:35 pm UTC

thank you, sean, for your reply.

but tell me, please, can you even think of a way to generate such XML output without the need to have one copy of the embedded query per object type (with just different parameters)? it would be fully sufficient for the requested output to have an object's data stored within an element <object>...</object> (instead of <function>...</function>, <procedure>...</procedure>, ...) since such element would reside within an element named according to the object type the object belongs to ...

As for "but there 's another question ..."

max, April 10, 2005 - 5:43 am UTC

yes, you 're right: the purpose is to flatten the XML. but in the real life use case the XML content to be transformed is stored within a CLOB column. that 's why we 'd like to take that column's content "on the fly" without the need of an extra step to store it into a xmltype column first.

is there no way to achieve that?

Tom Kyte
April 10, 2005 - 10:38 am UTC

so you have a table T with a clob:

ops$tkyte@ORA9IR2> insert into t values (
  2  '<PersonList>
  3      <Person>
  4                <Name>Max</Name>
  5                <Birthday>01.01.1990</Birthday>
  6                <Balance>123.45</Balance>
  7                <Address>
  8                       <City>Max Town</City>
  9                       <Street>Max Street</Street>
 10                       <Number>10</Number>
 11                </Address>
 12      </Person>
 13      <Person>
 14          <Name>Ruth</Name>
 15                <Birthday>01.01.1990</Birthday>
 16                <Balance>-1000</Balance>
 17                <Address>
 18                       <City>Ruth Town</City>
 19                       <Street>Ruth Street</Street>
 20                       <Number>42</Number>
 21                </Address>
 22      </Person>
 23  </PersonList>' );
 
1 row created.

<b>just convert it into an xmltype in the query</b>

 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> with myxmltab
  2  as
  3  (select xmltype.createxml(x) Xml from T where rownum=1)
  4  select extractvalue( value( p ), '/Person/Name'           ) Name,
  5         extractvalue( value( p ), '/Person/Birthday'       ) Birthday,
  6         extractvalue( value( p ), '/Person/Balance'        ) Balance,
  7         extractvalue( value( p ), '/Person/Address/City'   ) AddressCity,
  8         extractvalue( value( p ), '/Person/Address/Street' ) AddressStreet,
  9         extractvalue( value( p ), '/Person/Address/Number' ) AddressNumber
 10  from   myxmltab x,
 11         table( xmlsequence( extract( x.xml, '/PersonList/Person' ) ) ) p
 12  /
 
NAME
------------------------------
BIRTHDAY
-------------------------------------------------------------------------------
BALANCE
-------------------------------------------------------------------------------
ADDRESSCITY
-------------------------------------------------------------------------------
ADDRESSSTREET
-------------------------------------------------------------------------------
ADDRESSNUMBER
-------------------------------------------------------------------------------
Max
01.01.1990
123.45
Max Town
Max Street
10
 
Ruth
01.01.1990
-1000
Ruth Town
Ruth Street
42
 

thank you very, very much ...

max, April 15, 2005 - 11:57 am UTC

that 's exactly what we wanted to do.

Srinivas P

Srinivas Parvath, June 11, 2005 - 4:05 pm UTC

Very Useful.


Works on server, not client

VA, December 14, 2005 - 11:49 am UTC

Oracle 9.2.0.6 on Unix

If I telnet to the Unix server and do this in SQL*Plus

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

SQL> select xmlelement("abc") from dual;

XMLELEMENT("ABC")
--------------------------------------------------------------------------------
<abc></abc>

works fine.

But when I do the same thing from a Windows GUI tool, it gives me a ORA-31011!

Thats mighty strange, why would this be?

Thanks 

getStringVal()

A reader, December 14, 2005 - 12:23 pm UTC

I was missing the getStringVal()

In command-line SQL*Plus,  both work, but for some reason, in the GUI, the getStringVal() is required otherwise it throws that error.

SQL> SELECT xmlelement("abc").getStringVal() from dual;

XMLELEMENT("ABC").GETSTRINGVAL()
--------------------------------------------------------------------------------
<abc></abc>

SQL> SELECT xmlelement("abc") from dual;

XMLELEMENT("ABC")
--------------------------------------------------------------------------------
<abc></abc>
 

ORA-31011: XML parsing failed

S Ray, March 30, 2006 - 7:12 pm UTC

I keep getting ORA-31011 when I try to use xmlelement() (DB version:Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production). Could you throw some light on this?
CREATE TABLE emp (employee_id INT,
fname VARCHAR2(20), lname VARCHAR2(20));

INSERT INTO employees (
   employee_id, fname, lname) 
VALUES ( 1001,'John' ,'Smith' );

INSERT INTO employees (
   employee_id, fname, lname) 
VALUES ( 1002,'Mary' ,'Martin' );

INSERT INTO employees (
   employee_id, fname, lname) 
VALUES ( 60,'Scott' ,'King' );

SQL> SELECT xmlelement("name",e.fname )  FROM employees e;
ERROR:
ORA-31011: XML parsing failed

SQL> SELECT sys_xmlgen(e.fname  ) FROM employees e;
ERROR:
ORA-31011: XML parsing failed

But, SELECT dbms_xmlgen.getxml( 'select e.fname from employees e' ) FROM dual;
gives:
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <FNAME>John</FNAME>
 </ROW>
 <ROW>
  <FNAME>Mary</FNAME>
 </ROW>
 <ROW>
  <FNAME>Scott</FNAME>
 </ROW>
</ROWSET>
 

Convertig BLOB to Hex Values

Wolfgang, September 27, 2006 - 2:23 pm UTC

Hi Tom,
as this thread goes about XML generation Methods, I try asking here.
I'm using XMLELEMENT and XMLATTRIBUTES on 9.2.0.7 to generate XML.
Now I have got the problem, that some tables contain blobs.
When I execute this (simplified) Statement
SELECT XMLELEMENT ("Field",
xmlattributes ('ANGEBOTSDATEN' AS "Name",
angebotsdaten AS "Value"
)
)
FROM tbl1401angebot
WHERE angno = 'aaec31000a620a0200073b72ff652505'

I get:
ORA-00932: inconsistent datatypes: expected - got BLOB
(The column angebotsdaten is a BLOB Field with data between 1KB and
~100KB)

So first I wrote a little PL/SQL Function to convert a BLOB to Hex:

FUNCTION blobToHex(tableName VARCHAR2,pkName VARCHAR2,
pkValue VARCHAR2,blobColName VARCHAR2) RETURN CLOB IS
PRAGMA AUTONOMOUS_TRANSACTION;
buffer RAW(2000);
stmt VARCHAR2(200);
hexVal CLOB;
blobVal BLOB;
offset INTEGER;
len NUMBER;
amount BINARY_INTEGER;
tmpid VARCHAR2(32);
BEGIN
offset:=1;
amount:=2000;

--First get the Blob from the original table
stmt:='SELECT '||blobColName||' FROM '||tableName||
' WHERE '||pkName||'=:1';
EXECUTE IMMEDIATE stmt INTO blobVal USING pkValue;

--Now create a CLOB to hold the Hexdata
SELECT SYS_GUID INTO tmpid FROM DUAL;
INSERT INTO TBLHEX VALUES(tmpid,EMPTY_CLOB());
SELECT HEX INTO hexVal FROM TBLHEX where id=tmpid FOR UPDATE;

DBMS_LOB.OPEN(blobVal,DBMS_LOB.LOB_READONLY);
DBMS_LOB.OPEN(hexVal,DBMS_LOB.LOB_READWRITE);
len:=DBMS_LOB.GETLENGTH(blobVal);

--Convert the BLOB to RAW and write it into the CLOB
LOOP
EXIT WHEN offset >= len;
buffer:=DBMS_LOB.SUBSTR( blobVal,
amount,
offset );
DBMS_LOB.WRITEAPPEND( hexVal,
UTL_RAW.LENGTH(buffer)*2,
buffer);
offset:=offset+amount;
END LOOP;
DBMS_LOB.CLOSE(hexVal);
DBMS_LOB.CLOSE(blobVal);
COMMIT;
RETURN hexVal;
END;
/

And the table to hold the CLOB:
CREATE TABLE TBLHEX
(
ID VARCHAR2(32),
HEX CLOB
);
/

Now it's possible to convert a BLOB to a Hex value stored in a CLOB.
But I still get the ORA-00932.
ORA-00932: inconsistent datatypes: expected - got CLOB

Is there a solution for that? I thought it would be a common problem to store a blob in an XML Document, but i did't found something usefull to do this with PL/SQL.

By the way:
SELECT XMLELEMENT ("Field",
blobtohex ('TBL1401ANGEBOT',
'angno', 'ef86d5550a620a0201178281ca6bf198',
'ANGEBOTSDATEN'
)
)
FROM tbl1401angebot
WHERE angno = 'ef86d5550a620a0201178281ca6bf198'

works, but only if the CLOB is not longer than 4000Bytes. Otherwise I
get
a ORA-21500 internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]


Regards
Wolfgang

ampersand symbol in XML content

sara, October 16, 2006 - 11:53 am UTC

Hi Tom,

I have an XML content which I should turn into plain text. I have a stylesheet created. All the XML contents are being converted excepted for the ones which have '&' symbol. I get the following error for those:

ORA-20100: Error occurred while parsing: Expected name instead of .

I tried converting to &amp which doesnt help. Can you help me.
Thanks

Sara

Tom Kyte
October 16, 2006 - 12:48 pm UTC

it would be

&amp;


not just &amp

Apparao, October 24, 2006 - 7:19 am UTC

Hi Tom,



Currently I am facing a problem while validating the XML with the XSD. XML and XSD are <?xml version="1.0" encoding="utf-8"?>.



I am using the following logic to validate the XML:



l_Validatexml xmltype;



l_Validatexml := Xmltype(CLOB).Createschemabasedxml(XSD path);
l_Validatexml.Schemavalidate();



This is working if the XML contains the ASCII characters.



But if the XML contains some german characters for Ex:

<FIRST_NAME>jiagéadéeesseache</FIRST_NAME>



It is giving the following error:

ORA-31043: Element '' not globally defined in schema ''



Can you please help in this regard.



Regards,

Apparao



XML in UTF8

sam, October 25, 2006 - 5:11 pm UTC

Sean per Tom:

How would you generate an xml file of the EMP table
in UTF8 format (from oracle database in WE8ISO).

Any examples?

One group mandates that we provide XML files in UTF8 format for data exchange and we are wondering whether it makes sense (most likey NOT) to change the character set of the database from WE8ISO to UTF8 to accomplish that.






Table representation out of a xml

Espen Rydningen, November 22, 2006 - 5:50 am UTC

Hi Tom,

first of all, thank you for a great site. This site has helped me out of a lot of troublesome situations, and I hope you can help me out this time.

I've got an xml structure that looks like :

<VoucherSummary>
<Details>
<Name>IP Telephony</Name>
<VatPercent>0.25</VatPercent>
<VatBaseAmount>300.9900</VatBaseAmount>
<GrossAmount>376.237500</GrossAmount>
<Details>
<Name>Traffic</Name>
<VatPercent>0.25</VatPercent>
<VatBaseAmount>102.9900</VatBaseAmount>
<GrossAmount>128.737500</GrossAmount>
</Details>
<Details>
<Name>Producs</Name>
<VatPercent>0.25</VatPercent>
<VatBaseAmount>198.0000</VatBaseAmount>
<GrossAmount>247.500000</GrossAmount>
</Details>
</Details>
<Details>
<Name>Other</Name>
<VatPercent>0.0</VatPercent>
<VatBaseAmount>300</VatBaseAmount>
<GrossAmount>300</GrossAmount>
</Details>
</VoucherSummary>

And I want to extract this and represent this like

NAME VAT BASE GROSS LEVEL
IP Telephony 0.25 300.9900 376.237500 1
Traffic 0.25 102.9900 128.737500 2
Producs 0.25 198.0000 247.500000 2
Other 0.0 300 300 1

I've come this far:

select
extract(value(tab),'/Details/Name/text()').getStringVal() NAME,
extract(value(tab),'/Details/VatPercent/text()').getStringVal() VAT,
extract(value(tab),'/Details/VatBaseAmount/text()').getStringVal() BASE,
extract(value(tab),'/Details/GrossAmount/text()').getStringVal() GROSS
from
table (
XMLSequence(extract (
XMLType('<VoucherSummary>
<Details>
<Name>IP Telephony</Name>
<VatPercent>0.25</VatPercent>
<VatBaseAmount>300.9900</VatBaseAmount>
<GrossAmount>376.237500</GrossAmount>
<Details>
<Name>Traffic</Name>
<VatPercent>0.25</VatPercent>
<VatBaseAmount>102.9900</VatBaseAmount>
<GrossAmount>128.737500</GrossAmount>
</Details>
<Details>
<Name>Producs</Name>
<VatPercent>0.25</VatPercent>
<VatBaseAmount>198.0000</VatBaseAmount>
<GrossAmount>247.500000</GrossAmount>
</Details>
</Details>
<Details>
<Name>Other</Name>
<VatPercent>0.0</VatPercent>
<VatBaseAmount>300</VatBaseAmount>
<GrossAmount>300</GrossAmount>
</Details>
</VoucherSummary>'),
'/VoucherSummary//Details')
)
) tab;

This gives me the result without the level, so my problem is; If it is possible to retreive the level where the details exists, and how?

Sincerly yours

- Espen


DBMS_XMLGEN: Dynamic SQL

raajesh, December 01, 2006 - 3:25 am UTC

Hi Tom,

Iam trying to create an XML using DBMS_XMLGEN. My PL/SQL code looks like this

CTX := DBMS_XMLGEN.newContext(sqlstring);
DBMS_XMLGen.setRowsetTag(CTX, 'Header');
DBMS_XMLGen.setRowTag(CTX, '');
RES :=DBMS_XMLGen.getXML(CTX);

I want my output XML to be of this format

<?xml version="1.0"?>
<Header>
<R1>999999999</R1>
<R2>111111</R2>
<R3>333</R3>
<R4>ABCD1234<R4>
<R5>EF</R5>
<R6>0</R6>
</Header>

Now, R1, R2, R3 are generated using Oracle Sequence. R4 a database field value concatenated with static text. R5 and R6 are static text again.

I have declared a cursor, which will help me in fetching R4.

Iam struck with constructing "sqlstring" dynamically. How can I frame it in a dynamic way so that I can directly pass it to DBMS_XMLGEN.newContext? Please help me out.

Raajesh

Tom Kyte
December 01, 2006 - 5:33 am UTC

if you are passing "sqlstring" as a variable - which you are - you have already answered your own question?

You can put anything you want in sqlstring, so go ahead and do that - put anything you want in there.

How do I get individual element value

petrish, January 09, 2007 - 3:05 pm UTC

If I want to get SPOTRATE_8 and CCY element values from the xmltype how do I get it. Thank you for your help.

declare
resp XMLType;
soap_respond_clob CLOB;

begin

resp := XMLType.createXML(soap_respond_clob);
l_val := xmltype.extract(resp,???).getstringval();

end;

soap_respond_clob has following XML in it.

<env:Envelope xmlns:env=" http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance"
xmlns:soapenc=" http://schemas.xmlsoap.org/soap/encoding/"
xmlns:xsd=" http://www.w3.org/2001/XMLSchema" >
<env:Body env:encodingStyle=" http://schemas.xmlsoap.org/soap/encoding/" >
<m:getLastClosingRatesResponse xmlns:m=" http://extract.closingrates/ClosingRatesService" >
<result xmlns:n1="java:extract.closingrates"
soapenc:arrayType="n1:ClosingRate[119]">
<ClosingRate xsi:type="n1:ClosingRate">
<BR xsi:type="xsd:string">01</BR>
<CCY xsi:type="xsd:string">ADP</CCY>
<DAY xsi:type="xsd:int">8</DAY>
<SPOTRATE_8 xsi:type="xsd:double">188.26</SPOTRATE_8>
<TERMS xsi:type="xsd:string">D</TERMS>
<YRMONTH href="#ID_8">
</YRMONTH>
</ClosingRate>
<ClosingRate xsi:type="n1:ClosingRate">
<BR xsi:type="xsd:string">01</BR>
<CCY xsi:type="xsd:string">AED</CCY>
<DAY xsi:type="xsd:int">8</DAY>
<SPOTRATE_8 xsi:type="xsd:double">3.67165</SPOTRATE_8>
<TERMS xsi:type="xsd:string">D</TERMS>
<YRMONTH href="#ID_8">
</YRMONTH>
</ClosingRate>
</result>
</m:getLastClosingRatesResponse>
<xsd:string xmlns:xsd=" http://www.w3.org/2001/XMLSchema"
xsi:type="xsd:string"
id="ID_8">200701</xsd:string>
</env:Body>
</env:Envelope>

Generating xml

Rishi, March 16, 2007 - 6:57 pm UTC

Hello Tom,
Thanks for the amazing responses provided for all the questions posted in your website. I have a question as well.
I am a newbie to Oracle xml utilities.
on running the query below all I get is blank rows.
select xmlelement("param_name",param_name) p_tag from tbl_data_bcd
/

I was expecting to see something like
P_TAG
-------
<param_name>ALT_HYBRID_NORM</param_name>
<param_name>ALT_HYBRID_MIN</param_name>
<param_name>ALT_HYBRID_MAX</param_name>
...
...

Instead, all I see is
P_TAG
----------------------



P_TAG
----------------------



Am using the simplest possible construct here. Am I missing something?
I am using SQL*Plus: Release 10.1.0.4.2 and
the database is 10GR2

XML

A reader, April 11, 2007 - 1:25 pm UTC

TOm:

Do you know why this XML is not displayed as an XML format in the browser

PROCEDURE TEST_XML

IS
BEGIN
htp.p('
-<?xml version="1.0" encoding="ISO-8859-1" ?>');
htp.p('
-<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don''t forget me this weekend!</body>
</note> ');


END; -- Procedure

Browser displays this:
- - - Tove Jani Reminder Don't forget me this weekend!

instead of this:

<?xml version="1.0" encoding="ISO-8859-1" ?>
- <note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>

even though the view source does show this.
Tom Kyte
April 11, 2007 - 5:43 pm UTC

you'd need to put out the right mime header, else it is just treated as HTML

owa_util.mime_header

xml

A reader, April 12, 2007 - 12:00 pm UTC

Tom:

I did add the MIME type. But here is what the browser says.

The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.
--------------------------------------------------------------------------------
Invalid at the top level of the document. Error processing resource ' http://xxx.xxx.xx/xml'
Line 1, P...

<?xml version="1.0" ?>



Do I need to create a DTD file and a <!DOCTYPE statement for this to display




OWA_UTIL.mime_header('text/xml',TRUE);
htp.p('<?xml version="1.0"?>');
htp.p('
-<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don''t forget me this weekend!</body>
</note> ');

xml

A reader, April 17, 2007 - 5:26 pm UTC

Tom:

you dont have any hint on why browser still wont display the xml syntax even after adding the MIME type?
Tom Kyte
April 18, 2007 - 11:48 am UTC

maybe you have the wrong mime type for your browser. what is your web server returning as the mime type when you just access an xml file stored on the server?

mime type

A reader, April 23, 2007 - 5:21 pm UTC

Tom:

How do I find out what is the web server returning when I put an xml doucument. You mean just ftp an xml file to the unix machine and create a uRL to access it and then do a "View Source" or soemthing else?

I am using mod_plsql and Internet Explorer.

XSLT 1.0 spec states that MIME types text/xml and application/xml should be used.

I am wondering if I have to do some changes in my windows registry where you attach a file extension with a specific application.


Tom Kyte
April 23, 2007 - 6:01 pm UTC

look at the headers returned to the browser using whatever tool you want from a file you put on the web server and just access.

then see what headers are being returned by your plsql procedure

and make sure they match up

xml

Sam, August 02, 2007 - 1:32 pm UTC

Tom:


I need to be able to display null database values in an XML generated file. Since contract below was null it displayed one tag in opposite. I need to be able to display
<CONTRACT></CONTRACT>

Do you know a way to do that. I tried the NVL(variable,'') but it still did not work.

BOOK_NO>DD28001</BOOK_NO>
<CREATOR>ABC</CREATOR>
<CONTRACT />
<TITLE>HArry Potter</TITLE>
<AUTHOR>Plain</AUTHOR>

Thanks
Tom Kyte
August 05, 2007 - 12:36 pm UTC

you cannot accept valid XML as your output? Why use a standard if you are not standard compliant?

http://www.w3.org/TR/REC-xml/#sec-starttags

XMLTYPE

Rajesh, October 24, 2007 - 5:04 pm UTC

Hi Tom,

I have a table with five columns line_qty,trace_cde,line_change_log,user and date
of which line_change_log is of XMLTYPE datatype.

I created a trigger on the update of line_qty or trace_cde
in the table. If they are updated, then the line_change_log is updated as follows..

The XMLType data "line_change_log" before trigger fire:

<Changes>
<Change>
<User>coreid</User>
<Date>sysdate</Date>
<LINEITEMQTY old= "3" new="5" />
<TRACECODE old="111" new="222" />
</Change>
</Changes>

After the trigger is fired, line_change_log will be updated with one more <Change></Change> tag along with the updated new values as follows..

<Changes>
<Change>
<User>coreid</User>
<Date>01/01/00</Date>
<LINEITEMQTY old= "3" new="5" />
<TRACECODE old="111" new="222" />
</Change>
<Change>
<User>Tom</User>
<Date>SYSDATE</Date>
<LINEITEMQTY old= "5" new="8" />
<TRACECODE old="222" new="333" />
</Change>
</Changes>


I created the trigger as follows..

CREATE OR REPLACE TRIGGER LINE_XML_UPDATE
BEFORE UPDATE ON LINE_ITEM
FOR EACH ROW
DECLARE
v_change_log CLOB;
v_xml xmltype;
BEGIN

IF (:OLD.LINE_ITEM_QTY <> :NEW.LINE_ITEM_QTY) OR
(:OLD.LINE_TRACE_CODE <> :OLD.LINE_TRACE_CODE)
THEN

v_change_log := '<Change>' ;
v_change_log := v_change_log||'<Date>'||:NEW.LINE_LAST_UPDATE||'</Date>';
v_change_log := v_change_log||'<User>'||:NEW.LINE_LAST_UPDATE_BY||'</User>';

IF (:OLD.LINE_ITEM_QTY <>:NEW.LINE_ITEM_QTY)
THEN

v_change_log := v_change_log || '<LINEITEMQTY OLD="' || :OLD.LINE_ITEM_QTY || '"NEW="' || :NEW.LINE_ITEM_QTY || '"/>';

END IF;

IF (:OLD.LINE_TRACE_CODE <> :NEW.LINE_TRACE_CODE)
THEN

v_change_log := v_change_log || '<TRACECODE OLD="' || :OLD.LINE_TRACE_CODE || '"NEW="' || :NEW.LINE_TRACE_CODE || '"/>';

END IF;

v_change_log := v_change_log || '</Change>' ;

dbms_output.put_line('in CQI');
dbms_output.put_line(v_change_log);

SELECT INSERTCHILDXML(v_xml,'/Changes','Change',xmltype.createXML(v_change_log))
INTO v_xml
FROM dual;

:NEW.LINE_CHANGE_LOG := v_xml;

END IF;

END;

I also used updateXML functions and INSERTXMLBEFORE functions. I couldnt find out the right function to do what I wanted. Can you please help me in this ?


Thanks
Rajesh

sqlx

prasanna, November 29, 2007 - 3:51 pm UTC

I am trying to write a SQL query to generate XML document that contains where a parent node can contain several child nodes.

Relation:
There are n number of loan records and each loan record can in turn have n number of loanalloc records.

See below two queries:

SELECT XMLELEMENT("Loans",
XMLAGG(XMLElement("Loan", XMLATTRIBUTES (l.LOAN_ID as "id"),
XMLForest(to_char(l.LOAN_SETTLE_DT, 'mm/dd/yyyy') as "settleDate",
l.LOAN_STATUS_CD as "status")))).getStringVal() as myXMLElement
FROM LOAN l
WHERE l.LOAN_SQ = 1
and rownum < 24
order by l.LOAN_ID;


SELECT XMLELEMENT("LoanAllocs",
XMLAGG(XMLElement("LoanAlloc",
XMLForest(la.LOAN_ALLOC_SQ as "allocSeq",
to_char(la.TERM_DT, 'mm/dd/yyyy') as "termDate",
la.FUND_ID as "fund",
to_char(la.SETTLE_DT, 'mm/dd/yyyy') as "settleDate")))).getStringVal() as myXMLElement
FROM LOAN_ALLOC la
WHERE la.LOAN_SQ = 1
and rownum < 24
order by lA.LOAN_ALLOC_SQ;



First query generates XML as

<Loans>
<Loan id="135929">
<settleDate>01/12/2004</settleDate>
<status>A</status>
</Loan>
<Loan id="135930">
<settleDate>01/12/2004</settleDate>
<status>A</status><
/Loan>
¿
¿
</Loans>


And second query generates XML as

<LoanAllocs>
<LoanAlloc>
<allocSeq>2646664</allocSeq>
<fund>01000000GE2G</fund>
<settleDate>12/10/2003</settleDate>
</LoanAlloc><LoanAlloc>
<allocSeq>2646666</allocSeq>
<fund>01000000CMD4</fund>
<settleDate>12/10/2003</settleDate>
</LoanAlloc>
¿
¿
</LoanAllocs>


But what I really want is as below, where loan allocations for each loan are included within its branch. Is this possible?

<Loans>
<Loan id="135929">
<settleDate>01/12/2004</settleDate>
<status>A</status>
<LoanAlloc>
<allocSeq>2646664</allocSeq>
<fund>01000000GE2G</fund>
<settleDate>12/10/2003</settleDate>
</LoanAlloc>
<LoanAlloc>
<allocSeq>2646666</allocSeq>
<fund>01000000CMD4</fund>
<settleDate>12/10/2003</settleDate>
</LoanAlloc>
¿
</Loan>
<Loan id="135930">
<settleDate>01/12/2004</settleDate>
<status>A</status><
<LoanAlloc>
<allocSeq>2646664</allocSeq>
<fund>01000000GE2G</fund>
<settleDate>12/10/2003</settleDate>
</LoanAlloc>
<LoanAlloc>
<allocSeq>2646666</allocSeq>
<fund>01000000CMD4</fund>
<settleDate>12/10/2003</settleDate>
</LoanAlloc>
¿

/Loan>
¿
¿
</Loans>

Thanks.

Problem with XMP query and parallel execution

Liuben Ivanov, January 18, 2008 - 5:01 am UTC

Hi Tom,

I have a problem with parallel execution of query that use XML functions. See following script:


DROP TABLE PLAN_TABLE;

DROP TABLE TEST_TAB;

-- PLAN_TABLE --

CREATE TABLE PLAN_TABLE
(
STATEMENT_ID VARCHAR2(30 CHAR),
PLAN_ID NUMBER,
TIMESTAMP DATE,
REMARKS VARCHAR2(4000 CHAR),
OPERATION VARCHAR2(30 CHAR),
OPTIONS VARCHAR2(255 CHAR),
OBJECT_NODE VARCHAR2(128 CHAR),
OBJECT_OWNER VARCHAR2(30 CHAR),
OBJECT_NAME VARCHAR2(30 CHAR),
OBJECT_ALIAS VARCHAR2(65 CHAR),
OBJECT_INSTANCE INTEGER,
OBJECT_TYPE VARCHAR2(30 CHAR),
OPTIMIZER VARCHAR2(255 CHAR),
SEARCH_COLUMNS NUMBER,
ID INTEGER,
PARENT_ID INTEGER,
DEPTH INTEGER,
POSITION INTEGER,
COST INTEGER,
CARDINALITY INTEGER,
BYTES INTEGER,
OTHER_TAG VARCHAR2(255 CHAR),
PARTITION_START VARCHAR2(255 CHAR),
PARTITION_STOP VARCHAR2(255 CHAR),
PARTITION_ID INTEGER,
OTHER LONG,
DISTRIBUTION VARCHAR2(30 CHAR),
CPU_COST INTEGER,
IO_COST INTEGER,
TEMP_SPACE INTEGER,
ACCESS_PREDICATES VARCHAR2(4000 CHAR),
FILTER_PREDICATES VARCHAR2(4000 CHAR),
PROJECTION VARCHAR2(4000 CHAR),
TIME INTEGER,
QBLOCK_NAME VARCHAR2(30 CHAR)
);


-- TEST_TAB TABLE --

CREATE TABLE test_tab
(ID NUMBER(38) NOT NULL,
DATA VARCHAR2(100) NULL
);

CREATE UNIQUE INDEX test_tab_pk ON VIES.TEST_TAB
(ID)
NOLOGGING
NOPARALLEL
COMPUTE STATISTICS;

ALTER TABLE TEST_TAB
ADD CONSTRAINT test_tab_pk
PRIMARY KEY (ID);


-- populate table --

BEGIN
FOR i IN 1 .. 1000000 LOOP
INSERT INTO TEST_TAB
VALUES (i, 'DATA - ' || TO_CHAR(i));
END LOOP;

COMMIT;
END;

-- PARALLEL QUERY 1 --

EXPLAIN PLAN SET STATEMENT_ID = 'parallel query 1' FOR
SELECT /*+ parallel(t)*/
t.ID, t.DATA
FROM TEST_TAB t;

COMMIT ;

SELECT *
FROM TABLE(DBMS_XPLAN.display('PLAN_TABLE', 'parallel query 1', 'all'));


TRUNCATE TABLE plan_table;


-- PARALLEL QUERY 2 --

EXPLAIN PLAN SET STATEMENT_ID = 'parallel query 2' FOR
SELECT /*+ parallel(t)*/
XMLELEMENT ("table",
XMLAGG( XMLELEMENT ("tr",
XMLELEMENT ("td", xmlattributes ('Number' AS "type"), t.ID),
XMLELEMENT ("td", xmlattributes ('String' AS "type"), t.DATA)
) ORDER BY t.ID DESC) ).getclobval () AS report_record
FROM TEST_TAB t;

COMMIT ;

SELECT *
FROM TABLE(DBMS_XPLAN.display('PLAN_TABLE', 'parallel query 2', 'all'));

TRUNCATE TABLE plan_table;


xml

A reader, November 20, 2008 - 10:25 am UTC


How to get BLOB in XML ?

A reader, February 15, 2010 - 7:59 am UTC

In relation to the question "How to get BLOB in XML ?", is there any new 11g features which will make this easier?

Not too keen on the suggested Java route, just thought there must be some SQL / PL/SQL alternative.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here