how to get varbinary value from xml variable

Question

how to get varbinary value from xml variable

Matjaz Justin

Mr or Mrs. 500

Points: 598
More actions
May 20, 2010 at 7:15 am

#98663

I have a case below. In xml data I have varbinary value from hashbytes function but i can't get this value from xml variable data. Does anybody has an idea how to do that?
...,
x.TableColumn.value('../@HashConcatenateString[1]', 'varbinary(20)') AS HashConcatenateString,
...
is null.
declare @x xml
set @x = '
<IS21_INDEXES>
<Index Index_name="PK_od_banke_zc" Table_Name="OD_BANKE_ZC" Type="1" Is_unique="1" Is_primary_key="1" is_unique_constraint="0">
<HashConcatenateString>186D975F24EA254A404FFCE4F27A7155A68D1DEC=</HashConcatenateString>
<Field Column_name="POSLOVNI_PARTNER" Key_ordinal="1" Is_descending_key="0" Is_included_column="0" />
</Index>
</IS21_INDEXES>'
select @x
/*
<IS21_INDEXES>
<Index Index_name="PK_od_banke_zc" Table_Name="OD_BANKE_ZC" Type="1" Is_unique="1" Is_primary_key="1" is_unique_constraint="0">
<HashConcatenateString>186D975F24EA254A404FFCE4F27A7155A68D1DEC=</HashConcatenateString>
<Field Column_name="POSLOVNI_PARTNER" Key_ordinal="1" Is_descending_key="0" Is_included_column="0" />
</Index>
</IS21_INDEXES>
*/
-- select hashbytes('SHA1', '891gqw%$#%&') as HashConcatenateString
selectx.TableColumn.value('../@Table_Name[1]', 'varchar(100)') AS Table_name,
x.TableColumn.value('../@Index_name[1]', 'varchar(100)') AS Index_name,
x.TableColumn.value('../@Type[1]', 'int') AS Type,
x.TableColumn.value('../@Is_unique[1]', 'tinyint') AS Is_unique,
x.TableColumn.value('../@Is_primary_key[1]', 'tinyint') AS Is_primary_key,
x.TableColumn.value('../@HashConcatenateString[1]', 'varbinary(20)') AS HashConcatenateString,
x.TableColumn.value('@Column_name[1]', 'varchar(100)') AS Column_name,
x.TableColumn.value('@Key_ordinal[1]', 'smallint') AS Key_ordinal,
x.TableColumn.value('@Is_descending_key[1]', 'tinyint') AS Is_descending_key,
x.TableColumn.value('@Is_included_column[1]', 'tinyint') AS Is_included_column
from @x.nodes('//IS21_INDEXES/Index/Field') AS x(TableColumn)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 1

You have 2 very small problems:

First, there is no need to cast HashConcatenateString as varbinary(20) because it is already spelled out and therefore, if you cast it to varbinary(20) then you will get not what you see in xml. In your example,

186D975F24EA254A404FFCE4F27A7155A68D1DEC=

will become

0xD7CE83F7BE45DB8100DB9E00E34E05142138176EC0EF

when cast to varbinary(20) and I am not sure you want it this way.

Second, you have a typo: '../@HashConcatenateString[1]' should be '../HashConcatenateString[1]' because you need to query the inner text rather than attribute value of HashConcatenateString node.

Oleg