Add Separator Between Special Sql Content

Question

How to do add separator (,) between one cell in sql this example? declare @htmlXML xml = N'

Co

Solution 1:

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

declare@Description nvarchar(max);

WITH Divs AS 
(
    SELECTrow_number() over (orderby (select0)) 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
    leftjoin Divs divs2
      on (divs1.rn+1= divs2.rn)
    where divs1.rn%2>0FOR XML PATH('')
), 1, 3, '');

select@Descriptionas [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:

Baca Juga

declare@Xml xml;

WITH Divs AS 
(
    SELECTrow_number() over (orderby (select0)) 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
    leftjoin Divs divs2
      on (divs1.rn+1= divs2.rn)
    where divs1.rn%2>0FOR XML PATH('x'), root ('xml')
);

select@Xmlas 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@Descriptionas [Description];

Returns :

Compatibility:DellSTUDIO17,Replacement Part Type:LCDScreenOnly,Size:17-inchWideScreen(14.4inchx9inch),Resolution:WXGA+(1440x900),Surface Type:Glossy,Touchscreen Panel:NotIncluded,Backlight type:LED,Video signal connector:LED50pinscreen

Html5 Website

Add Separator Between Special Sql Content

Solution 1:

Post a Comment for "Add Separator Between Special Sql Content"