Generating SQL/XML queries, 249 | Sybase instruction

Chapter 5 Exchanging data with databases supporting XML

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 page 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.

Caution

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

To generate SQL/XML queries:

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

XSM User's Guide

249

Image 261

Sybase manual Generating SQL/XML queries, ™ To generate SQL/XML queries, 249

Contents

11.1 Sybase PowerDesigner Last Revised June Contents 100 Managing external shortcuts through references and data101 102 Working with an XML model 139 How to reverse engineer an XSD, a DTD or an XDR Why reverse engineer an XSD, a DTD or an XDR169 Mapping XML Model objects to OOM objects 171 Vii Exchanging data with databases supporting XML 215 Viii Subject AudienceDocumentation primer Physical Data Model User’s Guide Conceptual Data Model User’s Guide To work with the CDMObject Oriented Model Getting Started Business Process Model User’s Guide Typographic conventions Bibliography BeforeInsertTrigger template Xii XML Model Basics Contents Functional overview Demo models What is an XML model? About XML Objects in an XML model XSD Tool Object Description Objects in a diagram Tool Sequence symbol Choice symbol All symbol Tool Element symbol Group symbol Complex type symbol All Defining the XML model environment Choosing the XML language of an XML modelNot certified resource file Changing the XML language of an XML model ™ To change the XML language of an XML modelLanguage Description Changes concerning simple and complex types Option Description Selecting extended model definitions at model creation Defining model options ™ To define XML model options XML model extended dependencies Defining the XML model environment Defining an XML model Defining model properties™ To define the properties of an XML model Property Description Model General Model Detail Model Items Button Moves itemTool Tooltip Description Model External Schemas Model Namespaces XML Model Basics Model Preview Creating an XML model ™ To create a new XML model using the New model option Creating an XML model using the New model option Defining an XML model List of templates Detaching an XML model from the workspace Opening an existing XML model™ To open an existing XML model ™ To detach an XML model from a workspace Saving an XML Saving and closing an XML modelModel Closing an XML model Defining an XML model Building an XML model Defining an XML diagram XML diagram basics Why build an XML diagram? Creating an XML diagram Group Symbols featureExpand/Expand All/Collapse/Arrange Symbols features Defining elements Defining element properties Global and local elements in XDR files Element general properties Defining elements Property or XDR files Element detail properties Building an XML model Element attributes properties Element constraints properties Element mapping propertiesElement Sources Creating an element Attributes Mapping™ To create an element from the palette Defining elements Tool Action Result How to link a child object to an element? Pointer indications Defining the attributes of an element Building an XML model Global and local attributes in XDR files Attribute general properties Defining attributes Attribute values Attribute detail properties Adding an attribute to an element Element values with XDR™ To add an attribute to an element Modifying element display preferences Linking child elements to a parent elementPreference Description Defining group particles Group particlesGroup particle Value of the order attribute in XDR Items list Group particles properties ™ To create a group particle from an element property sheet Creating a group particle From the palette Name and code uniqueness Clicking an element symbol with the Element tool How to link a child object to a group particle? Defining Any properties Any general properties ™ To create an Any Defining Any Attribute properties Modifying the Any display preference For example Any Attribute general properties Defining a unique constraint Defining identity constraints Unique fields properties Unique general properties Defining a key constraint Defining a keyRef constraint Key general propertiesKey fields properties Defining identity constraints Creating an identity constraint KeyRef general propertiesKeyRef fields properties Defining an identity constraint selector Syntax Description XPath abbreviated syntax ™ To display a selector property sheet Defining selector general properties Defining an identity constraint field ™ To create an identity constraint field Defining field general properties Building an XML model Defining a group of elements Defining groups Defining group properties Groups in DTD and XDR files ™ To create a group of elements from the palette Creating a group of elements Defining groups How to link a child object to a group of elements? Defining a group of attributes Modifying the group display preference Attribute group general properties Defining attribute group properties Attribute group AttributesProperties ™ To create an attribute group from the Browser tree view Creating an attribute group Defining groups Data types References Example of shortcuts through a reference and a data type What is a simple type? Defining simple typesType of derivation Description Modifying the simple type display preference Defining simple type propertiesCreating a simple type ™ To create a simple type from the Browser tree view Building an XML model Defining complex types What is a complex type? Complex type general properties Defining complex type properties Complex type attributes properties Complex type detail properties Complex Type Complex type mapping propertiesSources Icon Tool Description Creating a complex type Modifying complex type display preferences ™ To create a complex type from the palette How to link a child object to a complex type? Pointer indications Simple content Defining simple content properties™ To create a simple content General property Complex content Defining complex content properties™ To create a complex content General properties Deriving by extension Defining derivationsDerivation in element property sheet 102 Defining extension ™ To define a derivation by extension103 Deriving by restriction Restriction on a simple type104 Restriction general Defining restriction facetsRestriction detail 105 Restriction Facet generalEnumerations 106 107 Restriction patterns Creating a restriction on a simple type ™ To create a restriction on a simple type108 109 110 Enumeration and Pattern facets Restriction on a complex type™ To create a restriction on a complex type 111 112 Deriving by list Deriving by union ™ To define a simple type derivation by list113 Union types Union general™ To define a simple type derivation by union 114 115 Defining annotations Defining documentation properties Defining annotation propertiesAnnotation general property Annotation items properties Defining application information properties Documentation contentDocumentation general properties Application information general properties Application information content Creating an annotation118 119 Defining notations Defining notation properties120 Creating a notation ™ To create a notation from the Browser tree view121 Defining entity properties Defining entities122 Entity name Reference Value Creating an entity ™ To create an entity from the Browser tree view123 124 Defining an import Defining import, include and redefineDefining import properties Import general Creating an import ™ To create an import from the Browser tree view126 Defining include properties Defining an includeInclude general 127 Creating an include Defining a redefine™ To create an include from the Browser tree view 128 Defining redefine properties Redefine general129 Redefine items Creating a redefine™ To create a redefine from the Browser tree view 130 131 Defining business rules What is a business rule?Defining business rule properties Business rule general properties Business rule expression property Creating a business rule133 Type Description Example 134 ™ To create a business rule from the List of Business Rules 135 Applying a business rule to an XML object 136 ™ To apply a business rule to an XML object 137 Column in the List of business rules 138 139 Working with an XML model Checking an XML model Levels of problem severityXML model check options 140 XML model object selection in the Check Model Selecting objects in the diagramAutomatic correction 141 Checking an XML model ™ To check an XML model142 143 Selecting all or clearing all check boxes Displaying previously applied check options in an XML model ™ To display errors and warnings selected in the last checkDockable result window 144 Display the Check toolbar Making corrections based on XML model check resultsContextual menu 145 Making automatic corrections to an XML model Making manual corrections to an XML model™ To make manual corrections to an XML model ™ To make automatic corrections to an XML model Group particle check XML Model objects verified by Check ModelExistence of particle Invalid cardinality Identifier uniqueness Model checkUndefined identifier Shortcut code uniqueness Data source check Business rule check in an XSMMissing namespaces Business rule name and code uniqueness Data source name and code uniqueness File checkExistence of model 150 Embedded file name uniqueness Entity checkExistence of external file location Entity name and code uniqueness Include check Simple type checkComplex type check Existence of attribute Element checkElement name and code uniqueness Undefined type 154 Undefined reference Group check Group name and code uniqueness155 Notation check Attribute checkAttribute name and code uniqueness 156 Notation name and code uniqueness Attribute group checkUndefined notation Attribute group name and code uniqueness Import check Undefined schema location and namespaceRedefine check Existence of attributes Existence of component Key checkKey name and code uniqueness Existence of fields KeyRef check KeyRef name and code uniqueness160 Unique check Unique name and code uniqueness161 Extended link check Extended object checkExtended object name and code uniqueness Extended link name and code uniqueness Extension check Replication checkRestriction check Partial object replication Simple type list check Existence of facet164 Annotation check Simple type union checkExistence of items 165 Understanding object mapping Mapping objects in an XML modelDefining data sources in an XML model 166 Creating a data source in an XML model Data source properties in an XML model™ To create a data source in an XML model 167 168 Mapping elements Mapping XML Model objects to PDM objects169 XML Model object PDM object Mapping complex types 170Tool Description Mapping XML Model objects to OOM objects 171XML Model object OOM object Creating a mapping for an XML object ™ To create a mapping for an element172 173 174 Create From Sources and Generate Mapping tools Modifying the mapping of an attribute ™ To modify the mapping of an attribute175 Local objects Manipulating XML objects graphicallyMove 176 Example converting a local object into a global object Global objects™ To convert a local object into a global object Drag and drop 178 After conversions 179 Comparing and merging XML models Generating XML Model objects Generating an XML model from a Physical Data Model180 PDM object Generated object in an XML model XML model naming conventions Generating and updating an XML modelGenerate new XML 181 Defining XML model generation options Update existingXML model Clearing the Preserve modifications check box 183 Check model before generation Generating a new XML model from a PDM ™ To generate a new XML model from a PDM184 Generating an XML model from a package Updating an existing XML model185 Preserve modifications Result ™ To update an existing XML model by generating from a PDM Preserve modifications186 187 Merging models Generating an XML model from an Object- Oriented Model 188OOM Object Generated object in an XML model 189 190 191 Generating a new XML model from an OOM ™ To generate a new XML model from an OOM192 193 194 ™ To update an existing XML model by generating from an OOM 195 Editing an XML model report What is a report?Creating an XML model report ™ To create a model report 197 Inserting diagram Language in a template198 199 200 201 Why generate a DTD file? Why generate an XSD file?Why generate an XDR file? Generating an XSD, a DTD or an XDR file Check model Defining generation parametersDefining generation targets 203 Defining generation options Using the Move up or down arrowsDefining generation tasks 204 Parameter entities during DTD generation How to generate an XSD, a DTD or an XDR file?205 Model targeted with Generated file 206 207 Why reverse engineer an XSD, a DTD or an XDR file? What is reverse engineering?How to reverse engineer an XSD, a DTD or an XDR file? Reverse engineering an XSD, a DTD or an XDR file Reverse engineering target models Reverse engineering optionsReverse engineering to a new XML model Convert Unique References 210 211 212 Reverse engineering to an existing XML model 213 Auto-layout 214 215 Exchanging data with databases supporting XML 216 Why use XML in databases? Generating an annotated schema for Microsoft SQL Server Mapping XML objects to PDM objects217 218 219 220 221 222 Extended model definitions 223 Annotated schema preview 224 225 Reinforcing mapping with extended attributes Annotation Description 226 227 228 229 Generating an annotated schema for Oracle ™ To generate an annotated schema for Oracle230 231 232 233 234 235 236 ™ To generate a DAD file for IBM DB2 Generating a DAD file for IBM DB2237 Type of storage Type of mapping Description 238 Generating a DAD file for IBM DB2 239 240 241 242 243 DAD file preview Extended attribute Description 244 245 246 247 248 Generating SQL/XML queries ™ To generate SQL/XML queries249 250 Generating SQL/XML queries 251 252 253 254 255 Generating from an XML model Target models parameters Generation basics256 Generation option Description 257 Generating from an XML model 258 Why generate an XML model from an XML model? Generating an XML model from an XML model259 260 Generating an XML model from an XML model 261 Generating a new XML model from an XML model 262 ™ To generate a new XML model from an XML model 263 Updating an existing XML model 264 265 266 267 268 XSM User’s Guide 269 Unique UnionXML language XPath 271 Index 272 PowerDesigner 273 IBM DB2 274 275 276 XML 277 278