Home » SQL & PL/SQL » SQL & PL/SQL » Saving XMLTYPE data to server
Saving XMLTYPE data to server [message #225574] Tue, 20 March 2007 06:56 Go to next message
Mike Nibeck
Messages: 49
Registered: May 2002
Member
I want to output the values of an XMLTYPE column in one of our tables into a separate file for each record.

I'm trying to get UTL_FILE to write the data and am having issues. I have found many examples of how to do this in 10, but I'm on 9i. Anyone have any sample code around that will allow me to do this?

_mike
Re: Saving XMLTYPE data to server [message #225609 is a reply to message #225574] Tue, 20 March 2007 08:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If your XML data is less than 32k, then do an XMLTYPE.getstringval(<xml variable/field>) to get a varchar2 dump of the contents.
If it is > 32k, you can use XMLTYPE.getclobval(<xml variable/field>), but you'll have to step through the CLOB that this returns and output the data to UTL_FILE in chunks it can handle.
Re: Saving XMLTYPE data to server [message #225770 is a reply to message #225574] Wed, 21 March 2007 06:24 Go to previous message
Mike Nibeck
Messages: 49
Registered: May 2002
Member
Excellent. Works like a champ, thanks!

_mike
Previous Topic: session time zone
Next Topic: fine tuning SQL
Goto Forum:
  


Current Time: Sun Dec 04 14:30:54 CST 2016

Total time taken to generate the page: 0.04446 seconds