Problem with FOR XML clause

I am trying to persist data from SQL Server 2005 table into an XML file using FOR XML clause in the SELECT statement. I have a column named “Photo” of type Image. Issue is; the FOR XML clause is returning the picture as some reference instead of binary format.

<Photo>dbobject/employees[@.EmployeeID='1']/@.Photo</Photo>

Writing XML file using DataSet.WriteXml() method persists the same column as binary format

<Photo>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQABBQAAAgAAAAcAAABQQnJ1c2

I have trimmed the above binary string for brevity. The XML file is used for backing and restoring data in the database.

Thanks

Try using the "SELECT * FROM <table> FOR XML RAW, BINARY BASE64". This option helps to write binary columns. See ms-help topic:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/02c1bc0b-760c-4589-9ab1-6927c6d9c734.htm

For more details.

Jeff Derstadt - MSFT