OraFAQ Forum: JDeveloper, Java & XML » format/indent XML output

Home » Developer & Programmer » JDeveloper, Java & XML » format/indent XML output

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

format/indent XML output [message #193886]

Tue, 19 September 2006 16:20

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.

Report message to a moderator

Re: format/indent XML output [message #193915 is a reply to message #193886]

Tue, 19 September 2006 22:07

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

Report message to a moderator

Re: format/indent XML output [message #194089 is a reply to message #193915]

Wed, 20 September 2006 14:40

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.

Report message to a moderator

Re: format/indent XML output [message #194228 is a reply to message #194089]

Thu, 21 September 2006 04:23

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.

Report message to a moderator

Re: format/indent XML output [message #199132 is a reply to message #193886]

Fri, 20 October 2006 06:20

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><ParticipantRole>SENDER</ParticipantRole>
---------------^

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

Waiting for reply.

Thanks in advance.

Amol.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 04:24:12 CST 2024