Home » Developer & Programmer » JDeveloper, Java & XML » Create xml (Oracle 10.2.0.3)
Create xml [message #490737] Thu, 27 January 2011 02:20 Go to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member

Hi,

The below is the test case I have created to generate the resulset. Actually the resultset is the outcome of a sql which have join between tables.

create table test2011
(
publication_id varchar2(10),
author_nbr number,
author_nm   varchar2(50),
total_pages number,
qualification_code varchar2(10),
qualification_year date,
title varchar2(200),
univ_code varchar2(10),
univ_name varchar2(100),
jury_f_name varchar2(30),
jury_m_name varchar2(30),
jury_l_name varchar2(30),
subject_seq number,
sub_code varchar2(10),
sub_name varchar2(30),
lang_code varchar2(3),
lang_desc varchar2(30));

insert into test2011 values('000123',1,'Mr. X',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',1,'0786','Chemistry','FR','French');

insert into test2011 values('000123',1,'Mr. X',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',1,'0786','Chemistry','FR','French');

insert into test2011 values('000123',1,'Mr. X',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',2,'0786','Environmental Sc.','FR','French');

insert into test2011 values('000123',1,'Mr. X',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',2,'0786','Environmental Sc.','FR','French');

insert into test2011 values('000123',1,'Mr. X',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',3,'0786','Mathematics','FR','French');

insert into test2011 values('000123',1,'Mr. X',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',3,'0786','Mathematics','FR','French');

insert into test2011 values('000123',2,'Mr. Y',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',1,'0786','Chemistry','FR','French');

insert into test2011 values('000123',2,'Mr. Y',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',1,'0786','Chemistry','FR','French');

insert into test2011 values('000123',2,'Mr. Y',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',2,'0786','Environmental Sc.','FR','French');

insert into test2011 values('000123',2,'Mr. Y',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',2,'0786','Environmental Sc.','FR','French');

insert into test2011 values('000123',2,'Mr. Y',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',3,'0786','Mathematics','FR','French');

insert into test2011 values('000123',2,'Mr. Y',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',3,'0786','Mathematics','FR','French');

insert into test2011 values('000123',3,'Mr. Z',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',1,'0786','Chemistry','FR','French');

insert into test2011 values('000123',3,'Mr. Z',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',1,'0786','Chemistry','FR','French');

insert into test2011 values('000123',3,'Mr. Z',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',2,'0786','Environmental Sc.','FR','French');

insert into test2011 values('000123',3,'Mr. Z',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',2,'0786','Environmental Sc.','FR','French');

insert into test2011 values('000123',3,'Mr. Z',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',3,'0786','Mathematics','FR','French');

insert into test2011 values('000123',3,'Mr. Z',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',3,'0786','Mathematics','FR','French');

insert into test2011 values('000123',4,'Mr. A',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',1,'0786','Chemistry','FR','French');

insert into test2011 values('000123',4,'Mr. A',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',1,'0786','Chemistry','FR','French');

insert into test2011 values('000123',4,'Mr. A',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',2,'0786','Environmental Sc.','FR','French');

insert into test2011 values('000123',4,'Mr. A',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',2,'0786','Environmental Sc.','FR','French');

insert into test2011 values('000123',4,'Mr. A',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Tom',NULL, 'Hanks',3,'0786','Mathematics','FR','French');

insert into test2011 values('000123',4,'Mr. A',1104,'P.hd',to_date('01/01/2009','dd/mm/yyyy'),'Transport and release of chemicals from plastics to the environment and to wildlife',
'0001', 'University of California','Danniel',NULL, 'Jones',3,'0786','Mathematics','FR','French');


I want to generate an xml format like below:

<RES>			
	<publication_nbr>000123</publication_nbr>
	<authors> 
		<author author_nbr="1">
			<author_name>Mr. X</author_name>
		</author >
		<author author_nbr="2">
			<author_name>Mr. Y</author_name>
		</author >
		<author author_nbr="3">
			<author_name>Mr. Z</author_name>
		</author >
		<author author_nbr="4">
			<author_name>Mr. A</author_name>
		</author >		
	</authors>
	<details>
		<total_pages>1104</total_pages>
		<qualification>
			<qualification_code>P.hd</qualification_code>
			<qualification_year>2009</qualification_year>
		</qualification>
		<titles> 
			<title>
				<title_text>Transport and release of chemicals from plastics to the environment and to wildlife</title_text> 
			</title>	
		</titles> 
		<university>
			<code>0001</code>
			<name>University of California</name>
		</university>
		<jury_members> 
			<jury_member> 
				<first_name>Tom</first_name> 
				<middle_name></middle_name>
				<last_name>Hanks</last_name>				
			</jury_member> 
			<jury_member> 
			    <first_name>Danniel</first_name> 
				<middle_name></middle_name>
				<last_name>Jones</last_name>				
			</jury_member> 
		</jury_members>
		<subjects> 
			<subject seq="1">
				<code>0786</code>
				<desc>Chemistry</desc>
			</subject>
			<subject seq="2">
				<code>0700</code>
				<desc>Environmental Sc.</desc>
			</subject>
			<subject seq="3">
				<code>0731</code>
				<desc>Mathematics</desc>
			</subject>
		</subjects>
		   	<lang>
				<lang_code>FR</lang_code>
				<lang_desc>French</lang_desc>
			</lang>
	</details>	
	
</RES>


I need your help here to create the xml format like above.Thanks in advance!

Regards
Ved
Re: Create xml [message #490797 is a reply to message #490737] Thu, 27 January 2011 07:12 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
SELECT XMLELEMENT("RES", XMLELEMENT("publication_nbr", e.publication_id ),
                                  XMLELEMENT("authors",
                                                          XMLELEMENT("author",XMLATTRIBUTES(e.author_nbr AS "author_nbr" )
                                                                                  ,XMLForest ( e.author_nm  as "author_name"))
                  )) AS "result" 
FROM TEST2011 e 
where e.publication_id='000123'

Output:
<RES>
<publication_nbr>000123</publication_nbr>
<authors>
	<author author_nbr="1">
	<author_name>Mr. X</author_name>
	</author>
</authors>
</RES>

This is what I tried.How can I get the repeating authors? I tried using xmlagg but unable to figure out how to achieve the reqd output.

Regards
Ved

[Updated on: Thu, 27 January 2011 08:50]

Report message to a moderator

Re: Create xml [message #490841 is a reply to message #490797] Thu, 27 January 2011 11:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
To get the authors using xmlagg:

SCOTT@orcl_11gR2> SELECT XMLTYPE
  2  	      ('<?xml version="1.0"?>' ||
  3  	       XMLELEMENT
  4  		 ("RES",
  5  		  XMLELEMENT
  6  		    ("publication_nbr", e.publication_id),
  7  		  XMLELEMENT
  8  		    ("authors",
  9  		     XMLAGG
 10  		       (XMLELEMENT
 11  			 ("author",
 12  			  XMLATTRIBUTES
 13  			    (e.author_nbr AS "author_nbr" ),
 14  			    XMLFOREST
 15  			      (e.author_nm  as "author_name"))
 16  			ORDER BY e.author_nbr))))
 17  	      AS "result"
 18  FROM   (SELECT DISTINCT publication_id,
 19  		    author_nbr,
 20  		    author_nm
 21  	     FROM   test2011) e
 22  WHERE  e.publication_id = '000123'
 23  GROUP  BY e.publication_id
 24  /

result
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<RES>
  <publication_nbr>000123</publication_nbr>
  <authors>
    <author author_nbr="1">
      <author_name>Mr. X</author_name>
    </author>
    <author author_nbr="2">
      <author_name>Mr. Y</author_name>
    </author>
    <author author_nbr="3">
      <author_name>Mr. Z</author_name>
    </author>
    <author author_nbr="4">
      <author_name>Mr. A</author_name>
    </author>
  </authors>
</RES>


1 row selected.
Re: Create xml [message #490844 is a reply to message #490841] Thu, 27 January 2011 12:16 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks for the help!

Regards
Ved
Re: Create xml [message #490845 is a reply to message #490841] Thu, 27 January 2011 12:17 Go to previous messageGo to next message
Michel Cadot
Messages: 66643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Barbara, I have 2 questions:
1/ How do you get this nice formatted output, I get a long string without any new lines? Or do you do it yourself manually?
2/ Why do you use an XMLFOREST at line 14 and not an XMLELEMENT?

Regards
Michel

[Note: I am a newbie in XML, so this is not ironic questions but newbie ones]
Re: Create xml [message #490852 is a reply to message #490845] Thu, 27 January 2011 12:54 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
To address Query 2,

In my code, I used XMLForest() function to produce a forest of XML elements from the given list of arguments.
The code was I wrote was a over simplified version.I keep the XMLFOREST() for multiple attribute.

I am a beginner here in XML and was trying to solve the problem following the Oracle doc. Please correct me if I am wrong here.


To address Query1,

What I can think of is..

sql> set long 1000
sql > set pages 1000

> Store the result in temp table(OPTIONAL)
> dbms_xmlgen.getxml to get the result in the proper format

I just do that. I dont know any other way.

Regards
Ved

[Updated on: Thu, 27 January 2011 13:11]

Report message to a moderator

Re: Create xml [message #490855 is a reply to message #490852] Thu, 27 January 2011 13:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Here is the whole thing:

SCOTT@orcl_11gR2> SELECT XMLTYPE
  2  	      ('<?xml version="1.0"?>' ||
  3  	       XMLELEMENT
  4  		 ("RES",
  5  		  XMLELEMENT
  6  		    ("publication_nbr", f.publication_id),
  7  		      (SELECT XMLELEMENT
  8  				("authors",
  9  				 XMLAGG
 10  				   (XMLELEMENT
 11  				     ("author",
 12  				      XMLATTRIBUTES
 13  					(e.author_nbr AS "author_nbr" ),
 14  				      XMLFOREST
 15  					(e.author_nm  as "author_name"))
 16  				    ORDER BY e.author_nbr))
 17  		       FROM   (SELECT DISTINCT publication_id,
 18  				      author_nbr,
 19  				      author_nm
 20  			       FROM   test2011
 21  			       WHERE  publication_id = '000123') e
 22  		       GROUP  BY e.publication_id),
 23  		      (SELECT XMLELEMENT
 24  				("details",
 25  				 XMLELEMENT
 26  				  ("total_pages", e.total_pages),
 27  				 XMLELEMENT
 28  				   ("qualification",
 29  				    XMLELEMENT
 30  				      ("qualification_code", e.qualification_code),
 31  				    XMLELEMENT
 32  				      ("qualification_year", e.qualification_year)),
 33  				 XMLELEMENT
 34  				   ("titles",
 35  				    XMLELEMENT
 36  				      ("title",
 37  				       XMLELEMENT
 38  					 ("title_text", e.title))),
 39  				 XMLELEMENT
 40  				   ("university",
 41  				    XMLELEMENT
 42  				      ("code", e.univ_code),
 43  				    XMLELEMENT
 44  				      ("name", e.univ_name)),
 45  				 (SELECT  XMLELEMENT
 46  					    ("jury_members",
 47  					     XMLAGG
 48  					       (XMLELEMENT
 49  						 ("jury_member",
 50  						   (XMLFOREST
 51  						      (e.jury_f_name as "first_name",
 52  						       e.jury_m_name as "middle_name",
 53  						       e.jury_l_name as "last_name")))))
 54  				  FROM	 (SELECT DISTINCT publication_id,
 55  						 jury_f_name,
 56  						 jury_m_name,
 57  						 jury_l_name
 58  					  FROM	 test2011
 59  					  WHERE  publication_id = '000123') e
 60  				  GROUP  BY e.publication_id),
 61  				 (SELECT  XMLELEMENT
 62  					    ("subjects",
 63  					     XMLAGG
 64  					       (XMLELEMENT
 65  						 ("subject",
 66  						  XMLATTRIBUTES
 67  						    (e.subject_seq AS "seq" ),
 68  						  XMLFOREST
 69  						    (e.sub_code  as "code",
 70  						     e.sub_name  as "desc"))
 71  						ORDER BY e.subject_seq))
 72  				  FROM	 (SELECT DISTINCT publication_id,
 73  						 subject_seq,
 74  						 sub_code,
 75  						 sub_name
 76  					  FROM	 test2011
 77  					  WHERE  publication_id = '000123') e
 78  				  GROUP  BY e.publication_id),
 79  				  (SELECT XMLELEMENT
 80  					    ("lang",
 81  					     XMLFOREST
 82  					       (e.lang_code as "lang_code",
 83  						e.lang_desc as "lang_desc"))
 84  				   FROM   (SELECT DISTINCT lang_code, lang_desc
 85  					   FROM   test2011
 86  					   WHERE  publication_id = '000123') e))
 87  		       FROM   (SELECT DISTINCT publication_id,
 88  				      total_pages,
 89  				      qualification_code,
 90  				      qualification_year,
 91  				      title,
 92  				      univ_code,
 93  				      univ_name
 94  			       FROM   test2011
 95  			       WHERE  publication_id = '000123') e)
 96  	    )) AS "result"
 97  FROM   (SELECT DISTINCT publication_id
 98  	     FROM   test2011
 99  	     WHERE  publication_id = '000123') f
100  /

result
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<RES>
  <publication_nbr>000123</publication_nbr>
  <authors>
    <author author_nbr="1">
      <author_name>Mr. X</author_name>
    </author>
    <author author_nbr="2">
      <author_name>Mr. Y</author_name>
    </author>
    <author author_nbr="3">
      <author_name>Mr. Z</author_name>
    </author>
    <author author_nbr="4">
      <author_name>Mr. A</author_name>
    </author>
  </authors>
  <details>
    <total_pages>1104</total_pages>
    <qualification>
      <qualification_code>P.hd</qualification_code>
      <qualification_year>2009-01-01</qualification_year>
    </qualification>
    <titles>
      <title>
        <title_text>Transport and release of chemicals from plastics to the envi
ronment and to wildlife</title_text>
      </title>
    </titles>
    <university>
      <code>0001</code>
      <name>University of California</name>
    </university>
    <jury_members>
      <jury_member>
        <first_name>Tom</first_name>
        <last_name>Hanks</last_name>
      </jury_member>
      <jury_member>
        <first_name>Danniel</first_name>
        <last_name>Jones</last_name>
      </jury_member>
    </jury_members>
    <subjects>
      <subject seq="1">
        <code>0786</code>
        <desc>Chemistry</desc>
      </subject>
      <subject seq="2">
        <code>0786</code>
        <desc>Environmental Sc.</desc>
      </subject>
      <subject seq="3">
        <code>0786</code>
        <desc>Mathematics</desc>
      </subject>
    </subjects>
    <lang>
      <lang_code>FR</lang_code>
      <lang_desc>French</lang_desc>
    </lang>
  </details>
</RES>


1 row selected.

SCOTT@orcl_11gR2>

Re: Create xml [message #490856 is a reply to message #490845] Thu, 27 January 2011 13:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Thu, 27 January 2011 10:17

1/ How do you get this nice formatted output, I get a long string without any new lines? Or do you do it yourself manually?


By making it an xmltype. Note the differences in the two below.

SCOTT@orcl_11gR2> select xmlelement
  2  	      ("deptnos",
  3  	       xmlagg
  4  		 (xmlelement ("deptno", d.deptno)))
  5  from   dept d
  6  /

XMLELEMENT("DEPTNOS",XMLAGG(XMLELEMENT("DEPTNO",D.DEPTNO)))
--------------------------------------------------------------------------------
<deptnos><deptno>10</deptno><deptno>20</deptno><deptno>30</deptno><deptno>40</de
ptno></deptnos>


1 row selected.

SCOTT@orcl_11gR2> select xmltype
  2  	      ('<?xml version="1.0"?>' ||
  3  	       xmlelement
  4  		 ("deptnos",
  5  		   xmlagg
  6  		     (xmlelement ("deptno", d.deptno))))
  7  from   dept d
  8  /

XMLTYPE('<?XMLVERSION="1.0"?>'||XMLELEMENT("DEPTNOS",XMLAGG(XMLELEMENT("DEPTNO",
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<deptnos>
  <deptno>10</deptno>
  <deptno>20</deptno>
  <deptno>30</deptno>
  <deptno>40</deptno>
</deptnos>


1 row selected.

SCOTT@orcl_11gR2>


Michel Cadot wrote on Thu, 27 January 2011 10:17

2/ Why do you use an XMLFOREST at line 14 and not an XMLELEMENT?


I just copied it from Ved's original code.

Michel Cadot wrote on Thu, 27 January 2011 10:17

[Note: I am a newbie in XML, so this is not ironic questions but newbie ones]


So am I. Sometimes I just get determined to figure something out and do enough research and adapt enough examples and put the pieces together until I get it. However, as usual, with such new things, I have a feeling there must be an easier way. Also, I only used xmlagg on the columns that had more than one distinct value. With different data, you might need to use it on more of them, but it should not be too difficult to see how to expand the pattern.

[Updated on: Thu, 27 January 2011 13:56]

Report message to a moderator

Re: Create xml [message #490857 is a reply to message #490856] Thu, 27 January 2011 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 66643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
By making it an xmltype. Note the differences in the two below.

Something strange, I can't get it in 10.2 or 11.2 (WinXP) still have the plain string any way.
On which OS are you working, maybe a difference in sqlplus for this?

Regards
Michel

Re: Create xml [message #490858 is a reply to message #490857] Thu, 27 January 2011 14:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
I am running Oracle 11.2.0.1.0 on Windows 7 Professional. I have included my long and longchunksize settings below, as well as code for you to copy and paste below that, to make sure you are running the same thing.

SCOTT@orcl_11gR2> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE	11.2.0.1.0	Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

SCOTT@orcl_11gR2> show linesize
linesize 80
SCOTT@orcl_11gR2> show long
long 100000
SCOTT@orcl_11gR2> show longchunksize
longchunksize 100000
SCOTT@orcl_11gR2> select xmltype
  2  	      ('<?xml version="1.0"?>' ||
  3  	       xmlelement
  4  		 ("deptnos",
  5  		   xmlagg
  6  		     (xmlelement ("deptno", d.deptno))))
  7  from   dept d
  8  /

XMLTYPE('<?XMLVERSION="1.0"?>'||XMLELEMENT("DEPTNOS",XMLAGG(XMLELEMENT("DEPTNO",
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<deptnos>
  <deptno>10</deptno>
  <deptno>20</deptno>
  <deptno>30</deptno>
  <deptno>40</deptno>
</deptnos>


1 row selected.

SCOTT@orcl_11gR2>


set linesize 80
set long 100000
set longchunksize 100000
select xmltype
         ('<?xml version="1.0"?>' || 
          xmlelement 
            ("deptnos",
              xmlagg 
                (xmlelement ("deptno", d.deptno))))
from   dept d
/
Re: Create xml [message #490892 is a reply to message #490858] Fri, 28 January 2011 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 66643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well I don't understand still get the same thing:
SQL> set linesize 80
SQL> set long 100000
SQL> set longchunksize 100000
SQL> select xmltype
  2           ('<?xml version="1.0"?>' ||
  3            xmlelement
  4              ("deptnos",
  5                xmlagg
  6                  (xmlelement ("deptno", d.deptno))))
  7  from   dept d
  8  /
XMLTYPE('<?XMLVERSION="1.0"?>'||XMLELEMENT("DEPTNOS",XMLAGG(XMLELEMENT("DEPTNO",
--------------------------------------------------------------------------------
<?xml version="1.0"?><deptnos><deptno>10</deptno><deptno>20</deptno><deptno>30</
deptno><deptno>40</deptno></deptnos>

SQL> @v

Version Oracle : 11.2.0.1.0

Maybe something added in Windows7 sqlplus implementation (I think you have a 64-bit one when my XP is a 32-bit).
I will check with other OS.

Regards
Michel

Re: Create xml [message #491001 is a reply to message #490892] Fri, 28 January 2011 07:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
I am baffled as well. I tend to suspect a SQL*Plus setting. I have provided all of mine below and suggest that you try saving the commands below as a script and running all of them, then test the xml query again. If it works, then you can test individual settings and figure out which one. If it does not work, then we will have eliminated SQL*Plus settings as a potential difference.

set appinfo OFF
set appinfo "SQL*Plus"
set arraysize 15
set autocommit OFF
set autoprint OFF
set autorecovery OFF
set autotrace OFF
set blockterminator "."
set cmdsep OFF
set colsep " "
set compatibility NATIVE
set concat "."
set copycommit 0
set copytypecheck ON
set define "&"
set describe DEPTH 1 LINENUM OFF INDENT ON
set echo ON
set editfile "afiedt.buf"
set embedded OFF
set escape OFF
set escchar OFF
set exitcommit ON
set feedback ON
set flagger OFF
set flush ON
set heading ON
set headsep "|"
set linesize 80
set logsource ""
set long 100000
set longchunksize 100000
set markup HTML OFF HEAD "<style type='text/css'> body {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} p {font:10pt Arial,Helvetica,sans-serif; color:black; background:White;} table,tr,td {font:10pt Arial,Helvetica,sans-serif; color:Black; background:#f7f7e7; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} th {font:bold 10pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} h1 {font:16pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;-
} h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} a {font:9pt Arial,Helvetica,sans-serif; color:#663300; background:#ffffff; margin-top:0pt; margin-bottom:0pt; vertical-align:top;}</style><title>SQL*Plus Report</title>" BODY "" TABLE "border='1' width='90%' align='center' summary='Script output'" SPOOL OFF ENTMAP ON PRE OFF
set newpage 1
set null ""
set numformat ""
set numwidth 10
set pagesize 9999
set pause OFF
set recsep WRAP
set recsepchar " "
set securedcol OFF
set serveroutput ON SIZE 1000000 FORMAT WORD_WRAPPED
set shiftinout invisible
set showmode OFF
set sqlblanklines OFF
set sqlcase MIXED
set sqlcontinue "> "
set sqlnumber ON
set sqlpluscompatibility 11.2.0
set sqlprefix "#"
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER'_11gR2'> "
set sqlterminator ";"
set suffix "sql"
set tab ON
set termout ON
set time OFF
set timing OFF
set trimout ON
set trimspool ON
set underline "-"
set verify ON
set wrap ON

Re: Create xml [message #491016 is a reply to message #491001] Fri, 28 January 2011 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I executed all the "set" statements you gave but still have the same thing. /forum/fa/1637/0/

Regards
Michel
Re: Create xml [message #491031 is a reply to message #491016] Fri, 28 January 2011 09:05 Go to previous message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
We have tested with the same Oracle version and same SQL*Plus settings, so as you have suggested, I believe that narrows it down to differences in operating system versions. Perhaps some others can test on Windows 7 Professional and Windows XP and such and see if they get what I get or what you get and post it to support that theory. If that is the case, then I don't know that there is anything that you can do to make it display better on your system, except upgrade the operating system, which is obviously not worth doing just for this one issue.

Previous Topic: Javascript carry the variable over to next page
Next Topic: can't insert data into database
Goto Forum:
  


Current Time: Thu Oct 17 20:31:41 CDT 2019