still problem with image

1 Dec 2005 9:25 PM

JFB

Hi All,
I have a problem storing a binary image coming from an xml file.
The image has 39,000 characters.
I my table I define the field as image but only store 0x.
What is the problem?
I try to store as text and doesn't work either, if I try varchar it works
but store part of the length.
How can I fix this?
It's a way to change and store the long name as short name?
Tks in advance.
JFB

1 Dec 2005 10:42 PM

ML

If the image is stored as Base64 in your XML you need to convert it to
binary, before storing it in an image column.

What method are you using to store the XML in the text/ntext column?

ML

---
http://milambda.blogspot.com/

2 Dec 2005 2:49 PM

JFB

Update myTable
Set
myTable.customer = xmlTable.cust,
myTable.address1 = xmlTable.addr1
myTable.xmlImage = xmlTable.image2
FROM OPENXML(@hDoc, '/FieldTitle1')
WITH (
     Cust       varchar(32)        'Request/Reference/Customer'
    ,ReqOpt varchar(32)          'Request/RequestOption'
    ,addr1     varchar(32)       'Address/addressLine1'
    ,addr2     varchar(32)       'Address/addressLine2'
    ,image2     image                'imageLabel/gifimage' ) xmlTable
Where myTable.RequetOption = xmlTable.ReqOpt

Here is how I'am storing the image.
Tks for you reply and help.
JFB

Show quote

"ML" <M*@discussions.microsoft.com> wrote in message
news:8619F6C5-0BEC-4AAA-807D-2124F3131D08@microsoft.com...
> If the image is stored as Base64 in your XML you need to convert it to
> binary, before storing it in an image column.
>
> What method are you using to store the XML in the text/ntext column?
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

2 Dec 2005 3:15 PM

ML

Could you please post the table DDL? What data type is the myTable.xmlImage
column?

Does the query SELECT...FROM OPENXML... return the expected result when you
run it outside the update statement?

ML

---
http://milambda.blogspot.com/

2 Dec 2005 5:27 PM

JFB

Tks for you reply.
As you see in my code ,image2 image
'imageLabel/gifimage'
It's an image type.
Does the query SELECT...FROM OPENXML...
Yes it does because if I change the variable as varchar it store the
characters but short length.
Rgds
Johnny

Show quote

"ML" <M*@discussions.microsoft.com> wrote in message
news:AA558A05-6A9B-4AA8-9660-9E58F01A5711@microsoft.com...
> Could you please post the table DDL? What data type is the
> myTable.xmlImage
> column?
>
> Does the query SELECT...FROM OPENXML... return the expected result when
> you
> run it outside the update statement?
>
>
> ML
>
> ---
> http://milambda.blogspot.com/

2 Dec 2005 5:56 PM

ML

Using OPENXML you cannot store any part of the XML in an image column without
proper conversion from text (all XML values are text).

Try modifying the datatype of the column to ntext or text. Binary values
(files) are stored as character representations (Base64), and there is no
native SQL function to convert Base64 to image.

You could, however, derive binary values from XML with an appropriate client
application and insert them into the database from there.

ML

---
http://milambda.blogspot.com/

2 Dec 2005 6:15 PM

JFB

Nope... it doesn't work either.

> You could, however, derive binary values from XML with an appropriate
> client
> application and insert them into the database from there.

I need to store the image after the confirmation came from web services
site.
Tks
JFB

Show quote

"ML" <M*@discussions.microsoft.com> wrote in message
news:C92D5D44-8429-4966-AE99-61354827C2E9@microsoft.com...
> Using OPENXML you cannot store any part of the XML in an image column
> without
> proper conversion from text (all XML values are text).
>
> Try modifying the datatype of the column to ntext or text. Binary values
> (files) are stored as character representations (Base64), and there is no
> native SQL function to convert Base64 to image.
>
> You could, however, derive binary values from XML with an appropriate
> client
> application and insert them into the database from there.
>
>
> ML
>
> ---
> http://milambda.blogspot.com/