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.
- 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:
- Click the SQL/XML Wizard tool to launch the wizard at the Tables and Views Selection page:
- 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.
- 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.
- When you have finished building your hierarchy, click Next to go to the Query tab:
- 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
- [optional] Add code to complete the SQL/XML query:
- Click OK to close the trigger property sheet: