|
adamf07
Starting Member
1 Post |
 Posted - 2009-06-11 : 09:10:15
|
| 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 asDECLARE @idoc intEXEC sp_xml_preparedocument @idoc OUTPUT, @XmlParmsSELECT *into #tmpParmsFROM 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 @idocBEGIN TRANSACTIONDELETE FROM ITGTicketsWHERE 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 #tmpParmsIF @@ERROR <> 0 BEGIN ROLLBACK RAISERROR('Something Went Wrong', 16, 1) ENDCOMMIT |
|
Insert new rows using stored procedure.