0

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
2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by adamf07
0

Ah, you were correct. It was a formatting error that was happening before the data ever got to the DB. Thanks!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.