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
Solved

Spool file xml query from Oracle

Posted on 2014-09-30
6
866 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
  • 3
  • 3
6 Comments
 
LVL 76

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 76

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

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

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle DR - data guard failover. 18 45
Database Design Dilemma 6 58
oracle differnce between two timestamps 5 31
join actual table rows based on the column 25 21
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

809 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