Splunk DB Connect 2: How to fetch and read XML data from BLOB and CLOB fields in MS SQL and Oracle database tables?

[moshere]

Hi,

We're using Splunk DB Connect 2 on top of our DB (MS SQL server and Oracle DB). In most of our tables, we've BLOB and CLOB fields that contain XML.
I would like to know how we can fetch and read the actual data from these fields?

Thank you in advance,
Moshe

[drodman29]

So far this is pretty ugly, but only substrings are working for me to extract from a clob.
My system does not understand the XMLType data type. (DBX 3, Java 8 and Oracle JDBC driver for 18C)
This runs...
select DBMS_LOB.substr(a.My_OBJ, 3000 , 1) as MyXML1, DBMS_LOB.substr(a.My_OBJ, 3000 , 3000) as MyXML2 from MyTableWithClob a

[xavierashe]

The answer to this question is more about SQL than Splunk. Here is what I do in Oracle, but you'll want to ask your DBA (I'm not a DBA by far).

SELECT x.object_value.getCLOBVal() FROM XML_TABLE x;

Googling around, here's another way to skin that cat.

select
XMLType( BLOB_COLUMN,
         1 /* this is your character set ID.
                   1 == USASCII */
       ) as XML
from my_table;

[srinathd]

Any answer for this

[peterchenadded]

You need to use SQL to convert it to a char field

E.g

Select to_char(clob_field) from table