Chapter 6 Building Triggers and Procedures

Creating SQL/XML Queries with the Wizard

You can use the SQL/XML Wizard to insert a SQL/XML query in the definition of a trigger, stored procedure, or function to store or retrieve data, in an XML format, from relational databases supporting SQL/XML. The wizard, allows you to select tables and views from a PDM to build a mapped XML model. This XML model (which does not appear in the workspace) is used to generate SQL/XML queries from global elements.

To insert a SQL/XML query in the definition of a trigger:

1. Open the trigger property sheet, click the Definition tab and position the cursor in the trigger definition where you want to insert the SQL/XML query:
   
2. Click the SQL/XML Wizard tool to launch the wizard at the Tables and Views Selection page:
   
3. Select the tables and views that you want to include in your query and click Next to go to the XML Hierarchy Design page:
   
   
   On this tab, you construct the XML hierarchy that you want to generate:
   
   
   • The left-hand pane lists the tables and views that you have selected
   • The right-hand pane displays the XML hierarchy to be generated, containing a default root element.
4. You can build your XML hierarchy using the following techniques:
   
   
   • Specify whether columns will be generated as elements or attributes by using the radio buttons above the panes.
   • Drag and drop a table, view, or column onto a node in the XML hierarchy. You must respect the PDM hierarchy: You cannot create an XML hierarchy between two elements if there is no reference between their corresponding tables, and a parent table cannot be placed beneath one of its children.
   • Right-click a table, view, or column and select Add from the contextual menu to add it to the last selected node in the XML hierarchy.
   • Rename an element or attribute by clicking its node and typing a new name.
   • Create new elements and attributes not in the PDM, and Sequence, Choice and All group particles, by right-clicking an XML node and selecting New→object from the contextual menu.
   • Delete an XML node by right-clicking it and selecting Delete from the contextual menu.
5. When you have finished building your hierarchy, click Next to go to the Query tab:
   
6. Review your query and click Back, if necessary, to make revisions in your hierarchy. When you are satisfied, click Finish to close the wizard and insert the SQL/XML query in the trigger definition
   
7. [optional] Add code to complete the SQL/XML query:
   
8. Click OK to close the trigger property sheet:

Copyright (C) 2008. Sybase Inc. All rights reserved.