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)