We have a SQL table that consists of several fields, one of which we know is
an XML dump from a table. The thing is, we don't know how to extract the
data out of the XML table that resides in this field. For example, here is a
single record of part of the table. The XML resides in the field labeled
'Data':

UnitID ManufacturedDate Data
0001eb5761f24313... 2006-08-01
0x3C0055006E00690074004500780...

The 'Data' field is an 'image' data type.

Does anyone know how to convert that data back into something that is
useable??

Image is just a Large Object (LOB) binary data type. Are you on SQL 2000?

"James" <James@discussions.microsoft.com> wrote in message
news:82EA9DD3-78D9-417E-B647-B54D77BD368E@microsoft.com...
> We have a SQL table that consists of several fields, one of which we know
> is
> an XML dump from a table. The thing is, we don't know how to extract the
> data out of the XML table that resides in this field. For example, here
> is a
> single record of part of the table. The XML resides in the field labeled
> 'Data':
>
> UnitID ManufacturedDate Data
> 0001eb5761f24313... 2006-08-01
> 0x3C0055006E00690074004500780...
>
> The 'Data' field is an 'image' data type.
>
> Does anyone know how to convert that data back into something that is
> useable??

Yes, this particular table is.

"Mike C#" wrote:

> Image is just a Large Object (LOB) binary data type. Are you on SQL 2000?
>
> "James" <James@discussions.microsoft.com> wrote in message
> news:82EA9DD3-78D9-417E-B647-B54D77BD368E@microsoft.com...
> > We have a SQL table that consists of several fields, one of which we know
> > is
> > an XML dump from a table. The thing is, we don't know how to extract the
> > data out of the XML table that resides in this field. For example, here
> > is a
> > single record of part of the table. The XML resides in the field labeled
> > 'Data':
> >
> > UnitID ManufacturedDate Data
> > 0001eb5761f24313... 2006-08-01
> > 0x3C0055006E00690074004500780...
> >
> > The 'Data' field is an 'image' data type.
> >
> > Does anyone know how to convert that data back into something that is
> > useable??
>
>
>

SQL Server 2000 has only poor support of the XML data type. You could,
however, pull the data over to a SQL Server 2005 instance (e.g. Express
Edition) and manipulate it there.

On SQL 2000, however, your best option is to use a client application to
manipulate XML data (or any other large data type).


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

I agree with ML, your best bet is to manipulate the data using a client
application or get it somewhere with some decent support for XML. If you
have a SQL 2005 instance handy you could probably set up a linked server to
the 2000 instance pretty easily.

One other thing you might be able to do is use OPENXML to grab the data, but
it's been a while so I can't recall if OPENXML would support a path
expression like //*. If I recall correctly OPENXML also returns data in a
special table format if you don't specify the result table structure, so you
might be able to get element names, etc., that way. You'd probably be a lot
better off just manipulating it outside of SQL Server 2000 though.

"James" <James@discussions.microsoft.com> wrote in message
news:71AC28D2-FE11-468C-B647-73AC21720648@microsoft.com...
> Yes, this particular table is.
>
> "Mike C#" wrote:
>
>> Image is just a Large Object (LOB) binary data type. Are you on SQL
>> 2000?
>>
>> "James" <James@discussions.microsoft.com> wrote in message
>> news:82EA9DD3-78D9-417E-B647-B54D77BD368E@microsoft.com...
>> > We have a SQL table that consists of several fields, one of which we
>> > know
>> > is
>> > an XML dump from a table. The thing is, we don't know how to extract
>> > the
>> > data out of the XML table that resides in this field. For example,
>> > here
>> > is a
>> > single record of part of the table. The XML resides in the field
>> > labeled
>> > 'Data':
>> >
>> > UnitID ManufacturedDate Data
>> > 0001eb5761f24313... 2006-08-01
>> > 0x3C0055006E00690074004500780...
>> >
>> > The 'Data' field is an 'image' data type.
>> >
>> > Does anyone know how to convert that data back into something that is
>> > useable??
>>
>>
>>