1 Reply Latest reply: Sep 10, 2012 9:25 PM by mdrake RSS

    Using the variable placeholder ? in an XPath expression

    959086
      I'm trying to using the variable operator (?) in an Oracle prepared statement used in a query where clause. Here is the query:
      select xt.APPLICATION_NAME, xt.VERSION, xt.EVENT_TYPE, xt.SENDING_SITE, xt.RECEIVING_SITE, xt.EVENT_ID 
            from AUDITED_EVENT_XML_MIN e,
               XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                      '/xae:auditable-event'
                      PASSING e.xml_event_content
                      COLUMNS
                      APPLICATION_NAME VARCHAR2(255) PATH 'xae:application-name',
                      VERSION          VARCHAR2(255) PATH 'xae:version',
                      EVENT_TYPE       VARCHAR2(255) PATH 'xae:event-type',
                      SENDING_SITE     VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.4/HD.2',
                      RECEIVING_SITE   VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.6/HD.2',
                      EVENT_ID         VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.10') xt
                  where existsNode(E.XML_EVENT_CONTENT, '/xae:auditable-event[xae:event-type=?]','xmlns:xae="http://gov/va/med/datasharing/audit/endpoint/audit"') = 1
      This code works when the ? is replaced with "aValue". I need to use the ? so that my Java client can pass a variable value into the query.
      Is this a supportable feature or am I doing something wrong?
      I didn't see any examples in the Oracle XML DB Developers Guide where there was a ? in an XPath expression.
      I also tried "?" with the same issue...

      Here is the version info:
      SQL*Plus: Release 11.1.0.6.0 - Production on Mon Sep 10 18:41:55 2012
      
      Copyright (c) 1982, 2007, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      Any assistance would be greatly appreciated.
      Thanks in advance...

      Edited by: flyeagle5683 on Sep 10, 2012 5:49 PM
        • 1. Re: Using the variable placeholder ? in an XPath expression
          mdrake
          select xt.APPLICATION_NAME, xt.VERSION, xt.EVENT_TYPE, xt.SENDING_SITE, xt.RECEIVING_SITE, xt.EVENT_ID 
                from AUDITED_EVENT_XML_MIN e,
                   XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                          '/xae:auditable-event'
                          PASSING e.xml_event_content
                          COLUMNS
                          APPLICATION_NAME VARCHAR2(255) PATH 'xae:application-name',
                          VERSION          VARCHAR2(255) PATH 'xae:version',
                          EVENT_TYPE       VARCHAR2(255) PATH 'xae:event-type',
                          SENDING_SITE     VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.4/HD.2',
                          RECEIVING_SITE   VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.6/HD.2',
                          EVENT_ID         VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.10') xt
                      where XMLEXISTS(
                                 'declarre namespace xae ="http://gov/va/med/datasharing/audit/endpoint/audit"; (: :)
                                  $XML/xae:auditable-event[xae:event-type=$VALUE]' 
                                  passing e.xml_event_content as "XML",
                                            :1 as "VALUE"
                      ) 
          or you can do
          select xt.APPLICATION_NAME, xt.VERSION, xt.EVENT_TYPE, xt.SENDING_SITE, xt.RECEIVING_SITE, xt.EVENT_ID 
                from AUDITED_EVENT_XML_MIN e,
                   XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                          '/xae:auditable-event'
                          PASSING e.xml_event_content
                          COLUMNS
                          APPLICATION_NAME VARCHAR2(255) PATH 'xae:application-name',
                          VERSION          VARCHAR2(255) PATH 'xae:version',
                          EVENT_TYPE       VARCHAR2(255) PATH 'xae:event-type',
                          SENDING_SITE     VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.4/HD.2',
                          RECEIVING_SITE   VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.6/HD.2',
                          EVENT_ID         VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.10') xt
                WHERE EVENT_TYPE = :1
          Edited by: mdrake on Sep 10, 2012 7:23 PM

          Edited by: mdrake on Sep 10, 2012 7:24 PM