Hi All,

I am trying to insert base64 data into an image field. Based on things I have read on other posts, I thought the way to do this was to use the OpenXML function to automatically convert the base64 field to an image field. It does put data into the image field, but it doesn't seem to do it correctly. I am trying to use this code:

==========
DECLARE @xml AS VARCHAR(1000)
SET @xml = '<?xml version="1.0" encoding="utf-8" ?>
<root>
<Schema name="inLineSchema"
xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="document" content="empty"
model="closed">
<AttributeType name="document_content"
dt:type="bin.base64" />
<attribute type="document_content" />
</ElementType>
</Schema>
<document xmlns="x-schema:#inLineSchema"
document_content="VA==" />
</root>'

DECLARE @ns AS VARCHAR(500)
SET @ns = '<root xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:a="x-schema:#inLineSchema"
xmlns:dt="urn:schemas-microsoft-com:datatypes" />'

DECLARE @intPointer AS INTEGER
EXEC sp_xml_preparedocument @intPointer OUTPUT, @xml, @ns

SELECT *
FROM OPENXML(@intPointer, '/root/a:document', 4)
WITH (document_content image)

EXEC sp_xml_removedocument @intPointer
==========

The result of the OPENXML call is 0x56004... The base64 value "VA==" should translate to 0x54.

Any ideas about what is going wrong or other ideas on how to do this?

Thanks.