Binary data such as images can be retrieved in an XML document in BASE64-encoded format, which is useful if you need to send binary data to an application or trading partner. To retrieve binary BASE64 data, you must specify the BINARY BASE64 option in a FOR XML query, as shown here:
SELECT picture FROM categories WHERE categoryid = 1 FOR XML RAW, BINARY BASE64
This code returns an encoded image, as shown in the following partial XML fragment. (The binary data has been truncated.)
<row picture="FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQu ... "/>
You can also retrieve a reference to binary data when using AUTO mode. This reference can be used to retrieve the data over HTTP through a SQL Server virtual root. (I'll talk about HTTP access to SQL Server in Chapter 4.) To retrieve a reference to binary data, you must include a primary key field in the query, as you can see here:
SELECT categoryid, picture FROM categories WHERE categoryid = 1 FOR XML AUTO
The resulting XML fragment contains an XPath reference to the record containing the binary data, as shown here:
<categories category picture="dbobject/categories[@CategoryID='1']/@Picture"/>