Add separator between special sql content

Question

How to do add separator (,) between one cell in sql this example?

declare @htmlXML xml = N'<div class="screen_specs_container  " >

<div class="left_specs_container">Compatibility:</div>
<div class="right_specs_container " ><h4 >Dell STUDIO 17</h4></div><div class="left_specs_container" data-filterLabel="Replacement Part Type:">Replacement Part Type</div><div class="right_specs_container spec_highlight"> LCD Screen Only<a class="thickbox" title="" href="/laptopscreen_viewer.php?edit_language=English&amp;view_article=1468010881&amp;height=500&amp;width=800"><img border="0" valign="middle" alt="?" src="/images/question2.gif"/></a></div><div class="left_specs_container"></div><div class="right_specs_container" style=" max-width: 250px;"><font color="red">ONLY FOR <a href="https://youtu.be/4F0xPuz4Jnw>" >LED VERSION</a> WILL NOT WORK FOR CCFL!</font></div><div class="left_specs_container" data-filterLabel="Size:">Size:</div><div class="right_specs_container" title="size">17-inch WideScreen (14.4inch x9inch )<a class="lightbox" title="" href="//www.youtube.com/embed/_zalTiJ1vFY?autoplay=1"><img border="0" valign="middle" alt="?" src="/images/question2.gif" /></a></div><div class="left_specs_container" data-filterLabel="Resolution:">Resolution:</div><div class="right_specs_container" title="resolution">WXGA+ (1440x900)<a class="lightbox" title="" href="//www.youtube.com/embed/WWPSXibVLkg?autoplay=1"><img border="0" valign="middle" alt="?" src="/images/question2.gif" /></a></div><div class="left_specs_container" data-filterLabel="Surface Type:">Surface Type:</div><div class="right_specs_container" title="type"><font color="red">Glossy<a class="lightbox" title="" href="//www.youtube.com/embed/gt_CSbMF-5o?autoplay=1"><img border="0" valign="middle" alt="?" src="/images/question2.gif" /></a></font></div><div class="left_specs_container" style="display:none" data-filterLabel="Touchscreen Panel:">Touchscreen Panel:</div><div class="right_specs_container"  style="display:none">Not Included</div><div class="left_specs_container" data-filterLabel="Backlight type:">Backlight type:</div><div class="right_specs_container" title="backlight-type">LED<a class="lightbox" title="" href="//www.youtube.com/embed/4F0xPuz4Jnw?autoplay=1"><img border="0" valign="middle" alt="?" src="/images/question2.gif" /></a></div><div class="left_specs_container">Video signal connector:</div><div class="right_specs_container">LED 50 pin screen [<a target="blank" class="thickbox laptops" title="Video Connector" href="/img/connectors/1_big/LED50pins.jpg">image</a>]</div><div class="left_specs_container">Condition:</div><div class="right_specs_container">New Grade A+<a class="thickbox" title="" href="/laptopscreen_viewer.php?edit_language=English&amp;view_article=1243967715&amp;height=500&amp;width=800"><img border="0" valign="middle" alt="?" src="/images/question2.gif"/></a></div><div class="left_specs_container">Warranty:</div><div class="right_specs_container">3 Years</div><div class="left_specs_container" style="color:f00">Note:</div><div class="right_specs_container">Make sure new screen has same size, resolution, backlight as your old one!</div></div>'
;


         SELECT  
            t.v.value('.','nvarchar(max)') as [Description]
     FROM   @htmlXML.nodes('/div[1]') as t(v)

now output:

Compatibility:Dell STUDIO 17Replacement Part Type LCD Screen OnlyONLY FOR LED VERSION WILL NOT WORK FOR CCFL!Size:17-inch WideScreen (14.4inch x9inch )Resolution:WXGA+ (1440x900)Surface Type:GlossyTouchscreen Panel:Not IncludedBacklight type:LEDVideo signal connector:LED 50 pin screen [image]Condition:New Grade A+Warranty:3 YearsNote:Make sure new screen has same size, resolution, backlight as your old one!

The desired output:(add , and : and delete special content)

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

score 1 · Accepted Answer · edited May 23 '17 at 11:53

Just using STUFF & FOR XML PATH to generate the comma separated string:

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];

If you want to get individual elements from it also, then first transforming to an easier to read XML might be worth it.

For example:

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];

Returns :

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

if i want to add : for Replacement Part Type : LCD Screen Only — cyrus2500, Oct 15 '16 at 12:04
Please See now output and The desired output:(add , and : and delete special content) — cyrus2500, Oct 16 '16 at 08:26
Changed the 2nd method to generate @Description. To make it more controlled. — LukStorms, Oct 16 '16 at 13:29
Never tried it, but SO has a [tag](http://stackoverflow.com/questions/tagged/html-agility-pack) for [HTML Agility Tag](https://htmlagilitypack.codeplex.com/). [Which seemed to help](http://stackoverflow.com/questions/22661640/how-to-fix-ill-formed-html-with-html-agility-pack) at least one person. — LukStorms, Oct 17 '16 at 14:25
help me this link: http://stackoverflow.com/questions/40189948/how-to-do-dynamic-parsing-node-in-this-example-in-sql — cyrus2500, Oct 22 '16 at 08:26
help me this link? http://stackoverflow.com/questions/40741327/how-to-do-add-separator-in-result-xquery-in-sql-this-example — cyrus2500, Nov 22 '16 at 12:02

Add separator between special sql content

1 Answers1

Linked