This discussion is archived
5 Replies Latest reply: Nov 27, 2012 10:43 AM by odie_63 RSS

Using the variable ? operator in an XPath Expression

959086 Newbie
Currently Being Moderated
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...
  • 1. Re: Using the variable ? operator in an XPath Expression
    AlexAnd Guru
    Currently Being Moderated
    you can try to use bind variable
    (Oracle® XML DB Developer's Guide 10g Release 2 (10.2)
    -- 6 XPath Rewrite
    -- Which XPath Expressions Are Rewritten?
    -- Table 6-1 Sample List of XPath Expressions for Translation to Underlying SQL constructs
    -- String bind variables inside predicates
    -- http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb_rewrite.htm#sthref878)
    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
  • 2. Re: Using the variable ? operator in an XPath Expression
    959086 Newbie
    Currently Being Moderated
    Thank you for your assistance. This worked very well...

    Now I would like to take it to the next level. I would like to specify this SQL statically, then add the XPath expression in the where clause dynamically in my Java client.

    Here is the static SQL:
    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,' || ? || ','xmlns:xae="http://gov/va/med/datasharing/audit/endpoint/audit"') = 1
    I want to compute the XPath expression is Java and pass it as a variable into the prepared statement.
    When I try this, I get a SQLException: Invalid column index. I usually get this error when the SQL is not coded correctly to accept a parameter.

    Is there a way to write the SQL so that this will work?
    How do you recommend implementing a DAO that can support a combination of different values in the where clause that must evaluate if a value is in a set of known values?

    Thanks in advance...
  • 3. Re: Using the variable ? operator in an XPath Expression
    AlexAnd Guru
    Currently Being Moderated
    did you try
    where existsNode(E.XML_EVENT_CONTENT, ? ,'xmlns:xae="http://gov/va/med/datasharing/audit/endpoint/audit"') = 1
    ?
  • 4. Re: Using the variable ? operator in an XPath Expression
    959086 Newbie
    Currently Being Moderated
    I incorporated this suggestion and it worked very well. Thanks!

    Now I have converted my solution to use a structured index. As such, I want to write the where clauses in terms of column names rather than XPath expressions.
    I hoped it was as simple as changing the code that generates the XPath expression to generate a SQL expression. After making that change, I'm now getting this error:
    Caused by: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator
    
         at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
         at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
         at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
         at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:686)
         at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:617)
         at oracle.jdbc.driver.T2CPreparedStatement.executeForDescribe(T2CPreparedStatement.java:559)
         at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1077)
         at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
         at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
         at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)
         at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1490)
         at gov.va.med.datasharing.audit.dao.jdbc.runner.MapQueryRunner.run(MapQueryRunner.java:47)
         at gov.va.med.datasharing.audit.web.dao.jdbc.AuditableEventsQueryDAOImpl.getAuditableEventsByDate(AuditableEventsQueryDAOImpl.java:150)
    It seemed to me that if it worked for the XPath expression that it should also work for the SQL expression.
    The static SQL is:
    SELECT PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID FROM
      (SELECT rownum as rn, PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID FROM
         (SELECT aet.AUDITABLE_EVENT_XML_PK_ID AS PK_ID, xt.EVENT_TYPE, xt.OUTCOME, xt.SENDING_SITE, xt.RECEIVING_SITE, aet.CREATED_TIME as CREATED_TIME, xt.EVENT_ID, xt.PATIENT_ID
            FROM AUDITABLE_EVENT_XML aet,
              XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                '/xae:auditable-event'
                PASSING aet.xml_event_content
                COLUMNS
                EVENT_TYPE       VARCHAR2(255) PATH 'xae:event-type',
                SENDING_SITE     VARCHAR2(255) PATH 'xae:sending-site',
                RECEIVING_SITE   VARCHAR2(255) PATH 'xae:receiving-site',
                EVENT_ID         VARCHAR2(255) PATH 'xae:event-id',
                PATIENT_ID           VARCHAR2(255) PATH 'xae:patient-id',
                OUTCOME                VARCHAR2(255) PATH 'xae:outcome') xt
            WHERE aet.CREATED_TIME BETWEEN ? AND ?
                 AND ?
          ) ORDER BY CREATED_TIME desc
        ) WHERE rn > ? and rn <= ?
    So I'm thinking that Oracle must perform strong checking in SQL, not allowing the '=' operator to be substituted. If I run this query in Toad, replacing the ? with the SQL, it runs fine...

    Can someone tell me whether or not this is a legal substitution?

    The SQL that runs in Toad is:
    SELECT PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID FROM
      (SELECT rownum as rn, PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID FROM
         (SELECT aet.AUDITABLE_EVENT_XML_PK_ID AS PK_ID, xt.EVENT_TYPE, xt.OUTCOME, xt.SENDING_SITE, xt.RECEIVING_SITE, aet.CREATED_TIME as CREATED_TIME, xt.EVENT_ID, xt.PATIENT_ID
            FROM AUDITABLE_EVENT_XML aet,
              XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                '/xae:auditable-event'
                PASSING aet.xml_event_content
                COLUMNS
                EVENT_TYPE       VARCHAR2(255) PATH 'xae:event-type',
                SENDING_SITE     VARCHAR2(255) PATH 'xae:sending-site',
                RECEIVING_SITE   VARCHAR2(255) PATH 'xae:receiving-site',
                EVENT_ID         VARCHAR2(255) PATH 'xae:event-id',
                PATIENT_ID         VARCHAR2(255) PATH 'xae:patient-id',
                OUTCOME             VARCHAR2(255) PATH 'xae:outcome') xt
            WHERE aet.CREATED_TIME BETWEEN TO_DATE ('9/19/2011 12:00:01 AM','MM/DD/YYYY HH:MI:SS PM')
                                         AND TO_DATE ('12/20/2012 12:00:00 AM','MM/DD/YYYY HH:MI:SS PM')
                AND (OUTCOME=0 or OUTCOME=1)
          ) ORDER BY CREATED_TIME desc
        ) WHERE rn > 0 and rn <= 5;
    Any assistance will be appreciated,
    Thanks in advance.
  • 5. Re: Using the variable ? operator in an XPath Expression
    odie_63 Guru
    Currently Being Moderated
    Can someone tell me whether or not this is a legal substitution?
    No, that's not legal.

    ? defines a placeholder for a bind variable, that's all it can do, binds a variable (value) at execution time, it doesn't handle identifiers and least of all movable pieces of code.

    You'll have to use concatenation to add the code fragment :
    " ... AND " + myFilter + " ... remaining for the query string"
    BTW, I see you're trying to build a pagination query, but the ORDER BY is misplaced, it must be in the innermost subquery, not at the same level as ROWNUM :
    SELECT PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID 
    FROM (
      SELECT rownum as rn, PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID 
      FROM (
        SELECT aet.AUDITABLE_EVENT_XML_PK_ID AS PK_ID, xt.EVENT_TYPE, xt.OUTCOME, xt.SENDING_SITE, xt.RECEIVING_SITE, aet.CREATED_TIME as CREATED_TIME, xt.EVENT_ID, xt.PATIENT_ID
        FROM AUDITABLE_EVENT_XML aet,
             XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
               '/xae:auditable-event'
               PASSING aet.xml_event_content
               COLUMNS
                 EVENT_TYPE       VARCHAR2(255) PATH 'xae:event-type',
                 SENDING_SITE     VARCHAR2(255) PATH 'xae:sending-site',
                 RECEIVING_SITE   VARCHAR2(255) PATH 'xae:receiving-site',
                 EVENT_ID         VARCHAR2(255) PATH 'xae:event-id',
                 PATIENT_ID       VARCHAR2(255) PATH 'xae:patient-id',
                 OUTCOME          VARCHAR2(255) PATH 'xae:outcome'
             ) xt
        WHERE aet.CREATED_TIME BETWEEN ? AND ?
        AND (OUTCOME=0 or OUTCOME=1)
        ORDER BY CREATED_TIME desc
      )
      WHERE rownum <= ? 
    ) 
    WHERE rn > ?
    or using ROW_NUMBER() :
    SELECT PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID 
    FROM (
      SELECT aet.AUDITABLE_EVENT_XML_PK_ID AS PK_ID, xt.EVENT_TYPE, xt.OUTCOME, xt.SENDING_SITE, xt.RECEIVING_SITE, aet.CREATED_TIME as CREATED_TIME, xt.EVENT_ID, xt.PATIENT_ID
           , ROW_NUMBER() OVER(ORDER BY created_time DESC) rn
      FROM AUDITABLE_EVENT_XML aet,
           XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
             '/xae:auditable-event'
             PASSING aet.xml_event_content
             COLUMNS
               EVENT_TYPE       VARCHAR2(255) PATH 'xae:event-type',
               SENDING_SITE     VARCHAR2(255) PATH 'xae:sending-site',
               RECEIVING_SITE   VARCHAR2(255) PATH 'xae:receiving-site',
               EVENT_ID         VARCHAR2(255) PATH 'xae:event-id',
               PATIENT_ID       VARCHAR2(255) PATH 'xae:patient-id',
               OUTCOME          VARCHAR2(255) PATH 'xae:outcome'
           ) xt
      WHERE aet.CREATED_TIME BETWEEN ? AND ?
      AND (OUTCOME=0 or OUTCOME=1)
    )
    WHERE rn <= ?
    AND rn > ? 

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points