sp_xml_preparedocument won't parse value from tag with _x in name

Question

Strange thing with sp_xml_preparedocument. Why it not take value from name_x tag ?

declare @pXml nvarchar(max)
set @pXml = '<item>
<name_x>_x</name_x>
<name_1x>_1x</name_1x>
<name_y>_y</name_y>
</item>'

DECLARE @hDoc INT 
EXEC sp_xml_preparedocument @hDoc OUTPUT, @pXml  

SELECT name_x, name_1x, name_y
FROM OPENXML(@hDoc, '/item', 2)
WITH
(
 name_x NVARCHAR(5),
 name_1x NVARCHAR(5),
 name_y NVARCHAR(5)
) xDoc
EXEC sp_xml_removedocument @hDoc

Answer 1

I don't know quite why it's not working (other than finding a few old threads that do suggest that the MSXML parser can struggle with underscores), but if you add a ColPattern, it's enough to force it to work:

declare @pXml nvarchar(max)
set @pXml = '<item>
<name_x>_x</name_x>
<name_1x>_1x</name_1x>
<name_y>_y</name_y>
</item>'

DECLARE @hDoc INT 
EXEC sp_xml_preparedocument @hDoc OUTPUT, @pXml  

SELECT * --name_x, name_1x, name_y
FROM OPENXML(@hDoc, '/item', 2)
WITH
(
 name_x NVARCHAR(5) 'name_x', --<-- Explicit pattern
 name_1x NVARCHAR(5),
 name_y NVARCHAR(5)
) xDoc
EXEC sp_xml_removedocument @hDoc

Results:

name_x name_1x name_y
------ ------- ------
_x     _1x     _y

I would still recommend marc's solution unless or until you've got a demonstrable issue though