Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1197
  • Last Modified:

Spool file xml query from Oracle

Greeting,

I use sqlplus to spool xml query but somewhere in the xml, the entries wrap to the second line like the following.

<DESCRIPTION>FORMERLY - xxxxxxxxxx &amp; LLLLLLLLLLLLLLLLL OFFICE BUILDING</DES
CRIPTION>

Below is what I ran. How to fix it?

set linesize 9000;
set trimspool on;
set pages 0;
set feedback off;


spool C:\DATA.xml;
select dbms_xmlgen.getxml('MyQuery here') xml from dual;
spool off;
0
mrong
Asked:
mrong
  • 3
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
Try this:
select xmlserialize(document xmltype(dbms_xmlgen.getxml('MyQuery here')) version '1.0' indent) from dual;
0
 
mrongAuthor Commented:
select xmlserialize(document xmltype(dbms_xmlgen.getxml('select id from mytable')) version '1.0' indent) from dual
                                                                                                        *
ERROR at line 1:
ORA-00907: missing right parenthesis
0
 
slightwv (䄆 Netminder) Commented:
What version of Oracle are you on(include all 4 numbers)?

I tested it on 11.2.0.2.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mrongAuthor Commented:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
0
 
slightwv (䄆 Netminder) Commented:
For 10g try:

select xmltype(dbms_xmlgen.getxml('select id from mytable')).extract('/*') from dual;
0
 
mrongAuthor Commented:
Thanks!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now