All Apps and Add-ons

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

moshere
Engager

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
Path Finder

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

0 Karma

xavierashe
Contributor

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;
0 Karma

srinathd
Contributor

Any answer for this

0 Karma

peterchenadded
Path Finder

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

E.g

Select to_char(clob_field) from table

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...