Home » Developer & Programmer » JDeveloper, Java & XML » format/indent XML output
format/indent XML output [message #193886] Tue, 19 September 2006 16:20 Go to next message
mfry
Messages: 2
Registered: September 2006
Junior Member
Hi there, I'm new to extracting DB data to XML output using the functions XMLElement, XMLAttributes, XMLAgg...

I'm getting the output I want (saved to a file) in SQLPlus, except that all of the XML is in a single very long line of output. Meanwhile all the examples I've seen in the Oracle docs show neat XML nicely indented, etc. etc.

Am I missing something here? Is there some simple option I need to turn on (or off) to get neat output? Or have the documented examples I saw just been 'doctored' to make them easier to read?

Thanks for any help you can provide,

M.
Re: format/indent XML output [message #193915 is a reply to message #193886] Tue, 19 September 2006 22:07 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
An EXTRACT('/*') on the XML generates formatted output.
SQL> SELECT XMLELEMENT("dataset",
  2           XMLELEMENT("element1", 'test1'),
  3           XMLELEMENT("element2", 'test2')).getStringVal() xml
  4  FROM dual;

XML
--------------------------------------------------------------------------------
<dataset><element1>test1</element1><element2>test2</element2></dataset>

SQL> SELECT XMLELEMENT("dataset",
  2           XMLELEMENT("element1", 'test1'),
  3           XMLELEMENT("element2", 'test2')).EXTRACT('/*').getStringVal() xmlf
  4  FROM dual;

XMLF
--------------------------------------------------------------------------------
<dataset>
  <element1>test1</element1>
  <element2>test2</element2>
</dataset>
I notice that the examples in the documentation get "pretty-printed" XML without doing this, though - that's a mystery to me as well. Smile
Re: format/indent XML output [message #194089 is a reply to message #193915] Wed, 20 September 2006 14:40 Go to previous messageGo to next message
mfry
Messages: 2
Registered: September 2006
Junior Member
Thanks a million, just what I needed. A couple of additional points for anyone else reading this...

1) there's a size limit (around 4k?) on what is returned by getStringVal(). If you need more, use getCLOBValue() instead.

2) I found it useful to put 'set trimspool ON' in my script to avoid having each and every line padded with blank spaces.
Re: format/indent XML output [message #194228 is a reply to message #194089] Thu, 21 September 2006 04:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The limit is exactly 4000 chrs - which is the limit of a VARCHAR2 in SQL.
Re: format/indent XML output [message #199132 is a reply to message #193886] Fri, 20 October 2006 06:20 Go to previous message
bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune
Senior Member
Hi,
Thanks for all ur comments.

But, i have one problem with it.

I run Following query and result save as test.xml But, when i want to open in browser i got the Error.
For a single record there is no error.

/**************************/
SELECT xmlagg(xmlelement ("Participant" , xmlforest ( 'SENDER' AS "ParticipantRole"
, company.company_code AS "Alias"
, company.company_code AS "Code"
, company.company_name AS "Name"
).EXTRACT('/*').getStringVal()
)
) AS "result"
FROM company;
/****************/

Error
/******************/
Only one top level element is allowed in an XML document. Error processing resource 'file:///C:/Documents and Settings/bhoi...

</Participant><Participant>&lt;ParticipantRole&gt;SENDER&lt;/ParticipantRole&gt;
---------------^

/****************/


Waiting for reply.

Thanks in advance.

Amol.


Previous Topic: Using DBMS_XMLDOM to insert into table
Next Topic: Problem in validating the XML with XSD in Oracle
Goto Forum:
  


Current Time: Fri Dec 02 14:35:59 CST 2016

Total time taken to generate the page: 0.30904 seconds