[Last Call] Learn how to a build a cloud-first strategyRegister Now


openXML with Identity insert

Posted on 2005-04-19
Medium Priority
Last Modified: 2011-04-14

    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?
Question by:LTSave
  • 5
  • 4
  • 3
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13819973
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:

Author Comment

ID: 13820886
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.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13824198
>>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.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Author Comment

ID: 13824367
Please check this site and many others
They mention the problem clearly.
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13824489
Sorry, no interest.  I don't have a problem using OPENXML.

Good luck.
LVL 26

Accepted Solution

Hilaire earned 2000 total points
ID: 13825182
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 ...


LVL 26

Expert Comment

ID: 13825291
Note : INSTEAD OF trigges require SQL 2000 (not available in SQL Server 7)

Author Comment

ID: 13825368
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.
LVL 26

Expert Comment

ID: 13825510
>>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.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 13826570

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.
LVL 26

Expert Comment

ID: 13826791
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 ?
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13826859
>>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.

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question