If you’re getting SQL Server error Msg 529 that reads something like Explicit conversion from data type int to xml is not allowed, it’s probably because you’re trying to perform a data type conversion that’s not permitted.

SQL Server doesn’t allow certain conversions. If you try to perform such a conversion, you’ll get this error.

Example of Error

Here’s an example of code that produces the error:

SELECT CAST(10 AS xml);

Result:

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to xml is not allowed.

Here, we tried to convert the number 10 to the xml type.

SQL Server doesn’t allow such a conversion and so it returned an error.

We get the same error if we try to convert that value to a date type:

SELECT CAST(10 AS date);

Result:

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to date is not allowed.

You might assume that we could get rid of the error by using the TRY_CAST() function instead. But that assumption would be incorrect:

SELECT TRY_CAST(10 AS xml);

Result:

Msg 529, Level 16, State 2, Line 1
Explicit conversion from data type int to xml is not allowed.

Although the TRY_CAST() function can normally be used to return NULL instead of an error, that doesn’t apply for this particular error.

When a conversion fails due to the conversion not being permitted, then even TRY_CAST() (and TRY_CONVERT()) will return an error.

Solution

To fix this, you’d need to change either the original type or the destination type (or both).

If you’re passing a column, check that you’ve got the right column. Same if you’re passing a variable – check that it’s the right variable.

For example, the following conversion succeeds:

SELECT CAST('{a:10}' AS xml);

Result:

{a:10}

And the following also succeeds:

SELECT CAST(10 AS char(2));

Result:

10

Depending on the actual value, you could try converting the original value to an accepted type.

For example, we could change our original XML error producing code to the following non-error producing code:

SELECT CAST(CAST(10 AS varchar) AS xml);

Result:

10

In this case, the operation didn’t validate that the result is a valid XML document. It simply converted the original value into the xml type.

However, this might not always work, depending on the value you’re trying to convert. For example, our date example still produces an error (albeit, a different error):

SELECT CAST(CAST(10 AS varchar) AS date);

Result:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

In this case, we get error Msg 241, which is a different error. In this case, the error is not due to the conversion being disallowed. It’s due to the actual value causing problems. In our case, SQL Server can’t convert the number 10 to a valid date. We would need to change the input value to a more meaningful value that represents a valid date.