Thursday, June 28, 2012

Split Comma Seperated String using XML in Sql Server

DECLARE @strDeviceData varchar(MAX) = '1:1,1:0,2:0,2:1'
DECLARE @X xml
DECLARE @split char(1)
SET @split=','
SELECT @X=CONVERT(xml,'<root><h>' + REPLACE(@strDeviceData,@Split,'</h><h>') + '</h></root>')
SELECT [DeviceInfo] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/h') T(c)

When you run query, you will get following output.

 

No comments:

Post a Comment