OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » help with xmlagg

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

help with xmlagg [message #543957]

Sat, 18 February 2012 03:33

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

Report message to a moderator

Re: help with xmlagg [message #543958 is a reply to message #543957]

Sat, 18 February 2012 03:45

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: help with xmlagg [message #543974 is a reply to message #543958]

Sat, 18 February 2012 09:31

Barbara Boehmer
Messages: 9103
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.

Report message to a moderator

Re: help with xmlagg [message #544014 is a reply to message #543957]

Sun, 19 February 2012 04:37

darbon
Messages: 4
Registered: February 2012

Junior Member

It's perfect THANKS Smile

Report message to a moderator

Re: help with xmlagg [message #544096 is a reply to message #543974]

Mon, 20 February 2012 02:53

_jum
Messages: 577
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.

Report message to a moderator

Re: help with xmlagg [message #544417 is a reply to message #544096]

Tue, 21 February 2012 09:53

Barbara Boehmer
Messages: 9103
Registered: November 2002
Location: California, USA

Senior Member

Yes, that's much easier to read and with fewer selects it might be more efficient.

Report message to a moderator

Previous Topic:	Java Stored Procedure - if expression
Next Topic:	Error while parsing XML file with special characters

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 00:54:55 CST 2025