select * from CMOBJPROPS26 WITH (NOLOCK)
-- SQL Server 2016 and above
select CMID, value
from CMOBJPROPS26 WITH (NOLOCK)
CROSS APPLY STRING_SPLIT(DELIVOPTIONS,';')
-- All SQL Server releases
With delivOptXML(CMID, optXML)
as
(select
CMID,
cast(''+replace(replace(replace(replace(replace(replace(cast(DELIVOPTIONS as NVARCHAR(max)),'xsi:',''),'SOAP-ENC:',''),';',''),'<','<'),'>','>'),''+char(13)+char(10),'')+'' as XML) optXML
from CMOBJPROPS26 WITH (NOLOCK)
)
select d.CMID,
optRow.value('c[1]', 'varchar(50)') optName,
optRow.value('c[2]', 'varchar(50)') optEnum,
optRow.value('c[3]', 'varchar(50)') optType,
optRow.value('c[4]', 'int') optValueLength,
optRow.query('c[5]/value') optValue
from delivOptXML d
CROSS APPLY d.optXML.nodes('/r') x(optRow)