Microsoft SQL Server 2008 R2 Unleashed- P209:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1874 CHAPTER 47 Using XML in SQL Server 2008 MountainBikeSpecials xmlns xsi http 2001 XMLSchema-instance Product Color White Color Id 710 Id Name Mountain Bike Socks L Name Size L Size Offer Id 1 Id Desc No Discount Desc Offer Product Product Color White Color Id 709 Id Name Mountain Bike Socks M Name Size M Size Offer Id 1 Id Desc No Discount Desc Offer Offer Id 2 Id Desc Volume Discount 11 to 14 Desc Offer Offer Id 3 Id Desc Volume Discount 15 to 24 Desc Offer Offer Id 4 Id Desc Volume Discount 25 to 40 Desc Offer Product MountainBikeSpecials With AUTO mode the keywords BINARY BASE64 have the same effect as with RAW mode with one major difference RAW mode generates an error if binary data is selected and BINARY BASE64 is not specified therefore it is required. With AUTO mode binary data may be selected without specifying BINARY BASE64 although SQL Server requires that the primary key of the table containing the binary data be selected. This is so that SQL Server can generate a path to the binary field using the primary key to address the row in place of the encoded data of the following form dbobject @PrimaryKeyName PrimaryKeyValue @ColumnName Relational Data As XML The FOR XML Modes 1875 This special XPath-like output is unique to AUTO mode and is useful for applications that incorporate SQLXML s URL-based querying to return the desired binary data. Listing illustrates this XML production. LISTING Addressing Binary Data That Uses FOR XML AUTO SELECT Top 1 ThumbNailPhoto Color Id Id Name Description Desc Size FROM Offer JOIN OP ON JOIN Product ON JOIN PhotoJunction ON JOIN Photo ON WHERE
