从 SQL Server 多表查询创建单个 XML [英] Create single XML from SQL Server multiple tables query
问题描述
我想从 SQL Server 2005 查询多个表并创建一个 XML 文档并在存储过程中执行此操作.
I would like to query multiple tables from SQL Server 2005 and create a single XML document and do this in a stored procedure.
我知道我可以在一个存储过程中查询多个表,并在我的 .NET 应用程序中获得一个 DataSet
,它可以很容易地保存为 XML.但是,我正在尝试在存储过程的上下文中执行类似的操作.
I know that I can query multiple tables within a stored procedure and get a DataSet
in my .NET application that can be easily saved as XML. However, I'm trying to do something similar within the context of a stored procedure.
基本上我想做这样的事情:
Essentially I want to do something like this:
declare @x xml
select @x = x.result
from (select y.* from tabley y for xml path('y')
union
select a.* from tablea a for xml path('aa')
) as x
select @x
推荐答案
如果你想要一个接一个,你可以尝试这样的事情:
If you want them just one after the other, you can try something like this:
SELECT
(SELECT y.* FROM dbo.TableY FOR XML PATH('y'), TYPE) AS 'YElements',
(SELECT a.* FROM dbo.TableA FOR XML PATH('aa'), TYPE) AS 'AElements'
FOR XML PATH(''), ROOT('root')
返回一个类似于以下内容的 XML:
That return an XML something like:
<root>
<YElements>
<Y>
....
</Y>
<Y>
....
</Y>
......
</YElements>
<AElements>
<A>
....
</A>
<A>
....
</A>
......
</AElements>
</root>
这篇关于从 SQL Server 多表查询创建单个 XML的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!