Chapter 15 Writing SQL Statements in PowerDesigner

SQLXML

Description

Avoids typing a SQL/XML query in the definition of a trigger, a procedure or a function. Use one of the following tools:

• The Insert SQL/XML Macro tool opens a selection dialog box where you choose a global element from an XML model open in the workspace, mapped to a PDM, and linked with the SQL/XML extended model definition. Click OK in the dialog box and the SQLXML macro appears in the definition code, with the code of the XML model (optional) and the code of the global element
• The Macros tool, where you select .SQLXML( ) in the dropdown listbox. The SQLXML macro appears empty in the definition code. You must fill the parentheses with the code of an XML model (optional), followed by :: and the code of a global element. The XML model, from which you choose a global element, must be open in the workspace, mapped to a PDM, and linked with the SQL/XML extended model definition

After generation, the SQLXML macro is replaced by the SQL/XML query of the global element.

Syntax

.SQLXML(code of an XML model::code of a global element)

Note: the code of an XML model is optional.

Example

In a trigger for the table EMPLOYEE, the following macro:

.SQLXML(CorporateMembership::DEPARTMENT)

generates the following trigger script:

select XMLELEMENT( NAME "Department", XMLATTRIBUTES (DEPNUM,DEPNAME),
       (select XMLAGG ( XMLELEMENT( NAME "Employee", XMLATTRIBUTES (DEPNUM,EMPID,FIRSTNAME,LASTNAME)) )
        from EMPLOYEE 
        where DEPNUM = DEPNUM))
from DEPARTMENT

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