How to get XML output ? 1

[Exie]

Hi Folks,

I have a simple traditional database, ans I'd like to connect via SQLPlus and submit a query to get XML back. Whats the easiest way to do this ?

select order.* , orderlines.*
from MyAppOrders order,
     MyAppOrderLines orderlines,
where ...

.. to make an output like....

<order>
   <column1> ... </column1>
   <orderlines key=1>
      <column1> ... </column1>
   </orderlines>
</order>

PS. I dont have Oracle 9AS

 

[Welshbird]

You could write yourself a complex 'spool' query from SQL*Plus, but this may be a long-winded way of doing this.

Could you possibly use another Oracle tool? I certainly use SQLTools (v1.42 - kinda handy and free) in order to make it faster to extract data to CSV, so maybe there is a tool which would do this for you. In fact, this may work if you use this then save as XML from Excel?

Sorry I can't be more help.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[taupirho]

Look at xmlelemnt and xmlforest for starters , there are other ways too.

eg

select xmlelement("EMPREC",xmlforest(empno,ename,job))
from scott.emp


<EMPREC>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
</EMPREC>

<EMPREC>
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
</EMPREC>

 

[Exie]

Thanks! Thats a great tip.

I had a go running that, and spooling the output to a file, but it kept getting cut off. I tried setting COLUMN x FORMANT A99999 and everything, but it didnt help.

Any tips to spool this out to a file ?

SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
column DATA format A1000 on
spool /tmp/getXML_output.xml
select xmlelement("INVOICES",xmlforest(accmname,accmaddress_line1,accmaddress_line2)) as data
from accm_data where rownum < 2;
spool off

... produces output like this:

<INVOICES>
  <ACCMNAME>ALPHA LEWIS SIGNS                   </ACCMNAME>
  <ACCMAD

... notice how it cuts off half-way through the tag!

 

[SantaMufasa]

Exie,

You "SET LINESIZE 80", so your output cuts off at 80. Instead, "SET LINESIZE 1000" AND "SET TRIMSPOOL ON" (which right trims the output to just what you are actually outputting instead of padding to 1000.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Exie]

Thanks, good tip.

Unfortunately, it didnt change the output at all, it still cuts off at the same place.

 

[SantaMufasa]

Exie,

I'm happy to help troubleshoot this if you can post a "CREATE TABLE ACCUM_DATA..." and a couple of INSERT statements (preferrably for "ALPHA LEWIS SIGNS") so that I can replicate, as nearly as possible, your scenario.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

You need to do a set long XXXXXX


Where XXXXXXX is a suitably big number

 

[SantaMufasa]

Exie,

Try taupirho's suggestion, then if things are still not behaving properly, you are welcome to post the CREATE+INSERTs I requested, and we'll make sure this gets resolved. (When I tried an XML SELECT similar to yours, above, my output looked fine.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Exie]

Thanks folks!

The "SET LONG 1000" worked a treat!

 

[SantaMufasa]

Then, Exie, if Taupirho's suggestion worked a treat, then it is customary to click the [Thank taupirho for this valuable post!] link, thus awarding a

.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]