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

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"?>
<ROWSET>
 <ROW>
  <numbers>
   <numbers_ROW>
    <one>1</one>
    <two>2</two>
    <three>3</three>
   </numbers_ROW>
  </numbers>
 </ROW>
</ROWSET>
"

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

Answers


You need to read through this article: http://www.orafaq.com/wiki/DBMS_XMLGEN

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:

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

Would output:

<?xml version="1.0"?>
<ROWSET>
 <NUMBERS>
  <one>1</one>
  <two>2</two>
  <three>3</three>
 </NUMBERS>
</ROWSET>

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

asp.net-mvc-3 backbone.js cross-domain asp.net-web-api

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.