Implicit conversion from data type XML to NVARCHAR T-SQL error

How to fix the SQL Server Error Implicit conversion from data type XML to NVARCHAR is not allowed?

How to convert XML into NVARCHAR with SQL Server? To avoid the SQL Server error conversion from XML to NVARCHAR. The exact message is “Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.”. With SQL Server data, it is useful to combine numerical columns, alphanumeric strings, and XML typed columns.

In addition, to easily query XML data, it’s very convenient to convert this XML column to an NVARCHAR one, so it is possible, for example, to filter its data. In this post, we do not use an XML column but an XML variable and an NVARCHAR variable.

Table of Contents

1. SQL Server Error Implicit conversion from data type XML to NVARCHAR

First, let’s consider this very simple XML example, a Tech company have three employees. It could be a high potential startup, yes! Note that the code works the same way with 30 employees. Furthermore, to manage a list of thousands of employees, then use NVARCHAR(MAX), and consider storing data into tables.

<company>
 <employee>SMITH</employee>
 <employee>WILKINS</employee>
 <employee>JOHNSON</employee>
</company>

2. T-SQL query to convert XML into NVARCHAR datatype

For example, if you use the XML typed column without explicit conversion to NVARCHAR type.

DECLARE @My_Xml 	XML;
DECLARE @My_String 	NVARCHAR(500);

SET 	@My_Xml =
    '<enterprise>
        <employee>SMITH</employee>
        <employee>WILKINS</employee>
        <employee>JOHNSON</employee>
    </enterprise>';
    
SET 	@My_String = @My_Xml ;
SELECT 	@My_String;

After executing the query, the SQL Server displays this error:

Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.

3. Convert an XML column to NVARCHAR datatype without error

So obviously the solution is to use the CONVERT() SQL Server function to convert the XML type to NVARCHAR or VARCHAR type. So, this simple query allows you to query a table containing XML columns and even do search with LIKE keywords for example.

DECLARE @My_Xml 	XML;
DECLARE @My_String 	NVARCHAR(500);

SET 	@My_Xml =
'<enterprise>
    <employee>SMITH</employee>
    <employee>WILKINS</employee>
    <employee>JOHNSON</employee>
</enterprise>';

SET 	@My_String = CONVERT(VARCHAR(500), @My_Xml);
SELECT 	@My_String;

4. Conclusion on XML conversion error with SQL Server

To conclude, the CONVERT SQL function allows to easily convert and manipulate XML tags. This article explains how to avoid the SQL Server classical conversion error from data type XML to Nvarchar. To go further check also the data types details in the official documentation from SQL Server.

Finally, this post explains how to avoid SQL Server Implicit conversion from data type XML to nvarchar Error and convert XML into NVARCHAR, want to learn more about SQL Server string management? Check out the section below with more tutorials.