How to cast context_info return value to xml

Question

How can I do cast/convert context_info return value data to XML?

The first query works fine and query2 does not work

-- Query 1
DECLARE @xml XML = '<row User="system" Log="1" Culture="1"/>'
DECLARE @varB VARBINARY(128);

SET @varB = CAST(@xml AS VARBINARY(128)) 

SELECT @xml, CAST(@varB AS XML)

-- Query 2
DECLARE @xml XML = '<row User="system" Log="1" Culture="1"/>'
DECLARE @varB VARBINARY(128);

SET @varB = CAST(@xml AS VARBINARY(128)) 

SET CONTEXT_INFO  @varB

SELECT @xml, CAST(@varB AS XML), CONTEXT_INFO(), CAST(CONTEXT_INFO() AS XML)

Error:

XML parsing: line 1, character 41, illegal xml character

`CONTEXT_INFO` is `binary(128)` rather than `varbinary(128)`. The parsing error is due to the trailing binary zeros being evaluated. What SQL Server version are you using? — Dan Guzman, May 12 '19 at 11:22

score 0 · Accepted Answer · answered May 12 '19 at 12:35

CONTEXT_INFO is fixed-length binary(128) rather than varbinary(128). The parsing error is due to the trailing binary zeros being evaluated.

Instead of CONTEXT_INFO, consider using SESSION_CONTEXT (available in SQL Server 2016 onwards). SESSION_CONTEXT allows one to store one or more key/value pairs with a sysname key type (nvarchar(128)) and a value of type sql_variant with sp_set_session_context. The value can be retrieved with the SESSION_CONTEXT function.

So if your underlying objective is to store and retrieve the User, Log, and Culture values, you could create SESSION_CONTEXT entries for the 3 values instead of XML:

EXEC sp_set_session_context @key= N'User', @value= 'system';
EXEC sp_set_session_context @key= N'Log', @value= 1;
EXEC sp_set_session_context @key= N'Culture', @value= 1;
SELECT SESSION_CONTEXT(N'User'), SESSION_CONTEXT(N'Log'), SESSION_CONTEXT(N'Culture');

You could alternatively use XML similarly to your original attempt but, since XML is not a permitted sql_variant type, you'll need to add the XML value as varchar/nvarchar and CAST to XML:

DECLARE @xml XML = '<row User="system" Log="1" Culture="1"/>';
DECLARE @xmlVarchar varchar(8000) = CAST(@xml AS varchar(8000));
EXEC sp_set_session_context @key= N'xml', @value= @xmlVarchar;
SELECT CAST(CAST(SESSION_CONTEXT(N'xml') AS varchar(8000)) AS xml);

How to cast context_info return value to xml

1 Answers1