Publishing the Matched Pattern

Examine a query that produces output rows that include the pattern that caused the output to be published.

1. Replace the Query statement in the Queries tab with the following text (or copy the text from PatternMatchingCCL.txt between the line "-- Pattern Matching Code Block 4" and the end of the file):

   /* Match a pattern indicating front running
      and publish the pattern */
   
   INSERT INTO StreamFraudAlerts
   SELECT "Front Running", Broker.OrderId, 
   	Broker.Symbol, XMLPATTERNMATCH()
   FROM StreamPhoneOrders Phone, StreamWebOrders Web, 
   	StreamPlacedOrders Broker, StreamPlacedOrders Cust
   MATCHING [10 SECONDS: Phone || Web, Broker, Cust]
   ON Phone.Broker = Web.Broker = Broker.Customer = Cust.Broker 
   	AND Phone.Symbol = Web.Symbol = Broker.Symbol = Cust.Symbol
   WHERE Phone.OrderID = Cust.OrderID 
   	OR Web.OrderID = Cust.OrderID;
   

   The SELECT clause includes the results of the XMLPATTERNMATCH function, which generates an XML tree containing all of the events that matched the pattern. Note that this function is only valid when used within a Query statement with a MATCHING clause.

2. Locate the schema definition for the output stream StreamFraudAlerts and change the data type of the Customer column from String to XML, since it will now hold XML elements.
3. Run the project, again allowing it to run for about a minute.
4. Examine the FraudAlerts stream viewer:

   
   
   

   The final column, still labeled Customer, contains the XML tree. The tree is a very long string, so it is difficult to see in the stream viewer.

5. Right-click a row in the stream viewer and then click Copy in the shortcut menu.
6. Open a text editor or word processing application and then paste the text into a window:

   Timestamp,Alert,OrderId,Symbol,Customer
   2009/05/06 08:24:35.000000,Front Running,1312,SWY,<PatternMatch>...
   

   The first line is a list of the column names. The second line is the row itself, showing the values in the columns. What follows is an example of the Id column that holds the results of the XMLPATTERNMATCH function, formatted for readability:

   <PatternMatch>
      <StreamPhoneOrders>
         <C8_Timestamp/>
         <OrderId/>
         <Symbol/>
         <Volume/>
         <Broker/>
         <Id/>
      </StreamPhoneOrders>
      <StreamWebOrders>
         <C8_Timestamp>2009-05-06 08:24:30.000000</C8_Timestamp>
         <OrderId>1102</OrderId>
         <Symbol>SWY</Symbol>
         <Volume>100</Volume>
         <Broker>14</Broker>
         <Id>551</Id>
      </StreamWebOrders>
      <StreamPlacedOrders>
         <C8_Timestamp>2009-05-06 08:24:31.000000</C8_Timestamp>
         <OrderId>1312</OrderId>
         <Symbol>SWY</Symbol>
         <Volume>10</Volume>
         <Broker>14</Broker>
         <Id>14</Id>
      </StreamPlacedOrders>
      <StreamPlacedOrders>
         <C8_Timestamp>2009-05-06 08:24:35.000000</C8_Timestamp>
         <OrderId>1102</OrderId>
         <Symbol>SWY</Symbol>
         <Volume>100</Volume>
         <Broker>14</Broker>
         <Id>551</Id>
      </StreamPlacedOrders>
   	</PatternMatch>
   

   This example shows that the fraud alert resulted from a row that arrived on StreamWebOrders.

7. Stop the project.