declare @xml xml
DECLARE @idoc int
select @xml='<Root>
<List>
<contentstr> aaaa bbbb </contentstr>
</List>
</Root>'
exec sp_xml_preparedocument @idoc output,@xml
select * from OPENXML(@idoc, '/Root/List',2)
WITH(
contentstr nvarchar(max))
EXEC sp_xml_removedocument @idoc
上面代码运行出来结果为aaaa bbbb,字符串前后的空格被自动去除,我需要保留这些空格,大家帮我看看怎么解决。
------解决方案--------------------
declare @xml xml
DECLARE @idoc int
select @xml='<Root>
<List>
<contentstr> aaaa bbbb </contentstr>
</List>
</Root>'
select @xml = REPLACE(CONVERT(NVARCHAR(MAX),@xml),' ','@') --转一下
exec sp_xml_preparedocument @idoc output,@xml
SELECT replace(contentstr,'@',' ') AS contentstr --再转回来
FROM OPENXML(@idoc, '/Root/List',2)
WITH(contentstr Nvarchar(max))