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.
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