This tutorial will show you the different methods of retrieving XML from SQL Server, how to control how the data is returned, and then discuss how to navigate through the returned results. You will also learn ways of viewing SQL Server tables from an XML perspective. From there you’ll learn how to load data into SQL Server using XML natively, including real-time over HTTP.

XML for Analysis is an open, web-based standard for client-server communications between OLAP systems. Since its debut in mid-2001 XML/A has been widely considered to perform poorly in comparison with proprietary, binary protocols as the overhead involved in creating XML documents at one end and then parsing them at the other end was thought to be high. Additionally, the XML output from an XML/A server is much larger in terms of actual bytes as the XML document must contain many tags which are required to describe the actual data they contain. This means that the time to transfer the data across the network is greater.

This paper proposes a number of type-system and language extensions to natively support relational and hierarchical data within a statically typed object-oriented setting. In our approach SQL tables and XML documents become first class citizens that benefit from the full range of features available in a modern programming language like C# or Java. This allows objects, tables and documents to be constructed, loaded, passed, transformed, updated, and queried in a unified and type-safe manner.

This article describes how Perl can be used to transfer data between XML and relational databases, and how XML can bridge two disparate databases. MySQL is used as an example database.