Specifying the XMLDATA Schema Option in a Query

XML and Internet Support

XML and Internet Support

Specifying the XMLDATA Schema Option in a Query

The primary purpose for specifying XMLDATA in a query is to receive XML data type information that can be used where data types are necessary (for example, in handling numeric expressions). Otherwise, everything in an XML document is a textual string. Generating an XML-Data schema is an overhead on the server, is likely to affect performance, and should be used only when data types are needed.

If the database column from which values are retrieved is of type sql_variant, there is no data type information in the XML-Data schema. If a given query designates different XML elements with same name, XMLDATA may produce an invalid XML-Data schema. This is because element name collisions and data type names are not resolved (you might have two elements with same name but different data types).

Example
A. Specify the XMLDATA schema option

This query specifies the XMLDATA schema option. The query returns customer and order information.

SELECT Customers.CustomerID, ContactName,
       Orders.OrderID, OrderDate, Orders.CustomerID,
       ProductID, Quantity
FROM Customers, Orders, [Order Details]
WHERE Customers.CustomerID = Orders.CustomerID
AND Orders.OrderID = [Order Details].OrderID
ORDER BY Customers.CustomerID, Orders.OrderID
FOR XML AUTO, XMLDATA

This is the partial result. The XML-Data schema is generated and prepended to the result. The table name [Order Details] is an invalid XML name because of the space in the table name. This invalid character is converted into escaped numeric encoding.

<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="Customers" content="eltOnly" model="closed">
<element type="Orders"/>
  <AttributeType name="CustomerID" dt:type="string"/>
  <AttributeType name="ContactName" dt:type="string"/>
  <attribute type="CustomerID"/>
  <attribute type="ContactName"/>
</ElementType>
<ElementType name="Orders" content="eltOnly" model="closed">
<element type="Order_0020_Details"/>
  <AttributeType name="OrderID" dt:type="i4"/>
  <AttributeType name="OrderDate" dt:type="dateTime"/>
  <AttributeType name="CustomerID" dt:type="string"/>
  <attribute type="OrderID"/>
  <attribute type="OrderDate"/>
  <attribute type="CustomerID"/>
</ElementType>
<ElementType name="Order_0020_Details" content="empty" model="closed">
  <AttributeType name="ProductID" dt:type="i4"/>
  <AttributeType name="Quantity" dt:type="i2"/>
  <attribute type="ProductID"/>
  <attribute type="Quantity"/>
</ElementType>
</Schema>
<Customers xmlns="x-schema:#Schema1" CustomerID="ALFKI" ContactName="Maria Anders">
  <Orders OrderID="10643" OrderDate="1997-08-25T00:00:00" 
          CustomerID="ALFKI">
    <Order_0020_Details ProductID="28" Quantity="15"/>
    <Order_0020_Details ProductID="39" Quantity="21"/>
  </Orders>
  <Orders OrderID="10692" OrderDate="1997-10-03T00:00:00"   
          CustomerID="ALFKI">
    <Order_0020_Details ProductID="63" Quantity="20"/>
  </Orders>
</Customers>

The query in the following example assigns the same alias to the EmployeeID and LastName columns, and Employees table specified in the FROM clause:

SELECT EmployeeID emp, 
       LastName emp 
FROM Employees emp 
FOR XML AUTO, ELEMENTS, XMLDATA

Only the resulting XML-Data schema is shown. In the schema there are three <emp> elements. Also note that two of the <emp> elements have different data types.

<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="emp" content="mixed" model="closed">
   <element type="emp"/>
   <element type="emp"/>
</ElementType>
<ElementType name="emp" content="textOnly" model="closed" dt:type="i4"/>
<ElementType name="emp" content="textOnly" model="closed" dt:type="string"/>
</Schema>