How to add Identity when inserting with OPENXML? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to add Identity when inserting with OPENXML?

Author

Topic

bangingtunes
Starting Member

23 Posts

Posted - 2003-01-31 : 10:45:10

Hi all!
How can i convert this code to insert the @ID indentity into the orderitems table, Im trying to insert the order and its orderitems in one call using openxml.

CREATE PROCEDURE dbo.oOrder_Add
(
@UserName VARCHAR(15),
@SoldBy VARCHAR(15),
@XMLString VARCHAR(8000)
)
AS

DECLARE @ID AS int
DECLARE @XMLDocument INT

EXEC sp_xml_preparedocument @XMLDocument OUTPUT, @XMLString

INSERT INTO Orders (UserName, SoldBy) Values (@UserName, @SoldBy)
SELECT @ID = @@identity

INSERT INTO OrderItems
SELECT *
FROM OPENXML (@XMLDocument,'/Order/OrderItems')
WITH OrderItems

EXEC sp_xml_removedocument @XMLDocument
GO

Thanks in advance

bangingtunes
Starting Member

23 Posts

Posted - 2003-01-31 : 19:25:01

Sussed it!

Heres the code in case anyone searching for this needs the answer

CREATE PROCEDURE dbo.oOrder_Add
(
@UserName VARCHAR(15),
@SoldBy VARCHAR(15),
@XMLString VARCHAR(8000)
)
AS

DECLARE @hDoc INT

DECLARE @ID AS int
INSERT INTO Orders (UserName, SoldBy) Values (@UserName, @SoldBy)
SELECT @ID = @@identity

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XMLString

INSERT INTO OrderItems
( OrderID, CatName, Price, Quantity)
SELECT
@ID, CatName, Price, Quantity
FROM OPENXML (@hDoc,'/Order/OrderItems', 2)
WITH OrderItems

EXEC sp_xml_removedocument @hDoc
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources