CODEWITHSUNDEEP
sqlsql-serverxml
Bhavika Mahadik
Bhavika Mahadik

Reputation: 3

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

Upvotes: 0

Views: 219

Answers (3)

Zhorov
Zhorov

Reputation: 29943

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)

Upvotes: 2

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

As @Larnu pointed out:

Starting from SQL Server 2005 onwards, it is better to use XQuery language, based on the w3c standards, while dealing with the XML data type. Microsoft proprietary OPENXML and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases. It is strongly recommended to re-write your SQL and switch it to XQuery.

One single XQuery .value() method call gives you all what you need.

SQL

DECLARE @xml XML = N'<ROOT><DETAIL ID ="1" name ="ABC" userId="    XYZ" /></ROOT>'
    , @user nvarchar(30);

SET @user = (SELECT @xml.value('(/ROOT/DETAIL/@userId)[1]', 'nvarchar(30)'));
SELECT @user;

Upvotes: 0

Marko Ivkovic
Marko Ivkovic

Reputation: 1290

If you just add space before userID, you will get desired result.

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

Check answer on DB<>FIDDLE

Upvotes: 0

Related Questions