OPENXML Invalid column name error on update

I have the stored procedure below which complies and works OK as long
as the field RxID is NOT set as an identity column. When the field is set as
an identity column then I get the error Invalid column name 'RxID'.
It's very easy to reproduce this issue, is this a bug, is there any work
around?
CREATE Procedure UpdateRxDetail
@.doc text
AS
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.doc
BEGIN TRANSACTION
UPDATE rxDetail
SET
rxDetail.FormattedErrorString = XMLTABLE.FormattedErrorString,
rxDetail.StatusID = XMLTABLE.StatusID
FROM OPENXML(@.hdoc, '//errInfo')
WITH rxDetail XMLTABLE
WHERE rxDetail.RxID = XMLTABLE.RxID
COMMIT
EXEC sp_xml_removedocument @.hdoc
RETURN
GOPlease post the XML you want to parse.
ML
http://milambda.blogspot.com/|||If you replace your UPDATE statement with this
SELECT XMLTABLE.*
FROM OPENXML(@.hdoc, '//errInfo')
WITH rxDetail XMLTABLE
you'll see that RxID only appears when it's
not an identity. AFAIK, there's no way round this.
You'll have to change "WITH rxDetail"
to explicitly list the XML<->column mappings
yourself. Something like this
UPDATE rxDetail
SET
rxDetail.FormattedErrorString = XMLTABLE.FormattedErrorString,
rxDetail.StatusID = XMLTABLE.StatusID
FROM OPENXML(@.hdoc, '//errInfo')
WITH (FormattedErrorString varchar(10),
StatusID int,
RxID int) XMLTABLE
WHERE rxDetail.RxID = XMLTABLE.RxID|||Thanks Mark - that did the trick!
"markc600@.hotmail.com" wrote:

> If you replace your UPDATE statement with this
> SELECT XMLTABLE.*
> FROM OPENXML(@.hdoc, '//errInfo')
> WITH rxDetail XMLTABLE
> you'll see that RxID only appears when it's
> not an identity. AFAIK, there's no way round this.
> You'll have to change "WITH rxDetail"
> to explicitly list the XML<->column mappings
> yourself. Something like this
> UPDATE rxDetail
> SET
> rxDetail.FormattedErrorString = XMLTABLE.FormattedErrorString,
> rxDetail.StatusID = XMLTABLE.StatusID
> FROM OPENXML(@.hdoc, '//errInfo')
> WITH (FormattedErrorString varchar(10),
> StatusID int,
> RxID int) XMLTABLE
> WHERE rxDetail.RxID = XMLTABLE.RxID
>

OpenXML Identity Insert

I am inserting data into a table using the OpenXML command (see query below). The table that I am inserting it into has an identity column named SOFTWARE_UID. I need to return an xmldoc with the identifier included for each row inserted. Does anyone have a clue how I can do that efficiently? I know I can do it with a cursor, but if that were the case, then why not just do x inserts with an @.@.identity call? I am looking to do this in an efficient way, preferebly with an out of box function (such as @.@.identities).

Declare @.XMLString varchar(max)

Set @.XMLString = '<IT_SOFTWARE>

<SOFT name="Soft1" status="1"/>

<SOFT name="Soft2" status="1"/>

<SOFT name="Soft3" status="1"/>

</IT_SOFTWARE>'

declare @.XmlHandle int

EXEC sp_xml_preparedocument @.XmlHandle output, @.XMLString

Insert Into INFORMATION_SYSTEMS.dbo.IT_SOFTWARE

SELECT SOFTWARE_NAME,SOFTWARE_STATUS_ID

FROM OPENXML (@.XmlHandle, '/IT_SOFTWARE/SOFT',1)

WITH ( SOFTWARE_NAME varchar(max) '@.name',

SOFTWARE_STATUS_ID int '@.status')

EXEC sp_xml_removedocument @.XmlHandle

As you are using varchar(max) I'll assume your are
using SQL Server 2005

This should give you your results from your
table along with the identities

select SOFTWARE_NAME as "@.name",
SOFTWARE_STATUS_ID as "@.status",
SOFTWARE_UID as "@.UID"
FROM INFORMATION_SYSTEMS.dbo.IT_SOFTWARE
FOR XML PATH('SOFT'), ROOT('IT_SOFTWARE'), TYPE

You can also change from using OPENXML and
sp_xml_preparedocument/sp_xml_removedocument to this

declare @.xml xml
set @.xml=@.XMLString

Insert Into INFORMATION_SYSTEMS.dbo.IT_SOFTWARE(SOFTWARE_NAME,SOFTWARE_STATUS_ID)
select R.n.value('@.name','varchar(max)') as SOFTWARE_NAME,
R.n.value('@.status','int') as SOFTWARE_STATUS_ID
from @.xml.nodes('/IT_SOFTWARE/SOFT') as R(n)

|||

If you are after only the newly inserted rows you
can use the output..into clause in the insert statement

DECLARE @.IT_SOFTWARE table (
SOFTWARE_UID int,
SOFTWARE_NAME varchar(max),
SOFTWARE_STATUS_ID int);

Insert Into INFORMATION_SYSTEMS.dbo.IT_SOFTWARE(SOFTWARE_NAME,SOFTWARE_STATUS_ID)
output inserted.SOFTWARE_UID,
inserted.SOFTWARE_NAME,
inserted.SOFTWARE_STATUS_ID
into @.IT_SOFTWARE(SOFTWARE_UID,SOFTWARE_NAME,SOFTWARE_STATUS_ID)
select R.n.value('@.name','varchar(max)') as SOFTWARE_NAME,
R.n.value('@.status','int') as SOFTWARE_STATUS_ID
from @.xml.nodes('/IT_SOFTWARE/SOFT') as R(n)

select SOFTWARE_NAME as "@.name",
SOFTWARE_STATUS_ID as "@.status",
SOFTWARE_UID as "@.UID"
FROM @.IT_SOFTWARE
FOR XML PATH('SOFT'), ROOT('IT_SOFTWARE'), TYPE

|||I used some code from both. I never knew about the output command. Thank you for the help.