Join with comma separated values in SQL Server Compact not working

Question

I have 2 table as below

CREATE TABLE [Chart]
(
   [Id] BIGINT NOT NULL IDENTITY (1,1),
   [Owner] NVARCHAR(100),
   [Identifier] NVARCHAR(128) NOT NULL,
   [Title] NVARCHAR(100),
   [Type] INT NOT NULL,
   [Category] INT NOT NULL,
   [CreatedDate] DATETIME NOT NULL,
   [ModifiedDate] DATETIME,
   [ExpireDate] DATETIME,
   [VisitCount] BIGINT NOT NULL,
   [ConfigurationId] BIGINT NOT NULL,
   [Theme] NVARCHAR(50)
);

ALTER TABLE [Chart] ADD CONSTRAINT [PK_dbo.Chart] PRIMARY KEY ([Id]);


CREATE TABLE [SelectedTag]
(
   [Id] BIGINT NOT NULL IDENTITY (1,1),
   [Identifier] NVARCHAR(4000),
   [TagName] NVARCHAR(4000),
   [Description] NVARCHAR(4000),
   [EuUnit] NVARCHAR(50),
   [EuRangeLow] NVARCHAR(50),
   [EuRangeHigh] NVARCHAR(50),
   [MinStats] FLOAT,
   [MaxStats] FLOAT,
   [Min] FLOAT,
   [Max] FLOAT,
   [GroupCode] NVARCHAR(4000)
);

ALTER TABLE [SelectedTag] ADD CONSTRAINT [PK_dbo.SelectedTag] PRIMARY KEY ([Id]);

I tried this answer for query Tag Count and TagList for one Id it worked well in SQL Server

https://dba.stackexchange.com/q/112408/172901

select c.Identifier, count(c.Identifier) as TagCount, (
       select concat(',', s.TagName)  from selectedtag  as s
       where concat(',', c.identifier, ',') like '%,'+s.identifier+',%'
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as TagList
 from chart c
 group by c.Identifier

But with SQL Server compact Database it did not work

I use CompactView to run query with SQL compact DB

https://sourceforge.net/projects/compactview/

What issue with my query or SQL Compact issue? Thanks

Answer 1

XML data type and functions are not supported by SQL Server Compact, and nested sSELECTs does not work either. Write a plain select, and perform additional processing in code.