The XML generated by using
XMLAgg(XMLElement('student', ...)...)
spits out everything onto one line. Given that I have a very large table, it reaches line length limit when spooling.
I'd like to have each <student>...</student> node on a separate line. This page suggests using XMLText(x'0A') to insert new lines, but SQLPlus doesn't seem to recognize it.
I've already tried :
set long 2000000000
set linesize 32767
set wrap on
set trimspool on
The usual trick in 10g was to add .extract('/*') on the outer xml operation you were doing eg
xmlagg(....).extract('/*')
but that doesn't work in 11g. for a cross version compatible one using an xsl transform see Generate XML file with Customized XML tags out of oracle database table.
10.2.0.4:
SQL> create table foo (id) as select rownum from dual connect by level <= 2;
Table created.
SQL> select xmlagg(xmlelement("id", xmlelement("id2", id))).extract('/*') a from foo;
A
--------------------------------------------------------------------------------
<id>
<id2>1</id2>
</id>
<id>
<id2>2</id2>
</id>
SQL> select xmlserialize(content xmlagg(xmlelement("id", xmlelement("id2", id))).extract('/*') indent) a from foo;
select xmlserialize(content xmlagg(xmlelement("id", xmlelement("id2", id))).extract('/*') indent) a from foo
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> select xmlagg(xmlelement("id", xmlelement("id2", id))).transform(xmltype('<xsl:stylesheet version="1.0"
2 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
3 <xsl:output omit-xml-declaration="yes" indent="yes"/>
4 <xsl:template match="node()|@*">
5 <xsl:copy>
6 <xsl:apply-templates select="node()|@*"/>
7 </xsl:copy>
8 </xsl:template>
9 </xsl:stylesheet>')) a from foo;
A
--------------------------------------------------------------------------------
<id>
<id2>1</id2>
</id>
<id>
<id2>2</id2>
</id>
and 11.2.0.2/3:
SQL> select xmlagg(xmlelement("id", xmlelement("id2", id))).extract('/*') a from foo;
A
--------------------------------------------------------------------------------
<id><id2>1</id2></id><id><id2>2</id2></id>
SQL> select xmlserialize(content xmlagg(xmlelement("id", xmlelement("id2", id))).extract('/*') indent) a from foo;
A
--------------------------------------------------------------------------------
<id>
<id2>1</id2>
</id>
<id>
<id2>2</id2>
</id>
SQL> select xmlagg(xmlelement("id", xmlelement("id2", id))).transform(xmltype('<xsl:stylesheet version="1.0"
2 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
3 <xsl:output omit-xml-declaration="yes" indent="yes"/>
4 <xsl:template match="node()|@*">
5 <xsl:copy>
6 <xsl:apply-templates select="node()|@*"/>
7 </xsl:copy>
8 </xsl:template>
9 </xsl:stylesheet>')) a from foo;
A
--------------------------------------------------------------------------------
<id>
<id2>1</id2>
</id>
<id>
<id2>2</id2>
</id>
in short, to do this version agnositc, you should use a XSL. if you're only trying this for adhoc stuff, then extract is shorter to type on 10g and xmlserializeis shorter on 11g.
