Convert ASCII Bytes from XML to VARCHAR

Question

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?

Answer 1

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