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

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top