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

Question

I'm writing following SQL procedure:

ALTER PROCEDURE [dbo].[spc_InsertSubjectToContentRelation]
(
    @pCourseGUID XML, 
    @pSubjectId XML,
    @pAssessmentIds XML,
    @pVideoIds XML
)
AS
BEGIN
SET NOCOUNT ON;

    DECLARE @CourseGUID NVARCHAR(50);
    DECLARE @SubjectId NVARCHAR(50);
    DECLARE @AssessmentIds NVARCHAR(MAX);
    DECLARE @VideoIds NVARCHAR(MAX);

    SET @CourseGUID = Convert(NVARCHAR,@pCourseGUID);
    SET @SubjectId = Convert(NVARCHAR,@pSubjectId);
    SET @AssessmentIds = Convert(NVARCHAR,@pAssessmentIds);
    SET @VideoIds = Convert(NVARCHAR,@pVideoIds);

    INSERT INTO SubjectToAssessmentAndVideoRelation VALUES (@pCourseGUID, @pAssessmentIds, @pAssessmentIds, @pVideoIds)
END

When I'm running this procedure I'm getting the error:

Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query. How can I resolve this?

Why would you declare the parameters as one type if that's not the type that your procedure wants? — Damien_The_Unbeliever, Jun 25 '13 at 07:29
@Damien_The_Unbeliever because the type of columns in the table is not of xml type — Jainendra, Jun 25 '13 at 07:32

score 2 · Accepted Answer · answered Jun 25 '13 at 07:30

You run the conversions and then ignore the converted values and continue to use the original parameter values. You might have wanted this:

INSERT INTO SubjectToAssessmentAndVideoRelation VALUES (
     @CourseGUID, @AssessmentIds, @AssessmentIds, @VideoIds)

(No p after the @, and thus using the local variables rather than the parameters)

But I continue to be mystified why you declare your procedure to want xml parameters if it's going to immediately convert them to nvarchars.

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

1 Answers1