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
No comments:
Post a Comment