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
)
as
DECLARE @idoc int DECLARE @GUID uniqueidentifier
SET @GUID = NEWID() DECLARE @DID int
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)
SELECT *
FROM OPENXML (@idoc, ‘/Employee’,2)
with (EmpID uniqueidentifier, EmpName varchar(255),DeptID int,Age int)
go
insert into Employee (EmpId,EmpName,DeptID,Age) values (@GUID,@EmpName,@DeptID,@Age) EXEC sp_xml_removedocument @idoc
go
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

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

---

]]>