Prevent OPENXML from trimming strings

[William F. Kinsley]

I have a XML node that is padded with leading spaces, when I use openxml to
access the XML file, the value returned has the leading spaces stripped
off. How can I prevent the test values from being trimmed?

XML Input:
<person><row first_name="Test Name" user_id=" 1"/></person>

SQL Sample:

DECLARE @idoc int;
DECLARE @doc varchar(8000);
SET @doc ='
<person><row first_name="Test Name" user_id=" 1"/></person>';

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT first_name, '|'+user_id+'|' User_id
FROM OPENXML(@idoc, '/person/row', 1)
WITH ( first_name varchar(15),
user_id char(12));
EXEC sp_xml_removedocument @idoc

Result:
first_name User_id
--------------- --------------
Test Name |1 |

(1 row(s) affected)

Thx

--
-----------------------------------------
William F. Kinsley
NextGen Healthcare Info. Sys. Inc.

[Bob]

If you are in SQL 2005 you can use the nodes method of the XML data-type, eg

DECLARE @doc XML

SET @doc ='<person><row first_name="Test Name" user_id="
1"/></person>';

SELECT
'|' + x.y.value('@user_id[1]', 'VARCHAR(30)') + '|',
'|' + x.y.value('@first_name[1]', 'VARCHAR(30)') + '|'
FROM @doc.nodes('/person/row') x(y)

If you're still in SQL2000, try here for a few examples:
http://sqlxml.org/faqs.aspx?faq=78

Looking at your data though, if it's going to be pipe-delimited maybe white
space doesn't matter? I can understand why it would matter in a fixed-width
file, but not a delimited one.

HTH

[William F. Kinsley]

Thank you, that helped, I only pipe-delimited the result to make the issue
easier to understand.

Bill

"Bob" <B...@discussions.microsoft.com> wrote in message
news:F3E16091-A2AE-423E...@microsoft.com...