Q

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
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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close