Cast the SQL 2005 IMAGE data type to the XML data type; then use @xml.query to get element/attribute values

The idea here is that you have XML in an image column and need to get the value of some element or attribute in that XML. Using SQL Server 2005, here's how:

 

go
set nocount on

declare @string varchar(max)
set @string =    '<root>
                    <users>
                        <user name="Jerry" type="0">A</user>
                        <user name="Eric" type="1">B</user>
                        <user name="Wes" type="2">C</user>
                        <user name="David" type="3">D</user>
                    </users>
                </root>'

    print ''
    print 'source xml'
    print @string

-- create demo table
create table x (imageCol image)
insert into x select @string

/*
    Note: first cast to varbinary(max) or get the exception:
    'Explicit conversion from data type image to xml is not allowed'
*/

declare @xml xml
select @xml = CAST(cast(imageCol as varbinary(max))as xml) from x

    print ''
    print 'table xml'
    print cast(@xml as varchar(max))

-- limit the xml to only the node you want to use

select @xml = @xml.query('/root/users/user[@name="Eric"]') 

    print ''
    print 'xml node'
    print cast(@xml as varchar(max))

/*
    Note: use the data() method or get the exception:
    'Attribute may not appear outside of an element'
*/

    print ''
    print 'xml values'

declare @value varchar(max)
select @value = cast(@xml.query('data(/user/@name)') 
    as varchar(max))
print @value
-- output = 'Eric'

select @value = cast(@xml.query('data(/user/@type)') 
    as varchar(max))
print @value
-- output = '1'

select @value = cast(@xml.query('data(/user)') 
    as varchar(max))
print @value
-- output = 'B'

-- remove demo table
drop table x