FOR XML PATH in SQL server and [text()]

Question

I found some article on internet this url

Then I code query like that and I get same result

But when I change AS [text()] to [name]

the result contain XML tag

like this

So My question is What is [text()] in this code

Thank you.

Answer 1

The other current answers don't explain much about where this is coming from, or just offer links to poorly formatted sites and don't really answer the question.

In many answers around the web for grouping strings there are the copy paste answers without a lot of explanation of what's going on. I wanted to better answer this question because I was wondering the same thing, and also give insight into what is actually happening overall.

tldr;

In short, this is syntax to help transform the XML output when using FOR XML PATH which uses column names (or aliases) to structure the output. If you name your column text() the data will be represented as text within the root tag.

<row>
    My record's data
<row>

---

In the examples you see online for how to group strings and concat with , it may not be obvious (except for the fact that your query has that little for xml part) that you are actually building an XML file with a specific structure (or rather, lack of structure) by using FOR XML PATH (''). The ('') is removing the root xml tags, and just spitting out the data.

The deal with AS [text()]

As usual, AS is acting to name or rename the column alias. In this example, you are aliasing this column as [text()]. The []s are simply SQL Server's standard column delimiters, often unneeded, except today since our column name has ()s. That leaves us with text() for our column name.

Controlling the XML Structure with Column Names

When you are using FOR XML PATH you are outputting an XML file and can control the structure with your column names. A detailed list of options can be found here: https://msdn.microsoft.com/en-us/library/ms189885.aspx

An example includes starting your column name with an @ sign, such as:

SELECT color as '@color', name
FROM #favorite_colors
FOR XML PATH

This would move this column's data to an attribute of the current xml row, as opposed to an item within it. You end up with

<row color="red">
  <name>tim</name>
</row>
<row color="blue">
  <name>that guy</name>
</row>

So then, back to [text()]. This is actually specifying an XPath Node Test. In the context of MS Sql Server, you can learn about this designation here. Basically it helps determine the type of element we are adding this data to, such as a normal node (default), an xml comment, or in this example, some text within the tag.

An example using a few moves to structure the output

SELECT 
  color as [@color]
  ,'Some info about ' + name AS [text()]
  ,name + ' likes ' + color AS [comment()]
  ,name
  ,name + ' has some ' + color + ' things' AS [info/text()]
FROM #favorite_colors
FOR XML PATH

Notice we are using a few designations in our column names:

• @color: a tag attribute
• text(): some text for this root tag
• comment(): an xml comment
• info/text(): some text in a specific xml tag, <info>

The output looks like this:

<row color="red">
    Some info about tim
    <!--tim likes red-->
    <name>tim</name>
    <info>tim has some red things</info>
</row>
<row color="blue">
    Some info about that guy
    <!--that guy likes blue-->
    <name>that guy</name>
    <info>that guy has some blue things</info>
</row>

Wrapping it up, how can these tools group and concat strings?

So, with the solutions we see for grouping strings together using FOR XML PATH, there are two key components.

• AS [text()]: Writes the data as text, instead of wrapping it in a tag
• FOR XML PATH (''): Renames the root tag to '', or rather, removes it entirely

This gives us "XML" (air quotes) output that is essentially just a string.

SELECT name + ', ' AS [text()] -- no 'name' tags
FROM #favorite_colors
FOR XML PATH ('')  -- no root tag

returns

tim, that guy, 

From there, it's just a matter of joining that data back to the larger dataset from which it came.

Answer 2

While righting the sql query remove alias name then you got the text.

select name+',' aa from employee for xml path('')

then the result comes in xml with aa tag.

select (select name+','  from employee for xml path('')) aa