openXML with Identity insert


    I have problem with openXML, when I am trying to insert records in table which has identity column as primary key. I tried to use  SCOPE_IDENTITY but that did not work. The scenarion is as follows.

Table A (A_ID int IDENTITY, Bal float, SecID varchar(36))

Table B(A_ID, Pct float)

I have multiple records to be inserted oin Table A reading from an XML file. Also the IDS in Table A act as foreign key in Table B so multiple records need to be inserted in table B based on IDs inTable A.

How can I do this when I am using openXML?
Who is Participating?
HilaireConnect With a Mentor Commented:
Hi LTSave,
Here's a solution using a view and an instead of trigger

-- create then view
create view vw_tech as select bal, secid, pct from tableA A inner join tableB B on A.A_ID = B.A_ID
-- create the INSTEAD OF trigger
create trigger utg_ins_vw_tech on vw_tech
instead of insert as
declare c1 cursor for select distinct Bal, SecID from inserted
declare @bal float, @secID varchar(36)
open C1
fetch c1 into @bal, @secID
while @@fetch_status = 0
      insert TableA(Bal, SecID) values(@bal, @secID)
      insert TableB(A_ID, Pct) select scope_identity(), Pct from inserted where Bal = @Bal and SecID = @SecID
      fetch c1 into @bal, @secID
close c1
deallocate c1      

-- Now you can insert directly from you openXML select statement

sp_xml_preparedocument .....
insert into vw_tech select * from OPENXML (......)
sp_xml_removedocument ....

It's not a perfect solution since it still needs a cursor (or some kind of loop to get each identity separately), but I use it and am rather happy with it ...


Anthony PerkinsCommented:
Strictly speaking, this problem is unrelated to OPENXML and has to do with INSERTing into a column with the IDENTITY attribute on.

In order to do this, you must first do:
LTSaveAuthor Commented:
I know that IDENTITY_INSERT has to be turned on for forced insert to IDENTITY column and turned off later.This works absolutely fine.

I am trying to copy to those tables from XML file using openXML. openXML does not read the columns which are defined as IDENTITY and that is creating problem, in this case IDENTITY_INSERT is of no use.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Anthony PerkinsCommented:
>>openXML does not read the columns which are defined as IDENTITY and that is creating problem<<
I beg to differ with you.  OPENXML does not "read" columns, it selects from nodes in an Xml document and therefore has no "knowledge" whether the value read is even intended for an Identity column.

If I was to speculate from your question, I would guess you are trying to add values to the second table using the IDENTITY value generated by the first insert statement (which happens to use OPENXML).  The Scope_Identity() function will only return the last row added.  If that is the what you doing than you will need to query the first table for the Identity values added in order to use them in the second query.

I suggest you post the stored procedure with the OPENXML as it stands now, what you need or is missing and perhaps we can help more.
LTSaveAuthor Commented:
Please check this site and many others
They mention the problem clearly.
Anthony PerkinsCommented:
Sorry, no interest.  I don't have a problem using OPENXML.

Good luck.
Note : INSTEAD OF trigges require SQL 2000 (not available in SQL Server 7)
LTSaveAuthor Commented:
Thanks Hilaire, it seems like your solution will work.
However I found a simple solution, by specifying schema in the openXML clause I could finalyy achieve my purpose.
>>However I found a simple solution<<
If you have found a better solution on your own, I'd be glad to know how it works !

Since you found an answer by yourself, you could also ask for a points refund.

Anthony PerkinsCommented:

The problem was that the questioner was using WITH TableName instead of the preferred and more widely used WITH SchemaDeclaration.  

This example clearly illustrates the problem:
Declare @iDoc integer

Create table #Temp (
            CategoryID integer IDENTITY(1,1),
            CategoryName varchar(50))

EXEC sp_xml_preparedocument @iDoc OUTPUT,
                              <CategoryName>My Category Name</CategoryName>

Select      *
From      OpenXml(@idoc, 'Categories/Category', 2) With #Temp

EXEC sp_xml_removedocument @idoc

Drop Table #Temp

CategoryID  CategoryName                                      
----------- --------------------------------------------------
9           My Category Name

Now, remove the IDENTITY attribute from the temp table and you get:

My Category Name

The solution is to use SchemaDeclaration like most of us do.
Thanks for the explaination Anthony.
I always use SchemaDeclaration and can't remember ever using this 'WITH TableName' method/syntax.
But even with the SchemaDeclaration, you still have to insert the records separately.

I happen to use the "view + instead of trigger" trick to avoid looping the OPENXML resultset.
Although it does not avoid the cursor to get the newly inserted identities, I found it rather efficient and useful because the code is not too verbose.
What do you think ?
Anthony PerkinsCommented:
>>I always use SchemaDeclaration and can't remember ever using this 'WITH TableName' method/syntax.<<
Same here and I knew you did.  I cannot think of any reason why you would even want to use WITH TableName.  It is like people who insist on using Select *, instead of taking the trouble of explicitly naming the columns.

>>What do you think ? <<
I don't believe I have ever had a need for that, but I will keep it in mind.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.