SQL 服务器中的 FOR XML PATH 和 [text()] [英] FOR XML PATH in SQL server and [text()]

问题描述

我在网上找到了一些文章这个

所以我的问题是这段代码中的 [text()] 是什么

谢谢.

解决方案

其他当前的答案并没有太多解释这是从哪里来的，或者只是提供指向格式错误的网站的链接而没有真正回答问题.

在网络上的许多字符串分组答案中，都有复制粘贴的答案，但没有对发生的事情进行大量解释.我想更好地回答这个问题，因为我想知道同样的事情，同时也深入了解实际发生的事情.

tldr;

简而言之，这是在使用 FOR XML PATH 时帮助转换 XML 输出的语法，它使用列名(或别名)来构建输出.如果您将列命名为 text()，则数据将在根标记中表示为文本.

我的记录数据<行>

<小时>

在您在网上看到的关于如何对字符串进行分组并使用 进行连接的示例中， 可能并不明显(除了您的查询具有很少的 for xml部分)您实际上是通过使用 FOR XML PATH ('') 构建具有特定结构(或者更确切地说，缺少结构)的 XML 文件.('') 正在删除根 xml 标记，并且只是吐出数据.

处理AS [text()]

像往常一样，AS 用于命名或重命名列别名.在此示例中，您将此列别名为 [text()].[]s 只是 SQL Server 的标准列分隔符，通常不需要，除非今天我们的列名有 ()s.这给我们的列名留下了 text().

使用列名控制 XML 结构

当您使用 FOR XML PATH 时，您正在输出一个 XML 文件并且可以使用您的列名控制结构.可在此处找到详细的选项列表:https://msdn.microsoft.com/en-us/library/ms189885.aspx

示例包括以@ 符号开头的列名称，例如:

SELECT color as '@color', name来自#favorite_colors对于 XML 路径

这会将这一列的数据移动到当前 xml 行的一个属性，而不是其中的一个项目.你结束了

<name>tim</name></row><行颜色=蓝色"><name>那个家伙</name></row>

那么，回到[text()].这实际上是在指定一个 XPath 节点测试.在 MS Sql Server 的上下文中，您可以在此处了解此名称.基本上，它有助于确定我们将此数据添加到的元素类型，例如普通节点(默认)、xml 注释，或者在本示例中，标记中的某些文本.

使用几个动作来构建输出的示例

SELECT颜色为 [@color],'关于'的一些信息 + name AS [text()],name + 'likes' + color AS [comment()]，姓名,name + ' has some ' + color + ' things' AS [info/text()]来自#favorite_colors对于 XML 路径

请注意，我们在列名称中使用了一些名称:

• @color:一个标签属性
• text():这个根标签的一些文本
• comment():一条xml注释
• info/text():特定xml标签中的一些文字，

输出如下:

关于蒂姆的一些信息<!--tim 喜欢红色--><name>tim</name><info>tim 有一些红色的东西</info></row><行颜色=蓝色">关于那个人的一些信息<!--那个家伙喜欢蓝色--><name>那个家伙</name><info>那个家伙有一些蓝色的东西</info></row>

总结一下，这些工具如何对字符串进行分组和连接?

因此，对于我们看到的使用 FOR XML PATH 将字符串分组在一起的解决方案，有两个关键组件.

• AS [text()]:将数据作为文本写入，而不是将其包装在标签中
• FOR XML PATH (''):将根标记重命名为 ''，或者更确切地说，将其完全删除

这为我们提供了本质上只是一个字符串的XML"(空气引号)输出.

SELECT name + ', ' AS [text()] -- 没有 'name' 标签来自#favorite_colorsFOR XML PATH ('') -- 没有根标签

返回

tim，那个家伙，

从那里开始，只需将该数据连接回它来自的更大的数据集.

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.

解决方案

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.

这篇关于SQL 服务器中的 FOR XML PATH 和 [text()]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！