Truncate spaces in extracting XML

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 recorded 9 characters - from the front space When extracting it clipped the left with spaces.

How to solve the problem?

Answer 1

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) use the newer style XML datatype and handling:

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)

Answer 2

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