ORA-19280: XQuery dynamic type mismatch: expected atomic value - got node

Question

I wrote a stored-procedure in Oracle to read data in XML with namespaces.

CREATE OR REPLACE PROCEDURE DynamicXQuery (fileName IN VARCHAR2) 
AS 
    V_TransactionId VARCHAR2(50);

    XPath VARCHAR2(200) := '/message/soap:Envelope/soap:Body/v01:recordTrans';
BEGIN
        SELECT
            X.trans_id
          INTO  
                V_TransactionId 
          FROM  TABLE_WITH_XML_COLUMN T,
                XMLTABLE(
                         XMLNAMESPACES(
                                       'http://schemas.xmlsoap.org/soap/envelope/' as "soap",                            'http://comml.thesoaprequest.com/transactionrecording/v01/' as "v01"
                                      ),
                         '$fullXPath'
                         PASSING T.XML_DOCUMENT, XPath as "fullXPath" 
                         COLUMNS 
                           trans_id VARCHAR2(15) PATH 'transactionIdentifier'
                        ) X
          WHERE T.FILENAME = fileName;

Getting error as 'ORA-19280: XQuery dynamic type mismatch: expected atomic value - got node'

Please advise.

thanks , Bhushan

Answer 1

The short answer is that it is difficult to use anything other than a literal string as the XQuery string in a call to XMLTABLE, and you are trying to do exactly that. You will have to avoid the use of dynamic XQuery expressions somehow.

You cannot achieve what you want by putting the XQuery expression in a variable $fullPath and using that variable in your XQuery string. This does not cause Oracle to evaluate the XQuery expression /message/soap:Envelope/soap:Body/v01:recordTrans on your XML document. Instead, it causes Oracle to evaluate the XQuery expression '/message/soap:Envelope/soap:Body/v01:recordTrans' on your XML document. This is just a literal string. It might be a literal string that happens to contain an XQuery expresssion but it's just a string that evaluates to itself. You can't then look up the value of a child node named transactionIdentifier, because a string is just a value, it doesn't have any child nodes.

If you only need a single value out of the XML document, you could just use EXTRACTVALUE, which has no such problem with variable XPath expressions, for example:

   SELECT EXTRACTVALUE(
            xml_document,
            XPath || '/transactionIdentifier',
            'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v01="http://comml.thesoaprequest.com/transactionrecording/v01/"'
          )
     INTO V_TransactionId
     FROM table_with_xml_column t
    WHERE t.filename = p_fileName;

See also a similar question, which basically states that the answer to your question is ultimately

don't use a variable as XQuery_string [...].