Cannot insert the value NULL into column: insertion through XML

Question

1.00/5 (1 vote)

See more:

Here is My SQL CODE

SQL

<pre>ALTER PROCEDURE [dbo].[uspVM_Visits](
	@Mode			    INT			  = 0	
   ,@VisitID		    INT			  = 0
   --,@VisitNo		    INT			  = 0
   ,@LocationID		    INT		      = 0
   --,@VisitType		    INT		      = 0
   ,@TotalVisitors		INT		      = 0
   ,@VisitDate	        DATETIME   = NULL
   ,@SchIntime	        SMALLDATETIME   = NULL
   ,@SchOutTime	       SMALLDATETIME  = NULL
   ,@InTime	            SMALLDATETIME   = NULL
   ,@OutTime	        SMALLDATETIME  = NULL
   ,@VisitDuration	    DECIMAL(18,2) = 0	
   ,@VisitStatus		INT			  = 0
   ,@PurposeID			INT				= 0
   ,@UserID			    INT				= 0 
   ,@Remarks	        VARCHAR(500) = ''
   ,@Company			VARCHAR(50)				= 0
   ,@VisitorsList		NVARCHAR(MAX)	= ''
   ,@VisitMembersList		NVARCHAR(MAX)	= ''
   )
 
AS
SET NOCOUNT ON;
SET DATEFORMAT dmy;

DECLARE @idoc		 AS INT=0
DECLARE @Visitor AS TABLE	
( 
 FirstName	    VARCHAR(50)  	
,LastName	    VARCHAR(50) 
,Mobile	        VARCHAR(20) 
,Email	        VARCHAR(50)  
,Pass	    VARCHAR(50)  
--,Photo	        VARCHAR(100) 
--,Company	    VARCHAR(50) 
,Notes	        VARCHAR(500)
,CreateDate		SMALLDATETIME 
,Createby			INT			 
)

DECLARE @VisitMember AS TABLE
(
VisitID			INT
,MemberID		INT
)

DECLARE @VisitVisitor AS TABLE
(
VisitID			INT
,VisitorID		INT
)


DECLARE @Output AS TABLE
(ID	INT)

BEGIN --TRY	
IF @Mode = 1	--INSERT 
	   BEGIN
	   BEGIN TRANSACTION
	     INSERT INTO VM_Visits(
		-- VisitNo		
	    LocationID			
	    --,VisitType			
	    ,TotalVisitors	
	   	,VisitDate	    
		--,SchIntime	    
	    --,SchOutTime	    	
	    ,InTime	        
	   	--,OutTime	    
        --,VisitDuration			      
		,VisitStatus	
		,PurposeID		
	    ,Createby
		,CreateDate	
		,Remarks		
	     )  
	     VALUES(
		-- @VisitNo		 	      
	    @LocationID		
		--,@VisitType		
		,@TotalVisitors	
		,@VisitDate
		--,@SchIntime	    
		--,@SchOutTime	    
		,@InTime        
		--,@OutTime	    
		--,@VisitDuration	
		,@VisitStatus	
		,@PurposeID
		,@UserID		
		,GETDATE()	
		,@Remarks		 
		 )

		 SET @VisitID = SCOPE_IDENTITY()
		
		 --VisitNo is same as that of VisitID
		 UPDATE VM_Visits
		 SET VisitNo = @VisitID
		 WHERE VisitID = @VisitID

		 --Inserting Visitor details
		 IF DATALENGTH(@VisitorsList)>0
		  BEGIN
			EXEC sp_xml_preparedocument @idoc OUTPUT, @VisitorsList
			INSERT INTO  @Visitor (FirstName,LastName,Mobile,Pass,Email,--Photo,Company,
			Notes,CreateDate,Createby)  	
			SELECT FirstName,LastName,Mobile,Pass,Email,--Photo,Company,
			Notes,GETDATE(),@UserID
			FROM OPENXML (@idoc,'/dsData/dtVisitors',2)	
			WITH
			 (					
				 FirstName	    VARCHAR(50)  	
				,LastName	    VARCHAR(50) 
				,Mobile	        VARCHAR(20)
				,Pass	    VARCHAR(50)   
				,Email	        VARCHAR(50)  
				--,Photo	        VARCHAR(100) 
				--,Company	    VARCHAR(50) 
				,Notes	        VARCHAR(500) 
				--,Createby			INT	
			 )
			EXEC sp_xml_removedocument @idoc						
			INSERT INTO  VM_Visitors(FirstName,LastName,Company,Mobile,Pass,Email,--Photo,
			Notes,CreateDate,Createby)	
			-- Storing latest generated Visitor ID in temp table Output (INSERTED operation)
			OUTPUT INSERTED.VisitorID INTO @output			
			SELECT FirstName,LastName,@Company,Mobile,Pass,Email,--Photo,
			Notes,CreateDate,@UserID
			FROM @Visitor

			SELECT * FROM VM_Visitors

			SET @idoc=0;
										
			INSERT INTO  VM_VisitVisitors(VisitID, VisitorID, InTime) 
			SELECT @VisitID, ID, @InTime
			FROM @Output
		END
		
		--Inserting multiple Host details
		IF DATALENGTH(@VisitMembersList)>0
		  BEGIN
			EXEC sp_xml_preparedocument @idoc OUTPUT, @VisitMembersList
			INSERT INTO  @VisitMember (VisitID, MemberID)  	
			SELECT @VisitID, MemberID
			FROM OPENXML (@idoc,'/dsData/dtVisitMembers',2)	
			WITH
			 (					
				 MemberID	INT
			 )
			EXEC sp_xml_removedocument @idoc						
			INSERT INTO  VM_VisitMembers(VisitID, MemberID) 
			SELECT VisitID, MemberID
			FROM @VisitMember

			SET @idoc = 0;
		END
	    COMMIT TRANSACTION 
	  END

These are My inputs:

SQL

<pre>[dbo].[uspVM_Visits]
		@Mode = 1,
		@VisitID = 0,
		@LocationID = 5,
		@TotalVisitors = 2,
		@VisitDate = '2017-08-24 00:00:00',
		@InTime = '2017-08-24 11:13:00',
		@VisitDuration = 0.0,
		@VisitStatus = 1,
		@PurposeID = 1,
		@UserID = 1,
		@Remarks = 'dgy',
		@Company = 'Cynosure',
		@VisitorsList = N'{<dsData>  <dtVisitors>    <FirstName>demo1</FirstName>    <LastName>last1</LastName>    <Mobile>1234567890</Mobile><Pass>PASS 01</Pass>    <Email>demo1@gmail.com</Email>    <Notes />  </dtVisitors>  <dtVisitors>    <FirstName>demo 2</FirstName>    <LastName>last 2</LastName>    <Mobile>123456809</Mobile>    <Pass>PASS 02</Pass>    <Email>demo2@gmail.com</Email>    <Notes />  </dtVisitors>  <dtVisitMembers>    <MemberID>1</MemberID>  </dtVisitMembers>  <dtVisitMembers>    <MemberID>4</MemberID>  </dtVisitMembers></dsData>}',
		@VisitMembersList = N'{<dsData>  <dtVisitors>    <FirstName>demo1</FirstName>    <LastName>last1</LastName>    <Mobile>1234567890</Mobile><Pass>PASS 01</Pass>    <Email>demo1@gmail.com</Email>    <Notes />  </dtVisitors>  <dtVisitors>    <FirstName>demo 2</FirstName>    <LastName>last 2</LastName>    <Mobile>123456809</Mobile>    <Pass>PASS 02</Pass>    <Email>demo2@gmail.com</Email>    <Notes />  </dtVisitors>  <dtVisitMembers>    <MemberID>1</MemberID>  </dtVisitMembers>  <dtVisitMembers>    <MemberID>4</MemberID>  </dtVisitMembers></dsData>}'

I am getting this error:

The XML parse error 0xc00ce556 occurred on line number 1, near the XML text "{<dsData>  <dtVisitors>    <FirstName>demo1</FirstName>    <LastName>last1</LastName>    <Mobile>1234567890</Mobile><Pass>PASS 01</Pass>    <Email>demo1@gmail.com</Email>    <Notes />  </dtVisitors>  <dtVisitors>    <FirstName>demo 2</FirstName>    <LastName>last 2</LastName>    <Mobile>123456809</Mobile>    <Pass>PASS 02</Pass>    <Email>demo2@gmail.com</Email>    <Notes />  </dtVisitors>  <dtVisitMembers>    <MemberID>1</MemberID>  </dtVisitMembers>  <dtVisitMembers>    <MemberID>4</MemberID>  </dtVisitMembers></dsData>}".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Invalid at the top level of the document.'.
Msg 8179, Level 16, State 5, Procedure uspVM_Visits, Line 114
Could not find prepared statement with handle 0.
The statement has been terminated.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.
The XML parse error 0xc00ce556 occurred on line number 1, near the XML text "{<dsData>  <dtVisitors>    <FirstName>demo1</FirstName>    <LastName>last1</LastName>    <Mobile>1234567890</Mobile><Pass>PASS 01</Pass>    <Email>demo1@gmail.com</Email>    <Notes />  </dtVisitors>  <dtVisitors>    <FirstName>demo 2</FirstName>    <LastName>last 2</LastName>    <Mobile>123456809</Mobile>    <Pass>PASS 02</Pass>    <Email>demo2@gmail.com</Email>    <Notes />  </dtVisitors>  <dtVisitMembers>    <MemberID>1</MemberID>  </dtVisitMembers>  <dtVisitMembers>    <MemberID>4</MemberID>  </dtVisitMembers></dsData>}".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Invalid at the top level of the document.'.
Msg 8179, Level 16, State 5, Procedure uspVM_Visits, Line 153
Could not find prepared statement with handle 0.
The statement has been terminated.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

What I have tried:

I have tried changing 2 to 1 in

FROM OPENXML (@idoc,'/dsData/dtVisitMembers',2)

.
But it didn't work out.

I removed { } from the xml data.
Msg 515, Level 16, State 2, Procedure uspVM_Visits, Line 134
Cannot insert the value NULL into column 'FirstName', table 'VisiTrac.dbo.VM_Visitors'; column does not allow nulls. INSERT fails

Posted 23-Aug-17 20:57pm

prapti.n3

Updated 23-Aug-17 21:35pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Thomas Daniels · Accepted Answer · 2017-08-23T21:05:00

Solution 1

Your XML is wrapped in { and }. They should be the cause of the error, because the rest of the text is valid XML.

Posted 23-Aug-17 21:05pm

Thomas Daniels

Comments

prapti.n3 24-Aug-17 3:17am

I removed them. Then I am getting this error:

Msg 515, Level 16, State 2, Procedure uspVM_Visits, Line 134
Cannot insert the value NULL into column 'FirstName', table 'VisiTrac.dbo.VM_Visitors'; column does not allow nulls. INSERT fails.

Patrice T 24-Aug-17 3:23am

Use Improve question to update your question.
So that everyone can pay attention to this information.

prapti.n3 24-Aug-17 3:35am

I updated my question

Thomas Daniels 24-Aug-17 6:15am

I can't immediately see why that happens -- I'd recommend you to debug your queries. It can tell you where it goes wrong, and then you have a better idea about where to look.