Search code examples
sqlxmloracleoracle11g

SQL Oracle how to extract xml tag content in a blob column

There is an xml that is in a column of type BLOB (in oracle) and I need to access a certain tag from that xml. Until then I can retrieve the column this way:

SELECT TRIM(UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(my_column_blob, 1024))) as tag_name_xml
FROM my_table

the return is like this: enter image description here

Here I leave part of the xml content: enter image description here

How do I return the value/content of the cUF or cCT tag? I would like the help of people with more knowledge, please.

the proposed solutions are very elegant, but for my case what would it be like to access with this xml structure to access the contents of the cUF tag?

<?xml version="1.0" encoding="UTF-8"?>
<cteProc xmlns="http://www.example.com" version="3.00">
   <CTe>
      <infCte version="3.00">
         <ide>
            <cUF>15</cUF>
         </ide>
      </infCte>
   </CTe>
</cteProc>
Solution

  • Use XMLTYPE and XMLTABLE:

    SELECT x.c
FROM   table_name t
       CROSS JOIN XMLTABLE(
         '/a/b/c'
         PASSING XMLTYPE(t.value, 1)
         COLUMNS
           c VARCHAR2(200) PATH './text()'
       ) x;

    Or XMLQUERY:

    SELECT XMLQUERY('/a/b/c/text()' PASSING XMLTYPE(value, 1) RETURNING CONTENT) AS c
FROM   table_name

    Which, for the sample data:

    CREATE TABLE table_name (value BLOB);

INSERT INTO table_name (value)
VALUES ( UTL_RAW.CAST_TO_RAW( '<a><b><c>something</c></b></a>' ) );

    Both output:

    C
    something

    db<>fiddle here