Show TOC

Creating SQL/XML Queries with the WizardLocate this document in the navigation structure

You can use the SQL/XML Wizard to insert a SQL/XML query in the definition of the body 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.

Procedure

  1. Open the trigger property sheet, click the Body tab and position the cursor 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 Start of the navigation path New Next navigation step <object> End of the navigation path 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 code.


  7. [optional] Add code to complete the SQL/XML query:


  8. Click OK to close the trigger property sheet: