CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008t-sql
Rojelo
Rojelo

Reputation: 108

Retrieving the only value from XML string

Here is a my XML string of datatype nvarchar(MAX):

declare @string Nvarchar(MAX)
set @string='<ROOT><Data  C="1" /><Data  C="2" /><Data  C="3" /></ROOT>'

Expected result:

@c='1,2,3'

i want to check that the value of "C" is exists in table or not

Upvotes: 2

Views: 52

Answers (1)

gotqn
gotqn

Reputation: 43636

You can use the following code to get the values in tabular format:

declare @string Nvarchar(MAX)
set @string='<ROOT><Data  C="1" /><Data  C="2" /><Data  C="3" /></ROOT>'

DECLARE @StringXML XML = CAST(@string as XML);

SELECT T.c.value('(./@C)[1]', 'INT')
FROM @StringXML.nodes('ROOT/Data') T(c);

Then, you can apply the EXIST clause.

SELECT STUFF
(
    (
        SELECT ',' + T.c.value('(./@C)[1]', 'VARCHAR(12)')
        FROM @StringXML.nodes('ROOT/Data') T(c)
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)')
    ,1
    ,1
    ,''
);

Upvotes: 1

Related Questions