Convert ASCII Bytes from XML to VARCHAR 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'
&lt;ProgramWzgSignStates&gt;
    &lt;WzgSignStates&gt;
        &lt;Element&gt;
            &lt;id&gt;AQ_A07_1_280_F3~ZA&lt;/id&gt;
            &lt;text&gt;
                &lt;Element&gt;50&lt;/Element&gt;
                &lt;Element&gt;72&lt;/Element&gt;
                &lt;Element&gt;97&lt;/Element&gt;
                &lt;Element&gt;108&lt;/Element&gt;
                &lt;Element&gt;108&lt;/Element&gt;
                &lt;Element&gt;111&lt;/Element&gt;
            &lt;/text&gt;
        &lt;/Element&gt;
    &lt;/WzgSignStates&gt;
&lt;/ProgramWzgSignStates&gt;'

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?
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