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
from User warren – Stack Overflow https://stackoverflow.com/questions/71703393/splunk-message-value-a-string-literal-was-expected/71708029#71708029
via IFTTT