An important feature of SQL Server 2000 is the ability to retrieve XML-formatted metadata that defines the content model (what elements will be present, their nesting structure, and what types of data they contain) of an XML document.
This metadata comes in the form of a well- formed XML document known as an XML-Data schema. It can be returned in queries that use any of the three FOR XML modes, and to get it, you specify the XMLDATA option, as exemplified in Listing 41.6.
Listing 41.6 Using the XMLDATA Option with FOR XML AUTO to Return an XML-Data Schema
SELECT TOP 2 OrderID, OrderDate, CustomerID FROM Orders FOR XML AUTO, XMLDATA go <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes"> <ElementType name="Orders" content="empty" model="closed"> <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> </Schema> <Orders xmlns="x-schema:#Schema1" OrderID="10248" OrderDate="1996-07-04T00:00:00" CustomerID="VINET"/> <Orders xmlns="x-schema:#Schema1" OrderID="10249" OrderDate="1996-07-05T00:00:00" CustomerID="TOMSP"/>
First note that the schema is always output directly on top of your XML results. Schema is always its root element, and its name attribute has a special function: It declares the document as a namespace . When a namespace is used, elements in other XML documents might contain the elements defined in this schema by specifying the name of the schema as the value of their xmlns (XML Namespace) attribute.
Orders elements, for example, are linked to Schema1 by way of their xmlns attribute. The value of xmlns (preceded by "x-schema:") points back to the schema as a way of indicating that the metadata in the schema applies to Orders elements. The # sign indicates that the schema is inline (it works just like the # sign does in HTML links) or contained within the XML document it describes. (Note also that schemas themselves refer to a Microsoft namespace in their xmlns attribute.)
The name attribute will always have a value of Schema followed by an integer. This integer is incremented automatically by SQL Server after every query generated during the same session to prevent what is known as a namespace collision ”when two XML documents declare the same namespace. It's necessary to rename the schema in this way because it differentiates one schema from any other that might have been produced by a query executed during the same SQL Server session.
The structure of the schema provides useful information about the XML. The values of its elements and attributes will differ depending on the mode and options you specify in the FOR XML clause. The elements that will be present (as of this writing ” please note that the specification for XML Schemas is a work in progress) are as follows :
Knowing these things about your XML results before parsing them enables you to write generic processing code that is far more likely to be reused than code that is purely data-specific.