Home » SQL & PL/SQL » SQL & PL/SQL » SQL function to Parse XML document stored in BLOB
SQL function to Parse XML document stored in BLOB [message #201672] Mon, 06 November 2006 05:36 Go to next message
cathycolless
Messages: 2
Registered: November 2006
Location: York, UK
Junior Member
Hi,

I am trying to use PL/SQL developer to extract some survey results.

The survey responses are stored as an XML document inside a BLOB.

Running Oracle 9i are there any functions I can use to parse the XML document stored in the BLOB for me.

Or should I give up and get the document out and parse it in a different development environment ?

Genuine thanks for any suggestions or advice.

TIA
cathy
Re: SQL function to Parse XML document stored in BLOB [message #201675 is a reply to message #201672] Mon, 06 November 2006 05:57 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Have you tried DBMS_LOB ?
Re: SQL function to Parse XML document stored in BLOB [message #201680 is a reply to message #201672] Mon, 06 November 2006 06:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't parse the XML while it's in anything but an XMLTYPE (oracles own internal xml storage format)

In an ideal world, your XML would be in a CLOB. Then you could just use the XMLTYPE function to convert the data.

The tricky bit is going to be getting the data out of the BLOB. I reckon you'll need to do something like
v_varchar2 := utl_raw.CAST_TO_VARCHAR2(dbms_lob.substr(v_blob,v_length,1));
If the XML data is longer than 32767 then you'll need to loop round and extract the data in sections, building them up into a CLOB.
See the docs on DBMS_LOB for more info.
Re: SQL function to Parse XML document stored in BLOB [message #201690 is a reply to message #201680] Mon, 06 November 2006 06:49 Go to previous messageGo to next message
cathycolless
Messages: 2
Registered: November 2006
Location: York, UK
Junior Member
Wow..thanks
What a great list.

That has given me the string.
I will see what I can do with the functions.

Cathy
Re: SQL function to Parse XML document stored in BLOB [message #201694 is a reply to message #201690] Mon, 06 November 2006 07:07 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Once you've got the string out of the Blob, you can use XMLTYPE(string) to convert that to an XMLTYPE.

You can then use Extract and ExtractValue to use conventional XPath phrases to extract xml fragments/data from the XML
Previous Topic: Diagonal Subtraction of Column Values
Next Topic: TNS-12560 TNS Prototcol adapter error
Goto Forum:
  


Current Time: Sun Dec 04 08:55:25 CST 2016

Total time taken to generate the page: 0.14512 seconds