Generating an Annotated Schema for Microsoft SQL Server

Microsoft SQL Server is an XML-enabled database server, which supports annotations that can be used on XSD or XDR files, to map XML data to relational data.

1. Map an XSM to a PDM. You can do this manually or by generating an XSM from a PDM (or a PDM from an XSM).
2. To enable the Microsoft SQL Server extensions in your model, select Model > Extensions, click the Attach an Extension tool, select the Microsoft SQL Server file (on the XML in Database tab), and click OK to attach it.
3. [optional] Reinforce the mappings of elements and attributes to tables and columns with extended attributes:
   Note: If the element and attribute names match the table and column names, you do not need to define extended attributes for XML objects.

   Annotation	Description
   encode	When an XML element or attribute is mapped to a SQL Server BLOB column, allows requesting a reference (URI) to be returned and used later to return BLOB data. Available for: Element, Attribute
   field	Maps an XML item to a database column. Available for: Element, Attribute
   hide	Hides the element or attribute specified in the schema in the resulting XML document. Available for: Element, Attribute
   is-constant	Creates an XML element that does not map to any table. The element is displayed in the query output. Available for: Element
   key-fields	Allows specification of columns that uniquely identify the rows in a table. Available for: Element
   limit-field	Allows limiting the values that are returned on the basis of a limiting value. Available for: Element, Attribute
   limit-value	Allows limiting the values that are returned on the basis of a limiting value. Available for: Element, Attribute
   mapped	Allows schema items to be excluded from the result. Available for: Element, Attribute
   max-depth	Allows you to specify depth in recursive relationships that are specified in the schema. Available for: Element
   overflow-field	Identifies the database column that contains the overflow data. Available for: Element
   relation	Maps an XML item to a database table. Available for: Element
   relationship-child	Specifies an element as the child table in a reference (To define only in the child element property sheet). Available for: Element
   relationship-child-key	Specifies an attribute as the foreign key of a child table in a reference (To define only in the child element property sheet). Available for: Element
   relationship-parent	Specifies an element as the parent table in a reference (To define only in the child element property sheet). Available for: Element
   relationship-parent-key	Specifies an attribute as the primary key of a parent table in a reference (To define only in the child element property sheet). Available for: Element
   use-cdata	Allows specifying CDATA sections to be used for certain elements in the XML document. Available for: Element
   prefix	Creates valid XML ID, IDREF, and IDREFS. Prepends the values of ID, IDREF, and IDREFS with a string. Available for: Attribute

4. [optional] Click the Preview tab of the model property sheet, to preview the annotated schema.
5. Select Language > Generate schemaFile to open the Generation dialog box.
6. Specify the directory in which to generate the file and select the XML in Database target on the Targets tab.
   
   
   
7. Click OK to begin the generation.

   The Result dialog box is displayed with the path of the annotated schema file selected.

8. Click Edit to open the generated annotated schema in your associated editor:
   
   
   

   Note the SQL namespace (with the sql prefix) and the SQL annotations for tables (sql:relation), columns (sql:field) and reference (sql:relationship).