0

I have XML data which includes strings as byte array, which I'd like to convert to VARCHAR in SQL Server 2012. Example snippet with text="2Hallo" and the code I tried:

DECLARE @testxml XML = N'
<ProgramWzgSignStates>
    <WzgSignStates>
        <Element>
            <id>AQ_A07_1_280_F3~ZA</id>
            <text>
                <Element>50</Element>
                <Element>72</Element>
                <Element>97</Element>
                <Element>108</Element>
                <Element>108</Element>
                <Element>111</Element>
            </text>
        </Element>
    </WzgSignStates>
</ProgramWzgSignStates>'

SELECT 
 Tab.Col.value('id[1]', 'varchar(50)') AS Id,
 Tab.Col.value('text[1]', 'nvarchar(MAX)') AS TextAsNVarChar
,Tab.Col.value('text[1]', 'varbinary(MAX)') AS TextAsVarBinary
,Convert(NVARCHAR(255), Tab.Col.value('text[1]', 'varbinary(MAX)')) AS TextUsingConvert
FROM @testxml.nodes('/ProgramWzgSignStates/WzgSignStates/Element') AS Tab(Col);

Which results in the following output:

Id                 TextAsNVarChar  TextAsVarBinary TextUsingConvert
AQ_A07_1_280_F3~ZA 507297108108111 NULL            NULL

Converting to NVARCHAR results in the ascii codes to be concatenated, while using VARBINARY returns NULL.

How can I retrieve the string 2Hallo as VARCHAR/NVARCHAR?

  • What is the encoding of the original string? And why is it provided in such a manner? Seems odd, unless it is UTF-8 or a specific Code Page or something. – Solomon Rutzky Nov 3 '15 at 6:19
2

I think you should parse the text/Elements ,so that you can obtain the desired string.

SELECT 
 Tab.Col.value('id[1]', 'varchar(50)') AS Id,
 Tab.Col.value('text[1]', 'nvarchar(MAX)') AS TextAsNVarChar
,(SELECT CHAR(t.u.value('.','nvarchar(max)'))
        FROM Tab.Col.nodes('text/Element') AS t(u)
        FOR XML PATH('')
 )TextAsNVarChar_New
FROM @testxml.nodes('/ProgramWzgSignStates/WzgSignStates/Element') AS Tab(Col)

I used FOR XML PATH to obtain 2Hallo

Id                 TextAsNVarChar    TextAsNVarChar_New
AQ_A07_1_280_F3~ZA 507297108108111   2Hallo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.