Невозможно вставить значение NULL в столбец: вставка через XML
Вот мой 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
Это мои входные данные:
<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>}'
Я получаю эту ошибку:
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.
Что я уже пробовал:
Я пробовал изменить 2 на 1 в
FROM OPENXML (@idoc,'/dsData/dtVisitMembers',2).
Но из этого ничего не вышло.
Я удалил { } из xml-данных.
Msg 515, Уровень 16, состояние 2, процедура uspVM_Visits, строка 134
Невозможно вставить значение NULL в столбец 'FirstName', таблицу 'VisiTrac.dbo.VM_Visitors'; столбец не допускает значений null. Вставить не удается