CODEWITHSUNDEEP
sqlsql-serverxmlt-sqlsql-server-2008
will
will

Reputation: 35

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 tried using the xmlelement() but could not get it to work.

Upvotes: 1

Views: 2570

Answers (3)

prem
prem

Reputation: 1

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

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332571

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.

Upvotes: 2

driis
driis

Reputation: 164291

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.

Upvotes: 1

Related Questions