Default Mapping of XSD Elements and Attributes to Tables and Columns [SQLXML 4.0]

Article
12/03/2008

By default, an element of complex type in an XSD annotated schema maps to the table (view) with the same name in the specified database, and an element or attribute of simple type maps to the column with the same name in the table.

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 default mapping

In this example, no annotations are specified in the XSD schema. The <Person.Contact> element is of complex type and, therefore, maps by default to the Person.Contact table in the AdventureWorks database. All the attributes (ContactID, FirstName, LastName) of the <Person.Contact> element are of simple type and map by default to columns with the same names in the Person.Contact table.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Person.Contact" >
     <xsd:complexType>
       <xsd:attribute name="ContactID"  type="xsd:string" /> 
       <xsd:attribute name="FirstName"   type="xsd:string" /> 
       <xsd:attribute name="LastName"    type="xsd:string" /> 
     </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.xml.
Copy the following template and paste it into a text file. Save the file as MySchemaT.xml in the same directory where you saved MySchema.xml.
```
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:xpath-query mapping-schema="MySchema.xml">
        /Person.Contact
    </sql:xpath-query>
</ROOT>
```
The directory path specified for the mapping schema (MySchema.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.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 4.0 Queries.

Here is the partial result set:

<?xml version="1.0" encoding="UTF-8" ?>
<ROOT>
  <Person.Contact ContactID="1" FirstName="Gustavo" LastName="Achong"/>
  <Person.Contact ContactID="2" FirstName="Catherine" LastName="Abel"/>
   ...
</ROOT>

B. Mapping an XML element to a database column

In this example, default mapping also takes place because no annotations are used. The <Person.Contact> element is of complex type and maps to the table with the same name in the database. The elements <FirstName> and <LastName> and the EmployeeID attribute are of simple type and, therefore, map to the columns with the same names. The only difference between this and the previous example are that elements are used for mapping the FirstName and LastName fields.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Person.Contact">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="FirstName" type="xsd:string" /> 
        <xsd:element name="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 MySchemaElements.xml.
Create the following template (MySchemaElementsT.xml), and save it in the same directory used in the previous step.
```
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:xpath-query mapping-schema="MySchemaElements.xml">
        /Person.Contact
    </sql:xpath-query>
</ROOT>
```
The directory path specified for the mapping schema is relative to the directory where the template is saved. An absolute path also can be specified, for example:
```
mapping-schema="C:\SqlXmlTest\MySchemaElements.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 4.0 Queries.

Here is the partial result set:

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

C. Mapping an XML element to an XML data type column

In this example, default mapping also takes place because no annotations are used. The <Production.ProductModel> element is of complex type and maps to the table with the same name in the database. The ProductModelID attribute is of simple type and, therefore, map to the columns with the same names. The only difference between this and the previous examples is that the <Instructions> element is mapping to a column that uses the xml data type by using the xsd:anyType type.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Production.ProductModel">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Instructions" type="xsd:anyType" /> 
      </xsd:sequence>
      <xsd:attribute name="ProductModelID" type="xsd:integer" /> 
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

The xml data type is new to SQL Server 2005.

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 MySchemaXmlAnyElements.xml.
Create the following template (MySchemaXmlAnyElementsT.xml), and save it in the same directory used in the previous step.
```
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
    <sql:xpath-query mapping-schema="MySchemaXmlAnyElements.xml">
        /Production.ProductModel[@ProductModelID=7]
    </sql:xpath-query>
</ROOT>
```
The directory path specified for the mapping schema is relative to the directory where the template is saved. An absolute path also can be specified, for example:
```
mapping-schema="C:\SqlXmlTest\MySchemaXmlAnyElements.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 4.0 Queries.

Here is the partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Production.ProductModel ProductModelID="7">
    <Instructions>
      <root xmlns="http:
//schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstru
ctions">
...
      </root>
    <Instructions>
  </Production.ProductModel>
</ROOT>

Default Mapping of XSD Elements and Attributes to Tables and Columns [SQLXML 4.0]

Examples

A. Specifying default mapping

B. Mapping an XML element to a database column

C. Mapping an XML element to an XML data type column

See Also

Reference

Other Resources

Help and Information

Additional resources