How to decode binary base 64 data using OPENXML

Jason Frank

unread,

Jun 3, 2005, 6:31:17 PM6/3/05

to

I am using SELECT ... FOR XML EXPLICIT, BINARY BASE64 in order to read
binary data out of SQL Server and put it into XML. This works great, I can
then convert it back to a byte array in the middle tier to get my binary
data.

I would like to be able to send binary data back to the database in XML, and
then use OPENXML to parse it out and do an update. The most natural thing
would seem to be to pass the binary data as base64 encoded text in the XML,
just as it was returned from the SELECT. The problem is, I can't figure out
how to get this to work.

Imagine that my XML looks like the following, after reading it out with
SELECT ... FOR XML EXPLICIT, BINARY BASE64:

It is the Code attribute that is the binary data. My OPENXML statement
looks something like the following (I've omitted the part where I call
sp_xml_preparedocument):

SELECT ValueCode, DisplayName
FROM OPENXML(@iDoc, 'DatasetVariable/DatasetValues/DatasetValue', 2)
WITH(ValueCode BINARY(8) './@Code',
DisplayName NVARCHAR(256) './@Name')

The problem is that it seems to just take the characters' ASCII values and
interpret those as the binary data. How can I get it to decode the
characters from base64 into binary?

Thanks,
Jason

Michael Cheng [MSFT]

unread,

Jun 6, 2005, 7:03:47 AM6/6/05

to

Hi Jason,

Welcome to use MSDN Managed Newsgroup!

From your descriptions, I understood you would like to know how to decode
BINARY BASE64 using OPENXML. If I have misunderstood your concern, please
feel free to point it out.

Based on my scope, I am afraid we are not able to do this directly with
OPENXML and you may refer the articles below on about

How To: Inserting Binary Data using XML
http://sqlxml.org/faqs.aspx?faq=51

How To: Retrieving binary data using XML
http://sqlxml.org/faqs.aspx?faq=52

Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Jason Frank

unread,

Jun 6, 2005, 9:35:54 AM6/6/05

to

The first article you reference there mentions using a schema to specify the
data type as bin.base64 and then use an updategram or bulk loading. So it
seems that there is no way to use a schema with OPENXML? The WITH clause is
a kind of schema, I suppose, but perhaps it does not allow for information
such as the bin.base64 encoding? I'd like to verify that I am unable to do
what I want to do with OPENXML before I start exploring the updategram and
bulk loading topics, about which I know very little.

"Michael Cheng [MSFT]" <v-mi...@online.microsoft.com> wrote in message
news:YiIAicoa...@TK2MSFTNGXA01.phx.gbl...

Jason Frank

unread,

Jun 6, 2005, 6:29:34 PM6/6/05

to

Further evidence that I cannot do what I want with OPENXML (the guy says to
wait until SQL Server 2005, which is great but not an option for me).
http://www.mcse.ms/archive96-2004-11-1203447.html

Just to add some detail to what I meant about using schemas with OPENXML...

I tried something like the following to see if it would do the proper base64
decoding, but it did not. (I copied the basic schema and xml document from
another posting I had found and modified it. The posting was
http://www.mcse.ms/archive96-2004-8-970022.html) The resulting rowset had a
column called ProductCode but it was again just the binary representation of
the characters that I had put in, not the base64 decoded version of that.
---
DECLARE @xmlDoc nvarchar(2000)
SET @xmlDoc = '<root>
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="Products" content="empty" model="closed">
<AttributeType name="ProductID" dt:type="i4"/>
<AttributeType name="ProductCode" dt:type="bin.base64"/>
<AttributeType name="ProductName" dt:type="string"/>
<attribute type="ProductID"/>
<attribute type="ProductCode"/>
<attribute type="ProductName"/>
</ElementType>
</Schema>
<Products xmlns="x-schema:#Schema1" ProductID="2" ProductCode="Pdt839nXvbs="
ProductName="Chang"/>
</root>'

DECLARE @h integer
EXEC sp_xml_preparedocument @h OUTPUT, @xmlDoc, '<x:mydata
xmlns:x="x-schema:#Schema1" />'

SELECT * FROM OPENXML(@h, 'root/x:Products', 1)
WITH (ProductID integer, ProductCode binary(8),
ProductName nvarchar(40))

EXEC sp_xml_removedocument @h
---

"Jason Frank" <mabra...@newsgroup.nospam> wrote in message
news:Ofw$rypaFH...@TK2MSFTNGP10.phx.gbl...

Michael Rys [MSFT]

unread,

Jun 8, 2005, 1:38:42 AM6/8/05

to

I am the guy that said that :-).

I also recommended that you can "Extract the encoded image and run a
base64-decoder on it before doing the
insertion. The algorithm is well-described in the literature and can be
implemented using T-SQL or an extended stored proc."

A quick online search revealed:

http://www.motobit.com/help/scptutl/sa307.htm which uses sp_OA stored procs.
And Steve Kaas' T-SQL UDF solution that implemented the algorithm in T-SQL
only at
http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/b47ca93137dc27c6/240cad41028bca78?q=23DB840F-B9C7-4CCF-BD3C-49EDBEC8F0D0&rnum=1#240cad41028bca78

Best regards
Michael

"Jason Frank" <mabra...@newsgroup.nospam> wrote in message

news:OBYF5cu...@tk2msftngp13.phx.gbl...