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,
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)
select DBMSLOB.substr(a.MyOBJ, 3000 , 1) as MyXML1, DBMSLOB.substr(a.MyOBJ, 3000 , 3000) as MyXML2 from MyTableWithClob a
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;