Using FOR XML PATH, TYPE).value('.[1]','nvarchar(max)')

Question

I was checking that using this is good to handle special characters but at the same time is over complicating the query generating a "cardinality estimate warning"

If I use FOR XML PATH(''), the query plan is much better and the cardinatity is gone. Anybody faced this issue before? is there any workaround to continue using FOR XML PATH, TYPE).value('.[1]','nvarchar(max)') and get rid of the cardinality issue?

SELECT r.ServiceId,

        STUFF(
                (
            SELECT '; ' + u.Name 
                FROM dbo.UsedFor u
                inner join dbo.ServiceUsedRelation r2
                    on u.UsedId = r2.UsedId
                where
                    r2.ServiceId = r.ServiceId
                FOR XML PATH, TYPE).value('.[1]','nvarchar(max)')          
                , 1
                , 1
                , ''
    ) as Name
     FROM dbo.ServiceUsedRelation r
     GROUP BY r.ServiceId

Does this help? FOR XML PATH, TYPE).value('.','nvarchar(max)') — smoore4, Oct 17 '17 at 17:25

score 0 · Answer 1 · answered Oct 17 '17 at 17:31

0

Stop using FOR XML PATH for concatenation. If you are using SQL Server 2017 you can use STRING_AGG. If not, you can implement the SQL String Utility Functions - look for the Concatenate class. More information ca be found here.

Having function that concatenate strings but it is aggregate at the same time, gives you the ability to write more complex grouping queries. It also simplify the used T-SQL syntax and improve performance.

For example, your query will looks like:

SELECT ServiceId
      ,[dbo].[Concatenate] (Name)
FROM dbo.ServiceUsedRelation
GROUP BY ServiceId;

answered Oct 17 '17 at 17:31

gotqn

42,737
46
157
243

Yes I know that but for now 2014 :( – carlosm Oct 17 '17 at 17:34
You can implement SQL CLR Aggregate. It really deserves to spent some time. – gotqn Oct 17 '17 at 17:35
@gotqn: Yea, but even SQL-Server 2008R2 is still supported until September 2019, so no. – Stefan Steiger May 23 '19 at 08:44
@StefanSteiger I am not sure I get your point? If you need to use older sql version you can implement custom CLR aggregate. – gotqn May 23 '19 at 14:08
@gotqn: Yes, you can do that, in theory, and I did for example. But then,if your customer base is sufficiently large, there is always at least ONE customer that will not want to give you the rights to install the CLR module (or rather not to enable the CLR in the first place), and so we had to find another way. Pitty if your customers pay well - then you have to do what THEY want, even if it makes little sense for you. And since SQL-Server 2008R2 is still supported until start of december, you cannot use it until then, if your application needs to run before that date. – Stefan Steiger May 23 '19 at 14:11
@StefanSteiger I see your point. It depends on the volume of data and how fast the concatenation needs to be done - if you can afford waiting or your data is small and using XML PATH does not affect performance - you are OK :-) we have used this technique five or six years with no worriers ... alternatively, you can use some kind of precalculation in order to get data in CSV ready to be read. – gotqn May 23 '19 at 15:55

Using FOR XML PATH, TYPE).value('.[1]','nvarchar(max)')

1 Answers1