XML field value is NULL

T-SQL:

DECLARE @EventXML XML

SET @EventXML=convert(xml,N'

<Event>

<ExternalID>4947</ExternalID>

<ExternalEventId>abc</ExternalEventId>

</Event>')

DECLARE @hDoc int

exec sp_XML_PrepareDocument @hDoc output,@EventXML

select ExternalID, ExternalEventID

from

OPENXML(@hDoc,'//Event',2)

with

( ExternalID nvarchar(100),

ExternalEventID nvarchar(100)

) XML

exec sp_XML_RemoveDocument @hDoc

Results:

ExternalID = 4947, ExternalEventId = NULL

Problem:

ExternalEventId should be 'abc' and not NULL.

Solution:

XML is case sensitive.

ExternalEventId was set to the XML with 'd', and in the OPENXML is 'D'.

Replace one of them to be like the other.

select ExternalID, ExternalEventID

from

OPENXML(@hDoc,'//Event',2)

with

( ExternalID nvarchar(100),

ExternalEventId nvarchar(100)

) XML