Home » Developer & Programmer » JDeveloper, Java & XML » help with xmlagg
help with xmlagg [message #543957] Sat, 18 February 2012 03:33 Go to next message
darbon
Messages: 4
Registered: February 2012
Junior Member
hi, i have a table with 2 coloumns (title,data). This table contain (1,0)(1,1)(1,2)(1,0)(1,1)(1,2). I need to generate a XML file like this:

<radix>
<env>
<title>1</title>
<data>0</data>
<data>1</data>
<data>2</data>
</env>
</radix>

but i have some problems with the query

select XMLELEMENT(name "radix",(XMLELEMENT(name "env",
(select XMLELEMENT(name "title",t.title) from t group by t.title),
(select XMLAGG(XMLELEMENT(name "data",t.data)) from t group by t.data)
)))
from dual

Error SQL: ORA-01427: single-row subquery returns more than one row

What is the right solution? Thanks
Re: help with xmlagg [message #543958 is a reply to message #543957] Sat, 18 February 2012 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67163
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: help with xmlagg [message #543974 is a reply to message #543958] Sat, 18 February 2012 09:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> create table t
  2    (title  number,
  3  	data   number)
  4  /

Table created.

SCOTT@orcl_11gR2> insert all
  2  into t values (1,0)
  3  into t values (1,1)
  4  into t values (1,2)
  5  into t values (1,0)
  6  into t values (1,1)
  7  into t values (1,2)
  8  select * from dual
  9  /

6 rows created.

SCOTT@orcl_11gR2> select * from t
  2  /

     TITLE       DATA
---------- ----------
         1          0
         1          1
         1          2
         1          0
         1          1
         1          2

6 rows selected.

SCOTT@orcl_11gR2> SELECT XMLSERIALIZE
  2  	      (DOCUMENT
  3  		(XMLELEMENT
  4  		  (name "radix",
  5  		   (XMLELEMENT
  6  		     (name "env",
  7  		      (SELECT XMLELEMENT
  8  				(name "title",
  9  				 t.title)
 10  		       FROM   t
 11  		       GROUP  BY t.title),
 12  		      (SELECT XMLAGG
 13  				(XMLELEMENT
 14  				  (name "data",
 15  				   t.data)
 16  				 ORDER BY t.data)
 17  		       FROM   (SELECT DISTINCT data
 18  			       FROM   t) t))))) INDENT SIZE = 0)
 19  FROM   DUAL
 20  /

XMLSERIALIZE(DOCUMENT(XMLELEMENT(NAME"RADIX",(XMLELEMENT(NAME"ENV",(SELECTXMLELE
--------------------------------------------------------------------------------
<radix>
<env>
<title>1</title>
<data>0</data>
<data>1</data>
<data>2</data>
</env>
</radix>


1 row selected.

Re: help with xmlagg [message #544014 is a reply to message #543957] Sun, 19 February 2012 04:37 Go to previous messageGo to next message
darbon
Messages: 4
Registered: February 2012
Junior Member
It's perfect THANKS Smile
Re: help with xmlagg [message #544096 is a reply to message #543974] Mon, 20 February 2012 02:53 Go to previous messageGo to next message
_jum
Messages: 571
Registered: February 2008
Senior Member
A little bit easier to read is IMO:
SELECT XMLSERIALIZE(DOCUMENT 
         XMLElement("radix",
           XMLElement("env",
             XMLFOREST(title "title"), 
               XMLAGG(XMLForest(data as "data")))) INDENT SIZE=0)
  FROM (SELECT DISTINCT title, data FROM t)
 GROUP BY title; 

XMLSERIALIZE(DOCUMENTXMLELEMENT("RADIX",XMLELEMENT("ENV",XMLFOREST(TITLE"TITLE")
---------------
<radix>
<env>
<title>1</title>
<data>0</data>
<data>1</data>
<data>2</data>
</env>
</radix>

1 row selected.
Re: help with xmlagg [message #544417 is a reply to message #544096] Tue, 21 February 2012 09:53 Go to previous message
Barbara Boehmer
Messages: 8922
Registered: November 2002
Location: California, USA
Senior Member
Yes, that's much easier to read and with fewer selects it might be more efficient.
Previous Topic: Java Stored Procedure - if expression
Next Topic: Error while parsing XML file with special characters
Goto Forum:
  


Current Time: Sun Jun 07 02:15:21 CDT 2020