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