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

Question

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

Why are you using those old system procedure? SQL Server has supported XQUERY since at least SQL Server 2005. — Thom A
– Thom A ♦, Commented Jun 24, 2021 at 12:08

Zhorov · Accepted Answer · 2021-06-24 14:33:32Z

2

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)

edited Jun 24, 2021 at 14:33

answered Jun 24, 2021 at 12:20

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marko Ivkovic · Accepted Answer · 2021-06-24 12:14:18Z

0

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

answered Jun 24, 2021 at 12:14

Marko Ivkovic

1,2981 gold badge11 silver badges14 bronze badges

2 Comments

Bhavika Mahadik Over a year ago

I don't want to add in extra space. need to fetch the same value passed in userId tag.

Marko Ivkovic Over a year ago

First of all, when you ask question, post what you really want... After Zhorov updated question, I see what you need...

Yitzhak Khabinsky · Accepted Answer · 2021-06-24 12:34:20Z

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;

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related