Home > Articles > Web Services > XML

XML Data to SQL Data: There and Back Again

  • Print
  • + Share This
Jesse Smith shows you how to use SQL Server 2000 to convert relational data to XML and vice versa, giving your applications easy XML support and compatibility. You'll learn how to make the best use of the T-SQL FOR XML clause and the OPENXML function.
Like this article? We recommend


Prior to the development of Extended Markup Language (XML), the only way to perform complicated queries and data analysis was by using a database. XML was developed to put data in a flat-file format for data retrieval and manipulation. One advantage of an XML file is that it can be used on any device, regardless of operating system! Let's say you have an application that uses an XML file for data retrieval and manipulation on a desktop using Linux, and you want to port it to a PDA using Windows CE. You only need to transfer the XML file, and you have your whole database, so to speak.

XML can be expanded to something more advanced: web services. Web services provide applications on dissimilar devices—regardless of programming language or operating system—with a means for exchanging information, using XML and Simple Object Access Protocol (SOAP) over the web. Imagine two applications on different systems written in two different languages exchanging information over the web. For example, one application could be providing a credit card service running a C# application on a Windows machine, while another could be a Java customer-management application running on Linux. Using SOAP, the customer-management application sends the credit card number, name, address, and bank code to the web service. The credit card web service then "decodes" the SOAP information back to XML for processing by the application, and so on. SOAP acts as the messenger between the two applications, putting the XML data into a more optimized format for transferring over HTTP or port 80.

You may wonder why databases still exist—are they on their way out, like typewriters replaced by word processors? Not necessarily. Companies such as Microsoft and Oracle have turned their database applications into multipurpose data distribution, replication, and management systems. When it comes to data warehousing, replication, and manageability, nothing beats the good ol' database server tools. Not to mention performance tuning and analysis, which these tools can provide on a very large and centralized scale. Both Microsoft and Oracle have added XML capabilities to their most recent versions, but for this article we'll focus on Microsoft's SQL Server 2000 XML features and support.

  • + Share This
  • 🔖 Save To Your Account