openXML >> using @@identity - SQL Server Forums

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

Analysis Services (2000)

openXML >> using @@identity

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-23 : 07:56:32

Niels writes "i'm working with openXML and wan't to insert in 2 tables. Also I want the second table to use a identity from the first insert .. is this possible ??

DECLARE @hDoc int
DECLARE @cDoc varchar(8000)
DECLARE @Exp1 int

SET @cDOC = '<item page_ID="3" cluster_ID="3" name="element" type="cluster" title="Element"><item type="simple" name="systeem" title="Systeem">Valuw<item></item>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @cDoc

INSERT INTO Cluster
SELECT *
FROM OPENXML(@hDOC, '/item', 3)
WITH
(
Page_ID int '@page_ID',
Name nvarchar(30) '@name'

)

SELECT @@IDENTITY

INSERT INTO Veld_Default (Name,Txt)
SELECT *
FROM OPENXML(@hDOC, '/item/item[@type = "simple"]', 3)
WITH
(
Cluster_ID int [**** here I neeed the @@identity],
Name nvarchar(30) '@name',
Txt nvarchar(30) '.'

)

EXEC sp_xml_removedocument @hdoc
go
SELECT * FROM Cluster
go"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 12:46:37

You need to put @@IDENTITY into a variable:

DECLARE @SomeVariable INT

SELECT @SomeVariable = @@IDENTITY

Then use the variable in the INSERT statement.

Tara

chadmat
The Chadinator

1974 Posts

Posted - 2003-09-23 : 12:53:08

Again,

I would suggest using Scope_Identity().

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-23 : 13:07:06

Yes, that is true.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources