Post reply

how to get varbinary value from xml variable

  • 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

    select x.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)

  • June 2, 2010 at 3:08 pm

    #1175596

    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

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

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