Problem solve Get help with specific problems with your technologies, process and projects.

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?

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
FROM Authors
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 last published in November 2003

Dig Deeper on XML in SQL Server

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.