Need help in XML insert -identity values

  • Hi all,

    I need your help I have a problem in inserting identity values into a table .The scenario is I have emp table (parent table) and emp_test (child table).the i/p values for emp table will be given as XML values i need to insert this records into emp table as well as into emp_test table forming the parent child relation.the problem is i'm not able to process the indentity values im getting only the last identity values while processing it .Herewith im sending my code for ur usage if anything found to be wrong pls correct me.

    code;

    --create sample table to test the script

    --If table already exists drop and recreate it

    if exists (select name from sys.tables where name='emp')

    drop table emp

    go

    create table emp (emp_id int identity(1,1) primary key,f_name varchar(30),l_name varchar(20),designation varchar(40),dob datetime)

    --If table already exists drop and recreate it

    if exists (select name from sys.tables where name='emp_test')

    drop table emp_test

    go

    create table emp_test (id int identity primary key ,emp_id int)

    --If procedure already exists drop and recreate it

    if exists (select name from sys.objects where name='prc_xmlinsert')

    drop procedure prc_xmlinsert

    go

    create procedure prc_xmlinsert

    (

    @i_xml varchar(4000) --I/p variable

    )

    /*

    sample excecution:

    ------------------

    exec prc_xmlinsert @i_xml ='<root>

    <Employee>

    <FirstName>Kevin</FirstName>

    <LastName>Goff</LastName>

    <Designation>Software Engg</Designation>

    <Dob>2010-03-30 13:23:13.307</Dob>

    </Employee>

    <Employee>

    <FirstName>Steve</FirstName>

    <LastName>Waugh</LastName>

    <Designation>Sr.Software Engg</Designation>

    <Dob>2010-03-30 13:23:13.307</Dob>

    </Employee>

    <Employee>

    <FirstName>Andy</FirstName>

    <LastName>Thomson</LastName>

    <Designation>Data Analyst</Designation>

    <Dob>2010-03-30 13:23:13.307</Dob>

    </Employee>

    </root>'

    */

    as

    begin

    --declare o/p variable for XML

    declare @i int,@l_error_no int

    declare @l_identity int

    exec sp_xml_preparedocument @i output,@i_xml

    BEGIN TRY

    BEGIN TRAN

    --Read the XML content and insert into table

    insert into emp SELECT FirstName,LastName,Designation,Dob FROM OPENXML (@i, '/root/Employee',2)

    with(Empid int,FirstName varchar(50),LastName varchar(50),Designation varchar(50),Dob datetime)

    select @l_identity=@@identity

    insert into emp_test values(@l_identity)

    EXEC sp_xml_removedocument @i

    COMMIT TRAN

    END TRY

    BEGIN CATCH

    -- insertion failed

    SET @l_error_no = 2000

    SELECT @l_error_no AS error_no

    ROLLBACK TRANSACTION

    RETURN

    END CATCH

    --insertion success

    SET @l_error_no = 1000

    SELECT @l_error_no AS error_no

    end

    Thanks In Advance

    Chandru

  • Hi Chandru,

    select @l_identity=@@identity

    will only return the last row and not all rows. You'd need to use the OUTPUT clause together with an intermediate table. See the code snippet below.

    Please note that I used XQuery instead of OPENXML. It provides more functionality (if needed).

    Side note: What is the reason to store the id values in a separate table? Sonds strange...

    DECLARE @MyTableVar TABLE( empid INT);

    INSERT INTO emp(f_name ,l_name ,designation ,dob)

    OUTPUT INSERTED.emp_id

    INTO @MyTableVar

    SELECT

    v.value('FirstName[1]','varchar(30)') AS FirstName,

    v.value('LastName[1]','varchar(20)') AS LastName,

    v.value('Designation[1]','varchar(40)') AS Designation,

    v.value('Dob[1]','datetime') AS Dob

    FROM @xml.nodes('root') T(c)

    CROSS APPLY

    t.c.nodes('Employee') U(v)

    INSERT INTO emp_test(emp_id)

    SELECT *

    FROM @MyTableVar

    SELECT *

    FROM emp

    SELECT *

    FROM emp_test



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hi Imu,

    Thanks for the kindly help .the code which you have given is very helpful for me.

    Side note: What is the reason to store the id values in a separate table? Sonds strange-->The tables which i have given is just sample its not fully normalised design .Anyways your example helped me alot.

    Thanks ,

    Chandru

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply