Просто используя STUFF & FOR XML PATH для генерации строки, разделенной запятой:
declare @Description nvarchar(max);
WITH Divs AS
(
SELECT
row_number() over (order by (select 0)) as rn,
replace(replace(replace(x.div.value('.','nvarchar(max)'),CHAR(13),''),CHAR(10),''),' ','') as val
from @htmlXML.nodes('/div/div') x(div)
)
select @Description = STUFF ((
select
rtrim(concat(' , ',divs1.val,' ',divs2.val))
from Divs divs1
left join Divs divs2
on (divs1.rn+1 = divs2.rn)
where divs1.rn%2>0
FOR XML PATH('')
), 1, 3, '');
select @Description as [Description];Если вы хотите также получить от него отдельные элементы, тогда первое преобразование в более простой для чтения XML может стоить того.
Например:
declare @Xml xml;
WITH Divs AS
(
SELECT
row_number() over (order by (select 0)) as rn,
ltrim(rtrim(replace(replace(x.div.value('.','nvarchar(max)'),CHAR(13),''),CHAR(10),''))) as val
from @htmlXML.nodes('/div/div') x(div)
)
select @Xml = (
select
divs1.val as "@type", rtrim(divs2.val) as "data()"
from Divs divs1
left join Divs divs2
on (divs1.rn+1 = divs2.rn)
where divs1.rn%2>0
FOR XML PATH('x'), root ('xml')
);
select @Xml as x;
declare @Description nvarchar(max);
/*
select @Description = STUFF ((
select concat(' ,',x.p.value('@type','nvarchar(max)'),' ',x.p.value('.','nvarchar(max)'))
from @Xml.nodes('/xml/x') x(p)
FOR XML PATH('')
), 1, 2, '');
*/
set @Description = concat(
'Compatibility: ',@Xml.query('/xml/x[@type="Compatibility:"]').value('.','nvarchar(max)')
,', Replacement Part Type: ',@Xml.query('/xml/x[@type="Replacement Part Type"]').value('.','nvarchar(max)')
,', Size: ',@Xml.query('/xml/x[@type="Size:"]').value('.','nvarchar(max)')
,', Resolution: ',@Xml.query('/xml/x[@type="Resolution:"]').value('.','nvarchar(max)')
,', Surface Type: ',@Xml.query('/xml/x[@type="Surface Type:"]').value('.','nvarchar(max)')
,', Touchscreen Panel: ',@Xml.query('/xml/x[@type="Touchscreen Panel:"]').value('.','nvarchar(max)')
,', Backlight type: ',@Xml.query('/xml/x[@type="Backlight type:"]').value('.','nvarchar(max)')
,', Video signal connector: ',replace(@Xml.query('/xml/x[@type="Video signal connector:"]').value('.','nvarchar(max)'),'[image]','')
);
select @Description as [Description]; Возврат:
Compatibility: Dell STUDIO 17, Replacement Part Type: LCD Screen Only, Size: 17-inch WideScreen (14.4inch x9inch ), Resolution: WXGA+ (1440x900), Surface Type: Glossy, Touchscreen Panel: Not Included, Backlight type: LED, Video signal connector: LED 50 pin screen