Reduce XML extraction gaps

Question

declare @hdoc int, @xmlchar nvarchar(max)
set @xmlchar = '<root> <row _Fld394=" 61640200" /></root>'
exec sp_xml_preparedocument @hdoc out, @xmlchar

select _Fld394
from openxml(@hdoc, '/root/row')
with
(_Fld394 nvarchar(9) '@_Fld394')

exec sp_xml_removedocument @hdoc
//result = '61640200'
//must be = ' 61640200'

If you look at _Fld394 9 characters are written - from the front space. When extracted, it trims the left side with spaces.

How to solve the problem?

Answer 1

declare @hdoc int, @xmlchar nvarchar(max)
set @xmlchar = '<root> <row _Fld394="     asas" /><row _Fld394="" /></root>'
exec sp_xml_preparedocument @hdoc out, @xmlchar

select   QuoteName(A)                       AS A
    ,QuoteName(B.value('.'          ,'varchar(9)')) AS B
    ,QuoteName(C.value('*[1]/@_Fld394'  ,'varchar(9)')) AS C
from openxml(@hdoc, '/root/row')
with (A varchar(9) '@_Fld394', B xml '@_Fld394/text()', C xml '.')

exec sp_xml_removedocument @hdoc

Answer 2

I believe this is a known issue with sp_xml_preparedocument. You can either encode your spaces with  :

set @xmlchar = '<root> <row _Fld394="&#160;61640200" /></root>'

or (assuming SQL 2005 or later) uses the new XML data type and processing style:

declare @xmlchar xml
set @xmlchar = '<root> <row _Fld394=" 61640200" /></root>'

select x.fld.value('@_Fld394','varchar(100)')
 from @xmlchar.nodes('//root/row') as x(fld)