xml in storedprocedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

xml in storedprocedure

hi, i have 2 tables. i have to insert data in to one table by comparing the data with second table. how to do this with storedprocedure and the input to the storedprocedure is xmlstring. the querry have written is alter procedure insertProcedure
@xmlString varchar(500),
@DeptID int output
DECLARE @idoc int DECLARE @GUID uniqueidentifier
set @DID = @DeptID
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlString select @DeptID = DeptID from Department where DeptName = @DeptName
insert into Employee (EmpId,EmpName,DeptID,Age)
FROM OPENXML (@idoc, ‘/Employee’,2)
with (EmpID uniqueidentifier, EmpName varchar(255),DeptID int,Age int)
insert into Employee (EmpId,EmpName,DeptID,Age) values (@GUID,@EmpName,@DeptID,@Age) EXEC sp_xml_removedocument @idoc
can anyone help me in figuring out the problem.
A quick scan of the code above [since you did not povide any error messages or the actual problem] will reveal that the following lines
insert into Employee (EmpId,EmpName,DeptID,Age) values (@GUID,@EmpName,@DeptID,@Age) EXEC sp_xml_removedocument @idoc

will not compile/run because the variables @GUID,@EmpName,@DeptID,@Age and @idoc are scoped only within the stored procedure insertprocedure and are not available to the insert statement nor to the line invoking the sp_xml_removedocument. These 2 lines should appear before the GO statement at the end of the stored procedure definition if that is the intended logic. Otherwise, it would be nice if you provided the actual problem/error.
Nathan H. Omukwenyi