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

score 2 · Answer 1 · answered Jul 29 '14 at 10:18

2

Why don't you just use the native XQuery support in SQL Server?

-- if you're dealing with XML - use the "XML" datatype!
declare @pXml XML = '<item>
                       <name_x>_x</name_x>
                       <name_1x>_1x</name_1x>
                       <name_y>_y</name_y>
                     </item>'


SELECT
    Name_x = @pXml.value('(item/name_x)[1]', 'nvarchar(5)'),
    Name_1x = @pXml.value('(item/name_1x)[1]', 'nvarchar(5)'),
    Name_y = @pXml.value('(item/name_y)[1]', 'nvarchar(5)')

This gives you an output of:

Name_x  Name_1x   Name_y
  _x      _1x       _y

answered Jul 29 '14 at 10:18

marc_s

732,580
175
1,330
1,459

-- if you're dealing with XML - use the "XML" datatype! This is no matter on issue described. And sp_xml_preparedocument looks like faster on big XML's ... – Alex A Jul 29 '14 at 10:19
If change declare @pXml nvarchar(max) to XML it still not work with tags that have _x in name. – Alex A Jul 29 '14 at 10:23
Using the **native XQuery** API, it works just fine - why can't you use that? – marc_s Jul 29 '14 at 10:24
OpenXML performs better for large XML documents – Alex A Jul 29 '14 at 10:28
1

@AlexA - so your optimizing for performance over correctness? Interesting. – Damien_The_Unbeliever Jul 29 '14 at 10:33
No, i just want to know, is any known reason why it fails on such names. And OpenXML does not mentioned as obsolete and outdated as i know. – Alex A Jul 29 '14 at 10:34

score 2 · Accepted Answer · edited May 23 '17 at 10:33

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

Thanks, ColPattern looks like exit :) – Alex A Jul 29 '14 at 10:51 — Alex A, Jul 29 '14 at 10:51

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

2 Answers2