Generating SQL/XML queries

SQL/XML is an XML extension of the Structured Query Language. With SQL/XML, you retrieve relational data using extended SQL syntax, and produce a result using XML. SQL/XML is made of five main functions:

XMLELEMENT: to edit an element with a name, a list of attributes (optional) and a list of values (optional)

XMLATTRIBUTES: to edit a list of attributes with names and values

XMLAGG: to edit in multiple rows a concatenation of elements, from a single XML value corresponding to a single column

XMLCONCAT: to edit in the same row a concatenation of elements, from several XML values corresponding to several columns

XMLFOREST: to edit in the same row a concatenation of elements, from several SQL values corresponding to several columns. The name and value of a column become the name and value of an element

An XML model allows you to generate SQL/XML queries for global elements, whatever the targeted XML language (XSD, DTD or XDR). You need first to map an XML model to a PDM, then to attach the SQL/XML extended model definition to the mapped XML model.

The best way to map an XML model to a PDM is to use the XML Builder Wizard from a PDM. The generated XML model is automatically mapped to the PDM and linked with the SQL/XML extended model definition. If need be, you can still modify the mapping through the Mapping tab of elements and complex types property sheets.

For more information on the XML Builder Wizard, see Generating an XML model via the XML Builder Wizard, in chapter Generating from a PDM, in the PDM User's Guide .

Generated SQL/XML queries cannot be parameterized.

WARNING!

Caution
The following procedure assumes you have an XML model open in the workspace and mapped to a PDM.

To generate SQL/XML queries:

< If the SQL/XML extended model definition is already attached to the XML model, go to step 6 > Select Model→Extended Model Definitions.

The List of Extended Model Definitions is displayed.

Click the Import an Extended Model Definition button.

The Extended Model Definition Selection dialog box is displayed.

In the General Purpose tab, select SQL/XML.

Click OK.

SQL/XML is displayed in the List of Extended Model Definitions.

Click OK.

The SQL/XML extended model definition is displayed in the Browser tree view, attached to the model.

< optional > Double-click a global element in the diagram to display its property sheet.

In the Preview tab, click the SQL/XML.Query tab to display the SQL/XML query.

The Preview tab is in read-only mode, you cannot modify the script.

Click OK.

Select Tools→Extended Generation.

The Generation dialog box is displayed with SQL/XML selected in the Targets tab.

Click the Select a Path button beside the Directory box to select a path for the SQL/XML queries.

In the Selection tab, select the global elements for which you want to generate a SQL/XML query. (Only one global element in the example)

Click OK.

The Result dialog box is displayed with a path for each SQL/XML query. (Only one in the example)

Select the path of a SQL/XML query and click Edit.

The SQL/XML query is displayed in the Editor window. (Extract)

Repeat step 13 to edit another SQL/XML query.

or

Click Close in the Result dialog box.

Once generated, SQL/XML queries are processed by SQL interpreters in databases supporting XML.