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 GOBased 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
- Dozens more answers to tough SQL Server questions from Greg Robidoux and Jeremy Kadlec are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in November 2003