Ask the Expert

Transferring SQL Server data into XML format

Is it possible to transfer data from SQL Server into an XML format in a predefined way as specified by a client?

    Requires Free Membership to View

XML (Extensive Markup Language) has been defined by the World Wide Web Consortium and has become the defacto standard to exchange data between organizations, replacing file width text files, as well as the previous EDI (Electronic Data Interchange) standard. XML is a self describing set of tags to define the data meaning. If you have any experience programming with HTML (Hyper Text Markup Language) and open an XML file, the tags will look very familiar. After some investigation, it will be easy to recognize that HTML is responsible for layout and formatting whereas XML is responsible for describing data via a set of tags to give the data meaning. For organizations leveraging Microsoft technologies rest assured that nearly every Microsoft product, from the front to back office, has firmly embraced XML, especially SQL Server.

Retrieving data from SQL Server in XML format can easily be achieved via the SELECT statement used on a daily basis by DBAs and developers alike. The SELECT statement has the clause FOR XML with the options of either RAW, AUTO or EXPLICIT. Below is a simple example that will retrieve all of the author records from the Pubs database:

USE Pubs
GO
SELECT *
FROM Authors
FOR XML AUTO
GO
Based on your needs, I would recommend reviewing the EXPLICIT option to define the data per the client specification. For additional information review SQL Server 2000 Books Online article entitled "SELECT" as well as the article entitled "Using EXPLICIT Mode." To learn about the complete set of functionality SQL Server's XML capabilities, research the SQLXML 3.0 release from Microsoft. Good luck!

For More Information

This was first published in November 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: