Click here to Skip to main content
14,099,478 members
Rate this:
 
Please Sign up or sign in to vote.
Here is My SQL CODE

<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:

<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
Updated 23-Aug-17 21:35pm
v2

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

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.
   
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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190518.1 | Last Updated 24 Aug 2017
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100