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
>