FOR XML Enhancements

FOR XML Enhancements

The native XML support in SQL Server 2000 was introduced by providing the FOR XML clause and the OPENXML function. The FOR XML clause can be used in a SELECT statement to convert a relational rowset into an XML stream. On the other hand, the OPENXML function does the reverse: It provides the rowset view over an XML document. SQL Server 2005 enhances these two constructs to fix some of the limitations from previous releases and also to add support for the new xml data type.

The following FOR XML clause enhancements have been introduced in SQL Server 2005:

• The new TYPE modifier can be used to generate an instance of an xml data type that can be assigned to a variable or can be directly used in a query. The following query uses the TYPE modifier with a FOR XML clause and saves the generated XML fragment in an xml data type variable, on which the XQuery query is executed later on:

  USE AdventureWorks; GO DECLARE @Contacts xml SET @Contacts =  (SELECT TOP 10 FirstName, MiddleName, LastName, EmailAddress, Phone   FROM Person.Contact ORDER BY LastName   FOR XML AUTO, TYPE); SELECT @Contacts.query(' <Contacts> {  for $c in /Person.Contact  return    <Contact>      <Name>{data($c/@LastName)}{data(" ")}{data($c/@FirstName)}      </Name>      <Phone>{data($c/@Phone)}</Phone>      <Email>{data($c/@EmailAddress)}</Email>    </Contact>  } </Contacts> '); GO

  
  

• The FOR XML queries can now be nested. It is important to use the TYPE directive with the internal query; otherwise, the XML generated by the internal query will be entitized (that is, < will be replaced with &lt; and so on). Many complex queries that were written using FOR XML EXPLICIT can now be replaced with nested FOR XML queries, which are simple to write and manage. Here is an example of nesting the FOR XML clause:

  SELECT SalesOrderNumber, PurchaseOrderNumber,  (SELECT AddressLine1, AddressLine2, City, PostalCode   FROM Person.Address   WHERE AddressID =     (SELECT AddressID FROM Sales.CustomerAddress      WHERE CustomerID = s.CustomerID AND AddressTypeID = 3)   FOR XML AUTO, ELEMENTS, TYPE   ) FROM Sales.SalesOrderHeader s WHERE PurchaseOrderNumber IS NOT NULL FOR XML AUTO

  
  

• The FOR XML clause in SQL Server 2000 could only generate XML Data Reduced (XDR) schemas. (XDR is Microsoft's proprietary XML schema format.) The new XMLSCHEMA directive in SQL Server 2005 allows you to generate inline XSD schemas, which are based on the W3C standard. The optional input to the XMLSCHEMA directive is the target namespace URI, as in the following example:

  SELECT * FROM HumanResources.Department FOR XML AUTO, XMLSCHEMA('urn:test.com');

  
  

  The default namespace URL is auto-generated in a format such as

  urn:schemas-microsoft-com:sql:SqlRowSet1.

  
  

• The new ROOT directive allows you to generate a well-formed XML document with a single root element. The optional input to the ROOT directive is the name of the topmost element. By default, the top-level element is called root when the ROOT directive is specified:

  SELECT * FROM HumanResources.Department FOR XML AUTO, ROOT('Departments');

  
  

• The RAW mode now supports the ELEMENTS directive so that the generated XML stream contains elements instead of attributes for columns. The first SELECT statement is without the ELEMENTS clause, and the next one uses the ELEMENTS clause with FOR XML RAW. In addition, you can now pass the name of an element that is generated for each record, instead of the default element, called row. try the following two SELECT statements to see the RAW mode enhancements:

  SELECT * FROM HumanResources.Department FOR XML RAW, ROOT('Departments'); SELECT * FROM HumanResources.Department FOR XML RAW('Department'), ELEMENTS, ROOT('Departments');

  
  

• In SQL Server 2000, the EXPLICIT mode provided the most control over the structure of the XML document generated. However, it is not easy to write queries by using the EXPLICIT mode. SQL Server 2005 simplifies this by providing a new mode called PATH that allows you to use XPath and specify where and how in the hierarchy the column should appear. The PATH mode provides a simpler way to mix elements and attributes and control the hierarchy of generated XML. An optional argument with the PATH mode is the element name for each record. By default, it is called row. Here is an example of the PATH mode:

  SELECT    DepartmentID "@id",    ModifiedDate "@updated",    Name         "Name",    GroupName    "Group" FROM HumanResources.Department FOR XML PATH ('Department'), ROOT('Departments');

  
  

• The ELEMENTS directive now provides an XSINIL option to map NULL values to an element with an attribute of xsi:nil="true" instead of completely omitting the element. If you execute the following batch in SQL Server 2000, you notice that for first row, the col2 element is completely missing:

  USE [tempdb]; GO CREATE TABLE tblTest  (col1 INT IDENTITY(1,1) NOT NULL,   col2 VARCHAR(20) NULL); GO INSERT INTO tblTest DEFAULT VALUES; INSERT INTO tblTest (col2) VALUES ('Value2'); GO SELECT * FROM tblTest FOR XML AUTO, ELEMENTS; GO --SELECT * FROM tblTest FOR XML AUTO, ELEMENTS XSINIL; GO DROP TABLE tblTest; GO

  
  

If you execute the script in SQL Server 2005, you notice the same thing. If you uncomment the statement containing the XSINIL option and run it in SQL Server 2005, you notice that for col2 having the NULL value, an element is generated with an xsi:nil="true" attribute.