OpenXML and IDENTITY-Columns

  • Hi!

    I have a problem using OpenXML in combination with tables that have an IDENTITY-Column...

    I want to read the values from a XML-File into a temp-table and later on I want to insert the values of the temp-table into a real table.

    Creating a temp-table on basis of a existing table:

    select * into #tmp_tbl from REAL_TABLE where 1 = 0

    Reading the XML:

    EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml

    INSERT INTO #tmp_tbl

    SELECT *

    FROM

    OPENXML (@Pointer,'DATA/OBJECTS/OBJECTS_ITEM',2)

    WITH REAL_TABLE

    does not work.

    The problem is that the REAL_TABLE hast one IDENTITY-Column called ID.

    So if I try just a simple SELECT * FROM OPENXML, the "ID"-Column is omitted in the Output.

    What can I do to insert all of my XML-Values (including the ID-column) into my temp-table??

    Any ideas??

    thanks in advance

  • Hi,

    write this at the beginning of you code

    SET INDENTIY_INSERT REAL_TABLE ON -- "disables" the identity column

    EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml

    INSERT INTO #tmp_tbl

    SELECT *

    FROM

    OPENXML (@Pointer,'DATA/OBJECTS/OBJECTS_ITEM',2)

    WITH REAL_TABLE

    and SET INDETITY_INSERT REAL_TABLE OFF --"enables" the identity column

    hope it will help,

    Oana.

  • Hi! Thanks for your reply, but it isn't working :/

    I did a bit of testing....

    If i make a SELECT * FROM OPENXML (...) WITH TABLEName then the ID-Column is omitted in the Output.

    If i make a SELECT * FROM OPENXML (...) WITH (columnname datatype) then the ID-Column is printed.

    So when I try the second option: (SET INDENTIY_INSERT is set to ON)

    INSERT INTO #tmp_tbl SELECT * FROM OPENXML (...)

    WITH (ID int, PID int, NAME varchar(255))

    i get:

    Msg 545: Explicit value must be specified for identity column in table '#tmp_tbl'

    How should i do that?

    I mean, I can save the value of the ID-field into a variable? But how can I insert the id-variable and the rest of the values into the tmp_tbl?

    I tried it this way, but it isn't working :/

    INSERT INTO #tmp_tbl(ID) VALUES(id_variable)

    INSERT INTO #tmp_tbl SELECT (columns) FROM OPENXML (...)

    WITH (col1 int,col2 varchar)

    WHERE #tmp_tbl.ID = @id_variable

    resulting in: The multi-part identifier "#tmp_tbl.ID" could not be bound.

    any help would be appreciated

    greets

    bang

  • ok, it's time for some dirty workaround 😎

    > "Michael Rys [MSFT]" wrote:

    >

    >> This is a restriction of the OpenXML WITH clause since IDENTITY values

    >> are

    >> generated automatically and we do not know, whether you want it to take

    >> from

    >> the XML document or not.

    >>

    >> The solution is to give an explicit WITH clause (instead of using the

    >> table

    >> name).

    >>

    >> Best regards

    >> Michael

  • That last error sounds like you either aliased the table and then referred to it by name or that you dropped the table prior to that part of the script running.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

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