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.
-
for xml auto, Elements will put data into correct xml format for mewill– will2010-11-15 20:27:37 +00:00Commented Nov 15, 2010 at 20:27
Add a comment
|
3 Answers
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.
2 Comments
littlegreen
Just out of curiosity, do you know an easy way to do the reverse? I need to import a large XML file (actually a SOAP file) and currently use a bloated SSIS structure for it...
OMG Ponies
@littlegreen: That's called "shredding" - here's an article on it for SQL Server 2005
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>'
