Hi Johan,

When I open the Software Factory and I filter the translations on translations containing the character & and use the export, no such problems occur, not with XLS or XLSX.

Are you certain a calculated field or a view being exported doesn’t use a for-xml-path construction to aggregate some text, causing this issue?

Hi Anne,

Sorry for my late response. I had to get more information from our customer about this issue. After some research I saw that the column that is exported with the text “&” in it is indeed using a for-xml-path construction. This column is an expression:

STUFF((
SELECT ' ,' + x.naam 
FROM   oplosser o
JOIN   organisatie x ON x.guid = o.organisatie_guid
WHERE  o.melding_guid = t1.guid
ORDER BY x.naam
FOR XML PATH ('')), 1 , 2 , '')

How can I prevent that this will show like this?

Are you by chance using SQL Server 2017 or later? The string_agg function would be perfect for this.

For earlier versions of SQL Server you could do something like this:

declare @data table (item nvarchar(100));

insert into @data values (N'test1');
insert into @data values (N'test2');
insert into @data values (N'test3');
insert into @data values (N'test4');
insert into @data values (N'test5 & more');


select 
    x.y.value('.', 'nvarchar(max)')
from 
(
    select 
        iif(row_number() over (order by item) = 1, '', ', ') + item 
    from @data
    order by item
    for xml path (''), type
) x (y);

We have to support multiple SQL versions because we deliver a standard solution for multiple clients. The minimum version that we support is SQL 2012.

Thank you for your suggestions. I’ll try your last solution and let you know if that works for me.

Heb de expressie van het veld uiteindelijk als volgt aangepast:

REPLACE(
STUFF((
SELECT ', ' + x.naam 
FROM   oplosser o
JOIN organisatie x ON x.guid = o.organisatie_guid
WHERE o.melding_guid = t1.guid
ORDER BY x.naam
FOR XML PATH ('')), 1 , 2 , ''),
'&', '&')

Toen werkte het bij het exporteren.

Bedankt voor je hulp Anne!

Hi Johan,

This does resolve the problem with the ampersand. However, you might run into a few more problems with other characters being escaped, such as greater-than and lower-than.

The following solution would prevent any xml escaping from ending up in the final text:

STUFF(
    (SELECT x.y.value('.', 'NVARCHAR(MAX)')
     FROM (
        SELECT ', ' + x.naam 
        FROM   oplosser o
        JOIN organisatie x ON x.guid = o.organisatie_guid
        WHERE o.melding_guid = t1.guid
        ORDER BY x.naam
        FOR XML PATH (''), type
        ) x(y)
    ), 1 , 2 , '')

Edited: casing of .value

Yes, this works great! I had some troubles implementing it for my specific situation, so thank you for this example!

Have a nice weekend!

Hi Anne,

I really don't get it. It worked fine before, but when opening subject “melding” now, it results in an error:

************************************
* Thinkwise Software Error Message *
************************************

Datum: 2-6-2020 23:25:05
GUI: 2019.2.17.0
.NET Runtime: 4.0.30319.42000

Project: JP_Bouwmanagement (6.3)
Softwarefabriek: JPDS_MOD_2018_3
Taal: NL

"VALUE" is not a valid function, property, or field.

SQL:
Error code: 227
select t1. guid], t1. melding_nummer], t1. oplossers_lijst]
from (
select t1. guid], t1. melding_nummer], (STUFF(
    (SELECT x.y.VALUE('.', 'NVARCHAR(MAX)')
     FROM (
        SELECT ', ' + x.naam 
        FROM   oplosser o
        JOIN organisatie x ON x.guid = o.organisatie_guid
        WHERE o.melding_guid = t1.guid
        ORDER BY x.naam
        FOR XML PATH (''), type
        ) x(y)
    ), 1 , 2 , ''))
 as yoplossers_lijst]
from melding] as t1
) as t1
order by t1.nmelding_nummer] desc

I run this with a Windows GUI 2019.2.17.0 on SQL Server 2016 (13.0.1742).

What is happening? Can you please help me out here?

Hi Johan, 

Sorry for the late reply.

The reason seems to be that x.y.value should be used instead of x.y.VALUE. I’m not entirely certain why this case-sensitivity is in place and in which versions of SQL Server this is the case.

Hi Anne,

Okay, I’ll try this later. Thank you for your reply.