SQL Server 2008: How to use SQL to output XML from Query?

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 tr开发者_如何学Pythonied using the xmlelement() but could not get it to work.

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

• RAW
• AUTO
• PATH
• EXPLICIT

There are examples for each in the link.

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.

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>'