how to read a XML string value with leading space in sql server

I want to store the value of userId as " XYZ" but when after executing the below code got output as "XYZ". Want to store the value with leading space.

Declare @xml    Nvarchar(100), 
        @hDoc   Int,
        @user   Nvarchar(30)


SET @XML= '<ROOT><DETAIL ID ="1" name ="ABC" userId="    XYZ" /></ROOT>'


EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml 

    SELECT  @user = userId
    FROM    OPENXML(@hDoc, '/ROOT/DETAIL')   
    WITH    ( userId Nvarchar(30) )

EXEC sp_xml_removedocument @hDoc

SELECT @user

Answer

You may try to parse the input XML using a variable of XML data type and a combination of nodes() and value() methods:

DECLARE @xml xml
SET @xml = N'<ROOT><DETAIL ID ="1" name ="ABC" userId="    XYZ" /></ROOT>'

SELECT d.a.value('@userId', 'nvarchar(30)')
FROM @xml.nodes('/ROOT/DETAIL') d(a)

Using this approach you may parse more complex XML data and get userId‘s as rows in a table:

DECLARE @xml xml
SET @xml = N'<ROOT>
   <DETAIL ID ="1" name ="ABC" userId="    XYZ" />
   <DETAIL ID ="2" name ="ABC" userId="    123" />
</ROOT>'

SELECT d.a.value('@userId', 'nvarchar(30)')
FROM @xml.nodes('/ROOT/DETAIL') d(a)