How to read data from XML that is stored as image type in SQL?

Question

I have an XML stored in the SQL as image type. What I'm trying to and actually managed to accomplish is to get a data from that stored xml. The problem is the data is stored as unicode, but what I'm getting is an ascii type of the data. I've tried alot but can't make this work.

DECLARE @xml XML;
set @xml = convert(xml,(CONVERT(varchar(max), @data)));
SELECT 
T.c.query('data(node1/id)') as 'id',
T.c.value('node1[1]/name[1]','nvarchar(100)') as 'name'
FROM   @xml.nodes('Test1/Test2') T(c)

I'm converting data to varchar(max) because I can't get any data when I cast it into nvarchar(max). Like, if I change my code to

set @xml = convert(xml,(CONVERT(nvarchar(max), @data)));

I can't see any data.

Thanks for the help.

It would probably be more worthwhile spending the time now on permanently changing the column data type (to either `xml` if all values are xml or `nvarchar(max)` otherwise). `image` is deprecated. — Damien_The_Unbeliever, Feb 18 '14 at 08:50
It's saved as image on Sql server and I'm getting it on a local variable as varbinary(max) — user3322380, Feb 18 '14 at 11:07
Have you tried to convert directly to XML? `cast(@data as xml)`. — Mikael Eriksson, Feb 18 '14 at 16:49

score 0 · Answer 1 · answered May 27 '14 at 13:26

0

Try converting to xml on the fly. cast(@data as xml)

answered May 27 '14 at 13:26

CoderKK

351
1
7
18

How to read data from XML that is stored as image type in SQL?

1 Answers1