SPLUNK : .Message.value()]: A string literal was expected

Question

I want to execute this code in Splunk using "| dbxquery":

(select top 1 a.Message.value ('(//Transaction[@transactionID=sql:column("t.Txn_Ref_Num")]/LifeSupportNotification/LifeSupportData/Status)[1]', 'varchar(50)')

but gets error: "Message_Archive.Message.value()]: A string literal was expected"

Been looking for solution for a while now.

Edit (here's the code)

    | dbxquery query="select
    'Elec' as Fuel,
    t.txn_ref_num as 'Transaction_Id',
    t.Transaction_Id as T_ID,

t.XML_transaction_Type as 'Transaction_Type',
t.cust_ref_num as 'NMI',
(select top 1 a.Message.value('(//Transaction [ @transactionID=sql:column("t.Txn_Ref_Num")]/LifeSupportNotification/LifeSupportData/Status)[1]', 'varchar(50)')  
    from MDHElec.dbo.MDH_Message_Archive a with (nolock) , MDHElec.dbo.ArchivePath p with (nolock)
where a.Message_Archive_Id=p.Message_Archive_Id and p.TransactionID=t.Txn_Ref_Num and a.Flow='REC') as 'LifeSupportStatus',
t.date_created as 'Timestamp',
mt.master_transaction_status as Status,
'Outbound' as 'Direction',
case
when mt.Master_Transaction_Status = 'PACN' then 'NULL'
when mte.Exception_Id is not null and t.XML_Transaction_Type not in ('TransactionAcknowledgement') and t.Transaction_Id = mte.Transaction_Id then 'Reject'
else 'Accept' end as 'TackStatus'
,mt.Date_Last_Updated
from MDHElec.dbo.transactions t with (nolock)
join MDHElec.dbo.Master_Transaction mt with (nolock) on mt.Master_Txn_Id = t.Master_Txn_Id
left join MDHElec.dbo.Master_Txn_Exception mte with (nolock) on mt.Master_Txn_Id = mte.Master_Txn_Id
where t.xml_transaction_type = 'LifeSupportNotification'
and t.Source_System = 'KRK'
and t.date_created >= dateadd(day,datediff(day,1,GETDATE()),0)
order by t.Date_Created desc" connection="MDH_PRD"

|table *

Answer 1

Per the docs, the fact you have multiple types of quote marks is most likely causing the error you're seeing.

Your excised query:

(select top 1 a.Message.value ('(//Transaction[@transactionID=sql:column("t.Txn_Ref_Num")]/LifeSupportNotification/LifeSupportData/Status)[1]', 'varchar(50)') 

Note you have both single (') and double (") quotes in the string.

Per the dbxquery page on Docs.Splunk, the query you send is supposed to be encased in double quotes:

query
Syntax: query=<string> Description: A SQL query. You can also use a URL-encoded SQL query, but you must percent-encode all spaces (%20).

And one of the examples shown:

dbxquery query="select * from actor where actor_id > ? and actor_name = ?" connection="mysql" params="3,BOB"

The two other examples shown:

| dbxquery query="SELECT actor_id, first_name as fname, last_name as lname, last_update FROM actor" connection="mySQL"

| dbxquery query="SELECT actor_id, first_name as fname, last_name as lname, last_update FROM actor" connection="mySQL" maxrows=100