November 29, 2007 at 1:36 am
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
November 29, 2007 at 1:50 am
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.
November 29, 2007 at 2:18 am
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
November 29, 2007 at 5:32 am
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
November 29, 2007 at 6:50 am
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