OTN Discussion Forums : Extract XML data/element from BLOB ...

user576711

Posts: 5
Registered: 05/25/07

Extract XML data/element from BLOB column using PLSQL
Posted: May 25, 2007 5:09 PM

Hi,

I have a table (Oracle 10XE) with two columns, 'ID' (Number) and 'ID_Details' (BLOB). Column 'ID_Details' is used to save XML document.

How do I extract XML document-elements from column type BLOB?

I have tried 'Extract', 'ExtractValue' and 'dburitype.createuri' functions but still getting following errors.

FUNCTION = getStringVal,getXML,getCLOB,getBLOB(873) 873 is csid for Characterset AL32UTF8

SELECT Extractvalue(x.ID_Details.FUNCTION()),'/Customer/Name') As XMLSrc FROM TableName x where x.ID = 12345;
SELECT Extract(x.ID_Details.FUNCTION()),'/Customer/Name') As XMLSrc FROM TableName x where x.ID = 12345;
SELECT Extract(x.ID_Details.FUNCTION()),'/Customer/Name/text(') As XMLSrc FROM TableName x where x.ID = 12345;
ORA-22806: not an object or REF

SELECT Extract( x.ID_Details,'/Customer/Name/text()').Function() As XMLSrc FROM TableName x where x.ID = 12345;
SELECT ExtractValue( x.ID_Details,'/Customer/Name').Function() As XMLSrc FROM TableName x where x.ID = 12345;
ORA-00932: inconsistent datatypes: expected - got -

select (dburitype.createuri('/Customer/Name')).getclob() As Results from (Select ID_Details from TableName where x.ID = 12345);
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'

Thanks
Avi.

michaels2

Posts: 5,875
Registered: 09/24/06

Re: Extract XML data/element from BLOB column using PLSQL
Posted: May 25, 2007 10:43 PM

in response to: user576711

Reply

michaels>  CREATE TABLE tablename (ID NUMBER, id_details BLOB)
Table created.
 
michaels>  INSERT INTO tablename
     VALUES (12345,
             UTL_RAW.cast_to_raw
                       ('<Customer><Name>Your Customer Name</Name></Customer>')
            )
1 row created.
 
michaels>  SELECT XMLTYPE (UTL_RAW.cast_to_varchar2 (id_details)).EXTRACT('Customer/Name/text()') XMLSrc 
  FROM tablename x WHERE x.ID=12345
 
XMLSRC                
----------------------
Your Customer Name

Maxim Demenko

Posts: 1,392
Registered: 11/05/05

Re: Extract XML data/element from BLOB column using PLSQL
Posted: May 26, 2007 4:01 AM

in response to: michaels2

Reply

And for bigger blob's dbms_lob.converttoclob can be used to get a clob instance, which can be passed to xmltype constructor.
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lob.htm#sthref3647

Best regards

Maxim

user576711

Posts: 5
Registered: 05/25/07

Re: Extract XML data/element from BLOB column using PLSQL
Posted: May 26, 2007 12:40 PM

in response to: michaels2

Reply

Tried below statement...

SELECT XMLTYPE (UTL_RAW.cast_to_varchar2 (id_details)).EXTRACT('Customer/Name/text()') XMLSrc
FROM tablename x WHERE x.ID=12345

Displays error:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW convesion (actual:12458, maximum:2000)

Maxim Demenko

Posts: 1,392
Registered: 11/05/05

Re: Extract XML data/element from BLOB column using PLSQL
Posted: May 26, 2007 2:18 PM

in response to: user576711

Reply

I fully agree with you, reading documentation is boring

SQL> PROMPT Setup
Setup
SQL> CREATE OR REPLACE FUNCTION CLOB2BLOB(L_CLOB CLOB) RETURN BLOB IS
  2    L_BLOB         BLOB;
  3    L_SRC_OFFSET      NUMBER;
  4    L_DEST_OFFSET  NUMBER;
  5    L_BLOB_CSID       NUMBER := DBMS_LOB.DEFAULT_CSID;
  6    V_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
  7    L_WARNING         NUMBER;
  8    L_AMOUNT  NUMBER;
  9  BEGIN
 10    DBMS_LOB.CREATETEMPORARY(L_BLOB, TRUE);
 11    L_SRC_OFFSET     := 1;
 12    L_DEST_OFFSET := 1;
 13    L_AMOUNT := DBMS_LOB.GETLENGTH(L_CLOB);
 14    DBMS_LOB.CONVERTTOBLOB(L_BLOB,
 15                           L_CLOB,
 16                           L_AMOUNT,
 17                           L_SRC_OFFSET,
 18                           L_DEST_OFFSET,
 19                           1,
 20                           V_LANG_CONTEXT,
 21                           L_WARNING);
 22    RETURN L_BLOB;
 23  END;
 24  / 
 
Function created.
 
SQL> CREATE OR REPLACE FUNCTION BLOB2CLOB(L_BLOB BLOB) RETURN CLOB IS
  2    L_CLOB         CLOB;
  3    L_SRC_OFFSET      NUMBER;
  4    L_DEST_OFFSET  NUMBER;
  5    L_BLOB_CSID       NUMBER := DBMS_LOB.DEFAULT_CSID;
  6    V_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
  7    L_WARNING         NUMBER;
  8    L_AMOUNT  NUMBER;
  9  BEGIN
 10    DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE);
 11    L_SRC_OFFSET     := 1;
 12    L_DEST_OFFSET := 1;
 13    L_AMOUNT := DBMS_LOB.GETLENGTH(L_BLOB);
 14    DBMS_LOB.CONVERTTOCLOB(L_CLOB,
 15                           L_BLOB,
 16                           L_AMOUNT,
 17                           L_SRC_OFFSET,
 18                           L_DEST_OFFSET,
 19                           1,
 20                           V_LANG_CONTEXT,
 21                           L_WARNING);
 22    RETURN L_CLOB;
 23  END;
 24  / 
 
Function created.
 
SQL> CREATE TABLE blob_xml(ID NUMBER,content BLOB,xml_content XMLTYPE)
  2  / 
 
Table created.
 
SQL> INSERT INTO blob_xml(ID,content,xml_content)
  2  SELECT 1,empty_blob(),XMLROOT(XMLELEMENT("Objects",
  3                  XMLAGG(XMLELEMENT("Object",
  4                        XMLATTRIBUTES(A.OWNER AS "owner",a.object_name AS "name"),
  5                        A.OBJECT_type))),
  6                 VERSION '1.0',
  7                 STANDALONE YES) AS "Result"
  8  FROM   ALL_OBJECTS A
  9  WHERE  owner IN ('SCOTT','SYSTEM')
 10  / 
 
1 row created.
 
SQL> UPDATE blob_xml t SET content=clob2blob(t.xml_content.getClobVal())
  2  / 
 
1 row updated.
 
SQL> COMMIT
  2  / 
 
Commit complete.
 
SQL> PROMPT Query Blob
Query Blob
SQL> SELECT
  2  dbms_lob.getlength(blob2clob(content)) length,
  3  XMLTYPE(blob2clob(content)).extract('/*/Object[@name="CLOB2BLOB"]/text()') object_type
  4  FROM blob_xml
  5  / 
 
LENGTH OBJECT_TYPE
------ ------------------------------
 33525 FUNCTION

Best regards

Maxim

user576711

Posts: 5
Registered: 05/25/07

Re: Extract XML data/element from BLOB column using PLSQL
Posted: Jul 3, 2007 4:12 PM

in response to: user576711

Reply

Hello Maxim - Thanks for the tip, as we had a baby hence the delay in response.

Problem definition was to extract BLOB data without creating additional databasse objects, especially tables. For this reason I used the function you specified with some minor customisation. These modifications were required to avoid consumption of Temp DB space and null error (as I had no provision to first set column to empty_clob()).

Added example for clarification.

CREATE OR REPLACE FUNCTION XBLOB_To_CLOB(L_BLOB BLOB) RETURN CLOB IS
L_CLOB CLOB;
L_SRC_OFFSET NUMBER;
L_DEST_OFFSET NUMBER;
L_BLOB_CSID NUMBER := DBMS_LOB.DEFAULT_CSID;
V_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
L_WARNING NUMBER;
L_AMOUNT NUMBER;
BEGIN
IF DBMS_LOB.GETLENGTH(L_BLOB) > 0 THEN
DBMS_LOB.CREATETEMPORARY(L_CLOB, TRUE);
L_SRC_OFFSET := 1;
L_DEST_OFFSET := 1;
L_AMOUNT := DBMS_LOB.GETLENGTH(L_BLOB);
DBMS_LOB.CONVERTTOCLOB(L_CLOB,
L_BLOB,
L_AMOUNT,
L_SRC_OFFSET,
L_DEST_OFFSET,
1,
V_LANG_CONTEXT,
L_WARNING);
RETURN L_CLOB;
ELSE
L_CLOB:= TO_CLOB('');
RETURN L_CLOB;
End IF;
DBMS_LOB.FREETEMPORARY(L_CLOB);
END;

TABLECUSTOMER
CustID AccountNumber
1234 52341
3456 32146

TABLEBLOBXML (example data row)
ID:=1234
ID_Details:=
<Customer>
<First>Mark</First>
<Last>Taylor</Last>
<Address>
<Street>12 Great West Road</Street>
<Zip>75689</Zip>
<State>TX</State >
</Address>
<Address>
<Street>13 South West Road</Street>
<Zip>65478</Zip>
<State>CA</State >
</Address>
</Customer>

Select c.CustId As CustID, x.ID_Details As CustomerDetails
From TABLECUSTOMER c Left Outer Join TABLEBLOBXML x ON (c.CustId = x.ID)

CustID CustomerDetails

1234 BLOB XML Data
3456 NULL

Query 1:Extract Single Row
SELECT c.CustID As CustID, c.AccountNumber As Account
Decode(x.ID, NULL, 'NA', NVL(XMLTYPE(XBLOB_To_CLOB(x.ID_Details)).extract('/customer/ First/text()').getStringVal(),'NA')) As FirstName,
Decode(x.ID, NULL, 'NA', NVL(XMLTYPE(XBLOB_To_CLOB(x.ID_Details)).extract('/customer/ Last/text()').getStringVal(),'NA')) As LastName
FROM
TABLECUSTOMER c Left Outer Join TABLEBLOBXML x ON (c.CustId = x.ID)

CustID Account FirstName LastName

1234 52341 Mark Taylor
3456 32146 NA NA

Query 2: Extract Multiple Rows
SELECT
Decode(x.ID, NULL, 'NA', x.ID) As xID,
EXTRACTVALUE(Value(p), 'Address/Street/text()') As Street,
EXTRACTVALUE(Value(p), 'Address/Zip/text()') As Zip,
EXTRACTVALUE(Value(p), 'Address/State/text()') As State
FROM
TABLEBLOBXML x,
TABLE(XMLSEQUENCE(
EXTRACT(XMLTYPE(XBLOB_To_CLOB(x.ID_Details)),'/customer/Address')
))p

CustID Street Zip State

1234 12 Great West Road 75689 TX
1234 13 South West Road 65478 CA

Regards
Avi

Maxim Demenko

Posts: 1,392
Registered: 11/05/05

Re: Extract XML data/element from BLOB column using PLSQL
Posted: Jul 3, 2007 4:29 PM

in response to: user576711

Reply

Hello Maxim - Thanks for the tip, as we had a baby
hence the delay in response.

Congratulations, and thank you for feedback.
Just one tip more (i assume, you solved your original problem, correct me if i'm wrong) - you can put your code between the ~~code~~ ~~/code~~ tags, so you increase readability and probably amount of usable responses.

Best regards

Maxim

user576711

Posts: 5
Registered: 05/25/07

Re: Extract XML data/element from BLOB column using PLSQL
Posted: Aug 2, 2007 2:54 PM

in response to: user576711

Reply

I agree. For some reason formatting gets lost after posting message.