Hey all, I'm trying to insert a bunch of rows from an xml document into an SQL database. I have a stored procedure that I'm calling from my program after I generate the XML string and pass it into the SP. It runs with no (visible) errors, but when I look at the table, no new rows have been inserted. I'm really stumped, as I'm fairly new to SQL Server and Stored Procedures, and not very experienced with SQL either. Am I doing something wrong here? (SQL below)
Anyway, here's my query. Basically it takes in a string of XML, transforms it into a temporary table (#tmpParms), deletes everything in the current table that is in the temporary table, then inserts everything in the temporary table.
ALTER procedure [dbo].[HOP_ITGTickets_Reinsert] @XMLParms text as
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @XmlParms
SELECT *
into #tmpParms
FROM OPENXML (@idoc, '/InvestAdminDataSet/HOP_Tickets_From_Excel',2)
WITH (ITGNumber int
,RequestType varchar(50)
,RequestStatus varchar(50)
,CreatedBy varchar(50)
,ApplicationName varchar(100)
,LawsonProject varchar(100)
,PlannedAcceptanceDate varchar(20)
,PlannedProductionDate varchar(20)
,WorkgroupCoordinator varchar(50)
,ALLApprovalsComplete varchar(20)
,BusinessApprover varchar(50)
,ITApprover varchar(50)
,LastUpdated varchar(20)
,LeadDeveloper varchar(50)
,SubmitDate varchar(20)
,RequestName varchar(200) )
EXEC sp_xml_removedocument @idoc
BEGIN TRANSACTION
DELETE FROM ITGTickets
WHERE ITGNumber IN (SELECT ITGNumber FROM #tmpParms)
insert into dbo.ITGTickets (ITGNumber,RequestType, RequestStatus, CreatedBy, ApplicationName,
LawsonProject, PlannedAcceptanceDate, PlannedProductionDate, WorkgroupCoordinator, ALLApprovalsComplete,
BusinessApprover, ITApprover, LastUpdated, LeadDeveloper, SubmitDate, RequestName)
select *
from #tmpParms
IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR('Something Went Wrong', 16, 1)
END
COMMIT