What is the best way to generate XML from the data in the database?

Question

If I have thousands of hierarchical records to take from database and generate xml, what will be the best way to do it with a good performance and less CPU utilization?

Answer 1

Oracle has tools for that, so I guess SQL-Server does too, but you'll need a schema. Personally for small set I use a php script I have around, but for big stuff with need for customization is another story.

Answer 2

You can output XML directly from SQL Server 2005 using

FOR XML

The results of a query are returned as an XML document. Must be used with one of the three RAW, AUTO and EXPLICIT options

RAW

Each row in the result set is an XML element with a generic identifier as the element tag

AUTO

Results returned in a simple nested XML tree. An element will be generated for each table field in the SELECT clause

EXPLICIT

Specifies the shape of the resulting XML tree explicitly. A query must be written in a particular way so that additional information about the nesting is specified

XMLDATA

Returns the schema, but does not add the root element to the result

ELEMENTS

Specifies that the columns are returned as child elements to the table element. If not specified, they are mapped as attributes

Generate an inline XSD schema at the same time using

XMLSCHEMA

You can handle null values in records using

XSINIL

You can also return data in Binary form.

You might want to have a look on MSDN for XML support in SQL Server 2005, for technologies such as XQuery, XML data type, etc.

Answer 3

That depends - if your application and database servers are on separate machines, then you need to specify which CPU you want to reduce the load on. If your database is already loaded up, you might be better off doing the XML transform on your application server, otherwise go and ahead and use SQL Server FOR XML capabilities.