?
Solved

Spool file xml query from Oracle

Posted on 2014-09-30
6
Medium Priority
?
1,002 Views
Last Modified: 2014-09-30
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
Comment
Question by:mrong
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40352637
Try this:
select xmlserialize(document xmltype(dbms_xmlgen.getxml('MyQuery here')) version '1.0' indent) from dual;
0
 

Author Comment

by:mrong
ID: 40352666
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40352682
What version of Oracle are you on(include all 4 numbers)?

I tested it on 11.2.0.2.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mrong
ID: 40352688
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40352717
For 10g try:

select xmltype(dbms_xmlgen.getxml('select id from mytable')).extract('/*') from dual;
0
 

Author Closing Comment

by:mrong
ID: 40352734
Thanks!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question