Text Clause Explanation

Question

I'm working with the following code which I discovered on a third party website to help with concatenating some data (the code works, I'm just attempting to understand it better). However, I'm having trouble (new to SQL) discerning the purposes of the "[text()]" clause in line 4, and (' ') in line 8.

SELECT DISTINCT ST2.[Financial Number], 
SUBSTRING(
    (
        SELECT ','+ST1.[Clinical Event Result]  AS [text()]
        FROM ED_FCT_Q1_FY19 ST1
        WHERE ST1.[Financial Number] = ST2.[Financial Number]
              Order BY [Financial Number] 
        FOR XML PATH ('')
    ), 2, 1000) [Clinical]
FROM ED_FCT_Q1_FY19 ST2

Answer 1

According to the documentation, when using PATH mode of FOR XML,

For a column with the name of text(), the string value in that column is added as a text node

So in this case, the column is not treated as an column name alias as it would be in a normal SELECT query, but used for the purpose of XML mapping.

Answer 2

In this case, the TEXT() is just an alias, which is used as a column name. It's not a function. There is not much to understand here further.

Answer 3

You were asking for clarification of this code in a previous question, but I didn't have time to answer it when I saw it, and it looks like you've taken that question down now.

The snippet you've posted is an implementation of a common way to create a comma separated list of values from a column of results. The most frequently used variant uses STUFF, but this one uses SUBSTRING instead, which is just fine. Really, it's the FOR XML that does the magic here, anyway.

Broadly speaking, the FOR XML sub-query inside the brackets pulls the list of values you want concatenated and prepends a , to the front of each value. By dumping those results into an XML doc, you then get a comma separated list. The AS [text()] functions as a pointer to a text node, as noted in Dan Guzman's answer.

The SUBSTRING is taking that list, starting at position 2, and continuing for 1000 characters. Functionally, just stripping off that very first comma, unless your list stretches out past 1000 characters, in which case you'll get some truncation, which may or may not be what you want.

There's a more detailed explanation of the mechanics in this question: How Stuff and 'For Xml Path' work in Sql Server. You would've been unlikely to find it since the implementation you found doesn't use STUFF.

See also (courtesy of Dan's comment) https://dba.stackexchange.com/questions/207371/please-explain-what-does-for-xml-path-type-value-nvarcharmax