Split QueryString Parameter Value Pair and Assign to Variables with T-SQL XML



--

declare @test table (id int, Col  varchar(2000))
Insert into @test values
(1, 'teststring1=testdata1;teststring2=testdata2;teststring3=testdata3;')


DECLARE @teststring1 as VARCHAR(10)=null
DECLARE @teststring2 as VARCHAR(10)=null
DECLARE @teststring3 as VARCHAR(10)=null
DECLARE @teststring4 as VARCHAR(500)=null
DECLARE @teststring5 as VARCHAR(500)=null

;With mycte as (
SELECT id, Col,  Cast (N'<H><r ' + Replace(Replace(Replace(Col, ';','" '),'=','="')  + '" /></H>' ,'" "','"' ) AS XML) AS [vals]
FROM   @test
)
,mycte1 as (
SELECT id, ROW_NUMBER() OVER (ORDER BY S.a.value('count(.)', 'tinyint')) rn,
S.a.value('@teststring1', 'varchar(50)') as teststring1, 
S.a.value('@teststring2', 'varchar(50)') as teststring2,
S.a.value('@teststring3', 'varchar(50)') as teststring3, 
S.a.value('@teststring4', 'varchar(50)') as teststring4,
S.a.value('@teststring5', 'varchar(50)') as teststring5  
   
FROM mycte d CROSS APPLY d.[vals].nodes('/H/r') S(a)  ) 
 --select * from mycte1
SELECT  
@teststring1=teststring1,
@teststring2=teststring2,
@teststring3=teststring3,
@teststring4=teststring4,
@teststring5=teststring5

from mycte1 WHERE id =1

Print '@teststring1 value ---' + @teststring1;
Print '@teststring2 value ---' + @teststring2;
Print '@teststring3 value ---' + @teststring3;
Print '@teststring4 value ---' + @teststring4;
Print '@teststring5 value ---' + @teststring5;



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s