T-SQL 到 XML - 每个属性的多个值是否可以单独列出? [英] T-SQL to XML - Can multiple values per attribute be listed on their own line?

问题描述

我目前正在使用 T-SQL 将一些数据转换为 XML.我需要在自己的行上列出属性的值.出于某种原因，T-SQL 到 XML 不断地将值连接在同一行上.

I'm currently using T-SQL to convert some data into XML. I need to list the values of an attribute on their own line. For some reason the T-SQL to XML keeps concatenating the values together on the same line.

示例代码:

SELECT
    'Fruits' AS [Attribute/@name],
    'Apple' AS [Attribute/Value],
    'Orange' AS [Attribute/Value],
    'Grape' AS [Attribute/Value]
FOR XML PATH (''), ROOT('CustomProduce'), TYPE

示例结果:

<CustomProduce>
  <Attribute name="Fruits">
    <Value>AppleOrangeGrape</Value>
  </Attribute>
</CustomProduce>

预期结果:

<CustomProduce>
  <Attribute name="Fruits">
    <Value>Apple</Value>
    <Value>Orange</Value>
    <Value>Grape</Value>
  </Attribute>
</CustomProduce>

非常感谢任何帮助，非常感谢！

Any help would be greatly appreciated, thanks so much!

推荐答案

一种(相当荒谬的)方法，以防没有人知道更好的解决方案:

One (fairly absurd) way in-case no one knows a better solution:

SELECT
    'Fruits' AS [Attribute/@name],
    '' AS [Attribute],
    'Apple' AS [Attribute/Value],
    '' AS [Attribute],
    'Orange' AS [Attribute/Value],
    '' AS [Attribute],
    'Grape' AS [Attribute/Value]
FOR XML PATH (''), ROOT('CustomProduce'), TYPE

这篇关于T-SQL 到 XML - 每个属性的多个值是否可以单独列出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！