0 Replies Latest reply: Aug 30, 2013 11:25 AM by Jalil-Amin RSS

    Using XML Operation Tags to query by date

    Jalil-Amin

      Problem 1:
      When I use timestamps from items' XML to form queries on repositories, I receive SQL errors.

      Problem 1 Example:
      Query the PublishingRepository to find all active projects created on August 16.

      1. In Dynamo Administration, navigate to /atg/epub/PublishingRepository
      2. In the box labeled "Run XML Operation Tags on the Repository," enter the following tag:
            <query-items item-descriptor="project">status = "Active" AND creationDate = "08/16/2013"</query-items>
      3. Result: java.sql.SQLDataException: ORA-01843: not a valid month


      Problem 1 Solution:

      Use this query instead:

      <query-items item-descriptor="project">
      status = "Active"
      AND
      creationDate = date("2013-08-16")
      </query-items>

      Problem 2:
      Using the "<" operator to construct a less-than argument doesn't work.

      Problem 2 Example:
      Query the PublishingRepository to find all active projects created before August 16.

      1. In Dynamo Administration, navigate to /atg/epub/PublishingRepository
      2. In the box labeled "Run XML Operation Tags on the Repository," enter the following tag:
            <query-items item-descriptor="project">status = "Active" AND creationDate < date("2013-08-16")</query-items>
      3. Result: SOURCE:org.xml.sax.SAXParseException: The content of elements must consist of well-formed character data or markup.


      Problem 2 Solution:
      For some reason, the ">" greater-than operator seems to work fine, even when the "<" less-than operator won't. You can switch around the order of the query to get the desired result:

       

      <query-items item-descriptor="project">
      status = "Active"
      AND
      date("2013-08-16") > creationDate
      </query-items>