SQL Server Implicit conversion from data type XML to nvarchar

How to convert XML into NVARCHAR with SQL Server? To avoid this error “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 query easily the XML data, it’s very convenient to convert this XML column to an NVARCHAR one, so you can, for example, filter it. In this post, we do not use an XML column but an XML variable and an NVARCHAR variable.

Error on SQL Server :Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.”

First of all, 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 employees, then use NVARCHAR(MAX), and consider storing data into tables.

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

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.

How to convert a SQL Server XML column to NVARCHAR using the SQL CONVERT function

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;

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? Read this article on how to remove a new line from a text in SQL Server.

To conclude, the CONVERT SQL function allows to easily convert and manipulate XML tags.

Also, you liked this post? Share it 🙂 

Be the first to comment

Leave a Reply