can't use identity column in where clause.

Hello,
Can anybody tell me why I can't use the identity column in the where clause
of the openxml query below? I am getting the following error "Invalid column
name 'DepId' ", but if I change the column "DepId" to not be an identity, the
query compiles.
********************Query************************* ********
CREATE PROCEDURE ESS_UpdateEmergencyCnt
@.emgData as nvarchar(4000)
AS
Declare @.hDoc int
DECLARE @.ReturnCode INT
exec sp_xml_prepareDocument @.hDoc OUTPUT, @.emgData
update prdepend
set prdepend.name = XMLPRDEPEND.name
from OPENXML(@.hDoc, '/ROOT/Contact')
with prdepend XMLPRDEPEND
where prdepend.DepId = XMLPRDEPEND.DepId
Exec sp_xml_removedocument @.hDoc
GO
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
"Sher" <Sher@.discussions.microsoft.com> wrote in message
news:F55E156B-3FCE-4964-AFF5-ECB57782D59D@.microsoft.com...
> Hello,
> Can anybody tell me why I can't use the identity column in the where
> clause
> of the openxml query below? I am getting the following error "Invalid
> column
> name 'DepId' ", but if I change the column "DepId" to not be an identity,
> the
> query compiles.
> ********************Query************************* ********
> CREATE PROCEDURE ESS_UpdateEmergencyCnt
> @.emgData as nvarchar(4000)
> AS
> Declare @.hDoc int
> DECLARE @.ReturnCode INT
> exec sp_xml_prepareDocument @.hDoc OUTPUT, @.emgData
> update prdepend
> set prdepend.name = XMLPRDEPEND.name
> from OPENXML(@.hDoc, '/ROOT/Contact')
> with prdepend XMLPRDEPEND
> where prdepend.DepId = XMLPRDEPEND.DepId
> Exec sp_xml_removedocument @.hDoc
> GO
>
|||Hi Michael,
Thank you for the response. However, I am not sure what you mean by use an
explicit with clause instead of the table name. I already have a with clause
in the query. Would you mind giving me an example of what you mean?
thanks,
Sher
"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
> "Sher" <Sher@.discussions.microsoft.com> wrote in message
> news:F55E156B-3FCE-4964-AFF5-ECB57782D59D@.microsoft.com...
>
>
|||If your table prdepend looks like (key int identity, foo nvarchar(50), bar
int)
then instead of saying
WITH prdepend
say
WITH (
foo nvarchar(50).
bar int)
if you do not have values for key or
WITH (
key int,
foo nvarchar(50),
bar int)
if you have.
Best regards
Michael
"Sher" <Sher@.discussions.microsoft.com> wrote in message
news:1257118A-523E-4380-8F9B-AC4E50C0E0DC@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thank you for the response. However, I am not sure what you mean by use an
> explicit with clause instead of the table name. I already have a with
> clause
> in the query. Would you mind giving me an example of what you mean?
> thanks,
> Sher
> "Michael Rys [MSFT]" wrote:
|||Thanks Michael. It worked.
regards,
SA
"Michael Rys [MSFT]" wrote:

> If your table prdepend looks like (key int identity, foo nvarchar(50), bar
> int)
> then instead of saying
> WITH prdepend
> say
> WITH (
> foo nvarchar(50).
> bar int)
> if you do not have values for key or
> WITH (
> key int,
> foo nvarchar(50),
> bar int)
> if you have.
> Best regards
> Michael
> "Sher" <Sher@.discussions.microsoft.com> wrote in message
> news:1257118A-523E-4380-8F9B-AC4E50C0E0DC@.microsoft.com...
>
>