Custom XSD mappings to tables/columns (SQLXML)

Article
03/03/2023

Applies to: SQL Server Azure SQL Database

When using an XSD schema to provide an XML view of the relational database , the elements and attributes of the schema must be mapped to tables and columns of the database. The rows in the database table/view will map to elements in the XML document. The column values in the database map to attributes or elements.

When XPath queries are specified against the annotated XSD schema, the data for the elements and attributes in the schema is retrieved from the tables and columns to which they map. To obtain a single value from the database, the mapping specified in the XSD schema must have both relation and field specification. If the name of an element/attribute is not the same name as the table/view or column name to which it maps, the sql:relation and sql:field annotations are used to specify the mapping between an element or attribute in an XML document and the table (view) or column in a database.

sql-relation

The sql:relation annotation is added to map an XML node in the XSD schema to a database table. The name of a table (view) is specified as the value of the sql:relation annotation.

When sql:relation is specified on an element, the scope of this annotation applies to all attributes and child elements that are described in the complex type definition of that element, therefore providing a shortcut in writing annotations.

The sql:relation annotation is also useful when identifiers that are valid in Microsoft SQL Server are not valid in XML. For example, "Order Details" is a valid table name in SQL Server but not in XML. In such cases, the sql:relation annotation can be used to specify the mapping, for example:

<xsd:element name="OD" sql:relation="[Order Details]">

sql-field

The sql-field annotation maps an element or attribute to a database column. The sql:field annotation is added to map an XML node in the schema to a database column. You cannot specify sql:field on an empty content element.

Examples

To create working samples using the following examples, you must meet certain requirements. For more information, see Requirements for Running SQLXML Examples.

A. Specifying the sql:relation and sql:field annotations

In this example, the XSD schema consists of an <Contact> element of complex type with <FName> and <LName> child elements and the ContactID attribute.

The sql:relation annotation maps the <Contact> element to the Person.Contact table in the AdventureWorks database. The sql:field annotation maps the <FName> element to the FirstName column and the <LName> element to the LastName column.

No annotation is specified for the ContactID attribute. This results in a default mapping of the attribute to the column with the same name.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:element name="Contact" sql:relation="Person.Contact" >  
   <xsd:complexType>  
     <xsd:sequence>  
        <xsd:element name="FName"  
                     sql:field="FirstName"   
                     type="xsd:string" />   
        <xsd:element name="LName"    
                     sql:field="LastName"    
                     type="xsd:string" />  
     </xsd:sequence>  
        <xsd:attribute name="ContactID"   
                       type="xsd:integer" />  
    </xsd:complexType>  
  </xsd:element>  
</xsd:schema>

To test a sample XPath query against the schema

Copy the schema code above and paste it into a text file. Save the file as MySchema-annotated.xml.
Copy the following template below and paste it into a text file. Save the file as MySchema-annotatedT.xml in the same directory where you saved MySchema-annotated.xml.
```
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">  
  <sql:xpath-query mapping-schema="MySchema-annotated.xml">  
    /Contact  
  </sql:xpath-query>  
</ROOT>  
```
The directory path specified for the mapping schema (MySchema-annotated.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:
```
mapping-schema="C:\SqlXmlTest\MySchema-annotated.xml"  
```
Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.

For more information, see Using ADO to Execute SQLXML Queries.

Here is the partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">   
 <Contact ContactID="1">   
    <FName>Gustavo</FName>   
    <LName>Achong</LName>   
 </Contact>   
  .....  
</ROOT>

Share via