FOR XML RAW with XMLDATA Directive

Today we'll learn more about XMLDATA directive for the FOR XML clause. 

XMLDATA Directive
       Return XML-Data schema (XML-Data Reduced schema aka XDR schema) of XML document/fragment.
       Cannot be use with ROOT directive.
       Namespace identifier = Schema+n where n is an integer. Everytime we execute the same query, n is incremented by 1.
       Will be deprecated on future release of SQL Server. Recommendation: use XMLSCHEMA directive to return XSD schema instead.
      

--This query returns the XML-Data Schema of the XML result.
SELECT custid, contactname
FROM   Sales.Customers 
WHERE  custid <= 2
FOR    XML RAW
     , XMLDATA;

--
--Result: Notice, if the same query above is executed one more time in the same query window,
--          the schema's name will change from Schema1 to Schema2.
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="empty" model="closed">
    <AttributeType name="custid" dt:type="i4" />
    <AttributeType name="contactname" dt:type="string" />
    <attribute type="custid" />
    <attribute type="contactname" />
  </ElementType>
</Schema>
<row xmlns="x-schema:#Schema1" custid="1" contactname="Allen, Michael" />
<row xmlns="x-schema:#Schema1" custid="2" contactname="Hassall, Mark" />

--
--The schema changes compared to result of previous query, when ELEMENTS directive is specified.
--ELEMENTS directive will changes the XML document/fragment's presentation 
--  from attribute-centric to element-centric presentation.
SELECT custid, contactname
FROM   Sales.Customers 
WHERE  custid <= 2
FOR    XML RAW
     , XMLDATA
     , ELEMENTS;

Result:
<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="eltOnly" model="closed" order="many">
    <element type="custid" />
    <element type="contactname" />
  </ElementType>
  <ElementType name="custid" content="textOnly" model="closed" dt:type="i4" />
  <ElementType name="contactname" content="textOnly" model="closed" dt:type="string" />
</Schema>
<row xmlns="x-schema:#Schema2">
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
</row>
<row xmlns="x-schema:#Schema2">
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</row>

--
--This query will yield an error because ROOT directive cannot be used with XMLDATA directive.
SELECT custid, contactname
FROM   Sales.Customers 
WHERE  custid <= 2
FOR    XML RAW
     , XMLDATA
     , ELEMENTS
     , ROOT; -- error, ROOT directive cannot be used with XMLDATA directive

Result:
Msg 6860, Level 16, State 1, Line 1
FOR XML directive XMLDATA is not allowed with ROOT directive or row tag name specified. 

If you know any other restrictions or uses of the XMLDATA directive for the FOR XML RAW, please share. Next post we'll learn about XMLSCHEMA Direction for the FOR XML RAW clause.

Reference:
http://technet.microsoft.com/en-us/library/bb510461.aspx
http://technet.microsoft.com/en-us/library/ms178035.aspx