SQL Server 2008：如何使用SQL从Query输出XML？

I would like to declare a variable 'XMLOutput' and have it produce the contents of a table in XML format. If you could provide a really simple example I could work off of I would really appreciate it. I tried using the xmlelement() but could not get it to work.

我想声明一个变量'XMLOutput'并让它以XML格式生成表的内容。如果你能提供一个非常简单的例子我可以解决,我会非常感激。我尝试使用xmlelement()但无法使其工作。

3 个解决方案

#1

SQL Server provides the ability to generate XML based on table structure via the FOR XML clause. It's options are:

SQL Server提供了通过FOR XML子句基于表结构生成XML的功能。它的选择是:

RAW
AUTO
PATH
EXPLICIT

There are examples for each in the link.

链接中的每个都有示例。

#2

Try using

FOR XML RAW

In the end of your query. This will return results as XML. Does that do what you want ? If not, I think you might need to elaborate your question a bit further. You could also have a look at the documentation, to see what options you have with FOR XML.

在您的查询结束时。这将以XML格式返回结果。这样做你想要的吗?如果没有,我想你可能需要进一步阐述你的问题。您还可以查看文档,了解FOR XML的选项。

#3

create xml schema collection cricketschemacollection
AS N'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLschema">
<xsd:element name="MatchDetails">
<xsd:complexType>
<xsd::complexContent>
<xsd:restiriction base="xsd:anyType">
<xsd:sequences>
<xsd:element name="Team" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd::complexContent>
<xsd:restiriction base="xsd:anyType">
<xsd:sequences/>
<xsd:attribute name="country"type="xsd:string"/>
<xsd:attribute name="score"type="xsd:string"/>
</xsd:restiriction>
</xsd::complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequences>
</xsd:restiriction>
</xsd::complexContent>
</xsd:complexType>
</xsd:element>
</xsd:schema>'

#1