Can I remove extra nesting of XML when using DBMS_XMLGEN.getxml and Cursor expression?

I've been searching the documentation and google but I can't seem to find what I am looking for; my version of oracle is

Let's use this simple query:

select dbms_xmlgen.getxml('select cursor(select ''1'' "one", ''2''
"two", ''3'' "three" from dual) "numbers" from dual') from dual;

the result is:

"<?xml version="1.0"?>

Question1: Why was "numbers_row" created when "numbers" is sufficient (at least for my purposes)?

Question2: Can I get rid of that extra nesting with the xmlgen package or some other package? I'm using regular expressions to do so but it seems a bit unreasonable.

Thank you, -joel


You need to read through this article:

It explains how to change the Oracle default names generated by the DBMS_XMLGEN package.

The answer to your Question 1 is: it's the default values and behaviour for the Oracle DBMS_XMLGEN package

For Question 2: You would need to call some additional DBMS_XMLGEN procedures to alter the default values so you would need to use PL/SQL:

   ctx DBMS_XMLGEN.ctxHandle;
   xml CLOB;
   ctx := dbms_xmlgen.newcontext('select ''1'' "one", ''2'' "two", ''3'' "three" from dual');
   dbms_xmlgen.setRowTag(ctx, 'NUMBERS');
   xml := dbms_xmlgen.getxml(ctx);

Would output:

<?xml version="1.0"?>

There are procedures to replace the <ROWSET> value with something more meaningful too.

Hope it helps...

Need Your Help

Efficient cross domain web API like Twitter Facebook Google etc backbone.js cross-domain

I have recently been experimenting with building a cross domain web api, and wow has it been a bumpy journey. I have not had any problems with modern browsers such as Chrome, FF and Safari. The pro...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.