XML tag ID 4 that was originally declared as is being redeclared as

Error message:

XML tag ID 4 that was originally declared as Aaaaa is being redeclared as Bbbbb.

Cause and solution:

Each group of data should be declared in other tag.

In this case, there were 2 groups with tag 4: [Aaaaa!4!CollectionID] and [Bbbbb!4!CollectionID].

The solution was to create new tag to the new group: [Bbbbb!13!CollectionID].

Of course that I had to add new UNION select in order to select the data of Bbbbb and to assign it to a parent.

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

There is insufficient system memory in resource pool to run this query

Error message:

There is insufficient system memory in resource pool to run this query

Possible solutions and checks:

Check the physical memory of the server, it's probably 100% of it is being used.

Possible fixes can be:

• Limit or increase the maximum server memory of the server (SSMS-->DB properties-->Memory). If you have enough RAM on the server - increase the memory, if not - check the limitation.
• Remove applications on the server that use much of the physical memory.
• Increase the RAM on the server.
• You can also restart the SQL Server Service, but it's just a quick solution, not permanent.