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?

• Solomon Rutzky: 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.

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