Solved

Spool file xml query from Oracle

Posted on 2014-09-30
6
816 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Select Query help 3 32
How to query date ranges with SQL 6 26
VB.Net - CSV to Oracle table 4 32
sql calculate averages 18 32
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now