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?

Answer 1

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.