Retrieving Binary Fields with the BINARY BASE64 Option

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"/> 

Programming Microsoft SQL Server 2000 With Xml
Programming Microsoft SQL Server(TM) 2000 with XML (Pro-Developer)
ISBN: 0735613699
EAN: 2147483647
Year: 2005
Pages: 89 © 2008-2017.
If you may any questions please contact us: