Retrieving Binary Data in XML

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.)

NOTE

BINARY BASE64 works with all three FOR XML modes, but it is required unless you use FOR XML AUTO , which generates encoded data automatically (detailed in the section "The Basics of XML Path Language (XPath)"). When using RAW or EXPLICIT mode, SQL Server 2000 requires you to know ahead of time whether a field in your query is binary. If you select a binary field but do not specify BINARY BASE64 , an error will result that reads, in part, use BINARY BASE64 to obtain binary data in encoded form .

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:

• Specify BINARY BASE64 .

• Add the primary key of the table to the SELECT list in the query.

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).