无法将值NULL插入列：通过XML插入 [英] Cannot insert the value NULL into column: insertion through XML
本文介绍了无法将值NULL插入列：通过XML插入的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我的SQL代码
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) 。
但它没有用完。
我从xml数据中删除了{}。
消息515，级别16，状态2，过程uspVM_Visits，第134行
无法将值NULL插入列'FirstName'，表'VisiTrac.dbo.VM_Visitors';列不允许空值。 INSERT失败
.
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
推荐答案
您的XML包含在{和}。它们应该是错误的原因，因为文本的其余部分是有效的XML。
Your XML is wrapped in{and}. They should be the cause of the error, because the rest of the text is valid XML.
这篇关于无法将值NULL插入列：通过XML插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
