T-SQL to XML - Can multiple values per attribute b

问题:

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.

Sample Code:

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

Sample Result:

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

Desired Result:

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

Any help would be greatly appreciated, thanks so much!

回答1:

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