All geek questions in one place

Convert oracle blob to xml type

I have experience using MSSQL 2008, and I recently had to migrate from MSSQL to Oracle 10g. The people who designed the table (Oracle), in which there is a column from which I need to extract data, used a column of type BLOB for XML, which they need to store.

In MSSQL, you would just save your XML string as an XML type or use VARCHAR(MAX) . Suppose the table myTable has a column named myColumn , which is VARCHAR(MAX) containing <ROOT><a>111</a></ROOT> . If you want to convert the VARCHAR(MAX) type to an XML type, you simply write something like:

 SELECT CONVERT(XML, myColumn) FROM myTable

if you want, you could use XQuery to get data from the transformed column, for example:

 SELECT CONVERT(XML, myColumn).query('/ROOT/a')

How would you do the same in Oracle 10g if myColumn was a BLOB without having to write a stored procedure, but still making it reusable? Text in the BLOB UFT-8 block.

I would really appreciate your help, as I need it in a hurry.

+6
source share
3 answers
 select XMLType( BLOB_COLUMN, 1 /* this is your character set ID. 1 == USASCII */ ) as XML from my_table;

For more character sets: http://www.mydul.net/charsets.html

+7
source

You can convert from BLOB to CLOB, and then pass the CLOB to the XMLTYPE constructor. Here's the function ...

 -- PL/SQL function to convert a BLOB to an XMLTYPE -- Usage: SELECT blob_to_xmltype(blob_column) FROM table_name; CREATE OR REPLACE FUNCTION blob_to_xmltype (blob_in IN BLOB) RETURN XMLTYPE AS v_clob CLOB; v_varchar VARCHAR2(32767); v_start PLS_INTEGER := 1; v_buffer PLS_INTEGER := 32767; BEGIN DBMS_LOB.CREATETEMPORARY(v_clob, TRUE); FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer) LOOP v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start)); DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar); v_start := v_start + v_buffer; END LOOP; RETURN XMLTYPE(v_clob); END blob_to_xmltype; /

And for your specific example above, you can use the EXTRACT() function:

 SELECT extract(blob_to_xmltype(myColumn), '/ROOT/a') FROM table_name;

The above will return another XMLTYPE. If you want to get the text value of a node, you can use EXTRACTVALUE() .

+2
source

if myColumn was a blob

 SELECT EXTRACT(XMLTYPE.CREATEXML(myColumn), '/ROOT/a') FROM myTable;
Sources

:

+1
source

Source: https://habr.com/ru/post/945043/

More articles:


All Articles