You might be wondering: If XML describes only character-based data, how can I retrieve the binary data stored in my columns ? You can do this in two ways. The first is by explicitly requesting the binary data in a character-encoded format by specifying the BINARY BASE64 option at the end of the FOR XML clause. The second is by using FOR XML without the BINARY BASE64 option to return an XPath reference to the location of the binary data.
SQL Server 2000 uses the BASE64 algorithm to encode binary column data into XML-friendly character data. ( BASE64 is defined as part of the Multipurpose Internet Mail Extensions (MIME) types, a set of standards for transmitting data over the Internet.)
Listing 41.7 provides an example of the use of BINARY BASE64 in conjunction with AUTO mode. Note: The resulting Picture element's value is truncated at the ellipses for brevity.
Listing 41.7 Using AUTO Mode with BINARY BASE64 to Retrieve Base64-Encoded Data from an Image Datatype Column
SELECT Picture FROM Categories WHERE CategoryID=1 FOR XML AUTO, ELEMENTS, BINARY BASE64 go XML_F52E2B61-18A1-11d1-B105-00805F49916B <Categories><Picture>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXA[...] </Picture></Categories>
When using AUTO mode, you can retrieve binary data in two ways:
When you add the primary key to the SELECT list and do not specify BINARY BASE64 , SQL Server 2000 won't return base64-encoded data in the XML. Instead, it will create a URL-encoded string that points to the relative database location of the binary data using a special XML syntax called XML Path Language (XPath).