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,

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

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,

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: dmin...@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

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,