使用 FOR XML 选择嵌套 Xml 元素 - 最佳实践
我正在使用 FOR XML 从 SQL Server 05 检索 Xml 格式的数据
在生成的 Xml 中嵌套元素的最佳实践是什么？
目前我正在这样做：
Select
(
Select
[Col1] As [Col1],
[Col2] As [Col2]
From [dbo].[NestedTable] As T1
Where T0.[Key] = T1.[Key]
FOR XML PATH('NestedTable'), TYPE
),
[Col1] As [Col1],
[Col2] As [Col2],
From [dbo].[TopLevelTable] As T0
FOR XML PATH('TopLevelTable'), ROOT('TopLevelTableItems')
但是，由于相关子查询，我担心此操作的性能。是否有可以使用的 Group By 类型功能，以便我可以在两个表上执行常规内部联接，并生成结果 Xml，其中顶级节点是唯一的并且具有适当的子节点集合？
编辑 鉴于缺乏回应，我似乎在要求不可能的事情。所以也许更好的问题是：
是否有一个好的/更好的方法从 SQL Server 生成 Xml，或者我应该将代码转移到 CLR 中并从那里生成？
I am working on retrieving data in Xml format from SQL Server 05 using FOR XML
What is the best practice for nesting elements in my resulting Xml?
Currently I am doing this:
Select
(
Select
[Col1] As [Col1],
[Col2] As [Col2]
From [dbo].[NestedTable] As T1
Where T0.[Key] = T1.[Key]
FOR XML PATH('NestedTable'), TYPE
),
[Col1] As [Col1],
[Col2] As [Col2],
From [dbo].[TopLevelTable] As T0
FOR XML PATH('TopLevelTable'), ROOT('TopLevelTableItems')
However, I am concerned about the performance of this due to the correlated sub query. Is there Group By type functionality that I can use so that I can do a regular inner join on the two tables and have resulting Xml where the top level nodes are unique and have the appropriate collection of child nodes?
Edit
It seems like I may be asking for the impossible given the lack of responses. So perhaps a better question is:
Is there a good / better way to be generating Xml from SQL Server or should I just shift my code out into the CLR and generate from there?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果可能的话，应该避免相关的子查询（如果表保证很小的话，这没什么大不了的）。
也许您应该首先定义 XML 模式（大概有什么东西正在使用它并需要某种格式）。
Correlated sub-queries should be avoided if possible (not a big deal if the tables are guaranteed to be small).
Perhaps you should first define the XML schema (presumably something is consuming this and expects a certain format).