Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

OPENXML and image binary base64

54 views
Skip to first unread message

Alberto Richart

unread,
Apr 3, 2002, 8:33:11 AM4/3/02
to
Hi all,

I get an xml from a FOR XML AUTO select statement. Since
my select contains an image field, I use the BINARY BASE64
coding. This is an example:

SELECT MyField1, MyImageField
FROM MyTable
FOR XML AUTO, BINARY BASE64

I have a stored procedure to insert the xml by means of
OPENXML.

Does anyone know how to process the binary base64 data
field in the OPENXML statement?

Thanks in advance,

Alberto.

David M

unread,
Apr 5, 2002, 4:17:12 AM4/5/02
to
Alberto

This will depend on what you are returning the XML
to. .NET's XML classes, for example, contain methods for
dealing with this. Tell me where the XML is going and I
will try to give you some pointers.

David

>.
>

Alberto Richart

unread,
Apr 5, 2002, 4:51:44 AM4/5/02
to
Hi David,

The returned XML contains the image field coded in binary
base64 format. Should I convert it from base64 to
hexadecimal? Or should I use another way of getting the
xml without using a FOR XML AUTO, BINARY BASE64 clause?

I don't know the format of the OPENXML clause for
inserting image fields.

The insert statement looks like as follows:

INSERT INTO MyTable(MyKey, MyImageField)
SELECT MyKey, MyImageField
FROM OPENXML(@hDoc, '/SqlXml/MyTable', 2)
WITH MyTable

Thanks,

Alberto.

Drew Minkin[MS]

unread,
Apr 9, 2002, 7:24:29 AM4/9/02
to
Alberto,

OPENXML cannot translate bin64 back into binary because the parsing sees
the node as character data. To circumvent this, try this function:

CREATE FUNCTION base64tobin (@bin64raw varchar(8000))
RETURNS varbinary(8000)
AS
BEGIN
declare @out varbinary(6000)
declare @i int
declare @length int
declare @bin64char char(1)
declare @bin64rawval tinyint
declare @bin64phase tinyint
declare @bin64nibble1 tinyint
declare @bin64nibble2 tinyint
declare @bin64nibble3 tinyint
SELECT @bin64phase = 0
SELECT @i = 1
SELECT @length = len(@bin64raw)
WHILE @i < @length
BEGIN
SELECT @bin64char = substring(@bin64raw,@i,1)
BEGIN
IF ASCII(@bin64char) BETWEEN 65 AND 90
SELECT @bin64rawval = ASCII(@bin64char)-65
ELSE
IF @bin64char LIKE '[a-z]'
SELECT @bin64rawval = ASCII(@bin64char)-71
ELSE
IF @bin64char LIKE '[0-9]'
SELECT @bin64rawval = ASCII(@bin64char)+4
ELSE
IF @bin64char = '+'
SELECT @bin64rawval = ASCII(@bin64char)+19
ELSE
IF @bin64char = '/'
SELECT @bin64rawval = ASCII(@bin64char)+16
ELSE
BEGIN
SELECT @bin64rawval = 0
SELECT @i = @length-1
END

END
IF @bin64phase = 0
BEGIN
SELECT @bin64nibble1 = (@bin64rawval - @bin64rawval%4)/4
SELECT @bin64nibble2 = @bin64rawval%4
SELECT @bin64nibble3 = 0
END
ELSE
IF @bin64phase =1
BEGIN
SELECT @bin64nibble2 = (@bin64nibble2*4) + (@bin64rawval -
@bin64rawval%16)/16
SELECT @bin64nibble3 = @bin64rawval%16
IF @i<5

SELECT @out= convert (binary(1),((16*@bin64nibble1) + @bin64nibble2))
ELSE
SELECT @out= @out + convert (binary(1),((16*@bin64nibble1) +
@bin64nibble2))
END
ELSE
IF @bin64phase =2
BEGIN
SELECT @bin64nibble1 = @bin64nibble3
SELECT @bin64nibble2 = (@bin64rawval - @bin64rawval%4)/4
SELECT @bin64nibble3 = @bin64rawval%4
SELECT @out=@out+ convert (binary(1),((16*@bin64nibble1) +
@bin64nibble2))
END
ELSE
IF @bin64phase =3
BEGIN
SELECT @bin64nibble1 = (@bin64nibble3*4) + (@bin64rawval -
@bin64rawval%16)/16
SELECT @bin64nibble2 = @bin64rawval%16
SELECT @out=@out+ convert (binary(1),((16*@bin64nibble1) +
@bin64nibble2))
END
SELECT @bin64phase = (@bin64phase + 1)%4
SELECT @i = @i + 1
END
RETURN(@out)
END

Thanks for the post and I hope that the information was helpful

Drew Minkin, MCDBA, MCSD, MCSE
SQL Support Professional, Microsoft Corporation
email: dmi...@online.microsoft.com

This posting is provide “AS IS” with no warranties, and confers no rights.
Please do not send email directly to this alias. Respond to the newsgroup

For the latest in SQLXML, check out
http://www.microsoft.com/sql/techinfo/xml

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Alberto Richart

unread,
Apr 11, 2002, 4:45:15 AM4/11/02
to
Drew,

To use the function base64tobin I have to get the binary base64 field from
the xml and call the function. I use the xml in a stored procedure, so I
don't think it's a good idea.

In order to understand my problem, I can read the blob field in the client
and process it, but I don't know which format should the xml blob field
have, because I need to insert the blob in the server by means of an OPENXML
clause. Any help???

Thanks in advance,

Alberto.


Drew Minkin[MS]

unread,
Apr 23, 2002, 6:53:43 PM4/23/02
to
Alberto,

OPENXML is flexible enough that you can add the UDF to your XML parsing
stored procedure.

Example:

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer base64data="AFF+=="/>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
INSERT INTO SELECT db.dbo. base64tobin(CustomerID)
FROM OPENXML (@idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(8000))

0 new messages