Microsoft SQL Server 2012 Bible

The XML Data Type

Working with XML can be broken into three main categories:

• Generating XML
• Querying XML
• Validating XML

Prior to SQL Server 2008, it was the responsibility of the application layer to produce the XML, and there was no consistent or defined way to produce the XML. Typically, it would use the XML API functions available in the programming languages to produce the desired XML, and if you have done it, it wasn't easy.

Luckily, XML started gaining acceptance, and developers saw more and more need to produce and consume XML. Developers started to see the benefit of XML, and today you see it all over in websites that produce RSS or ATOM feeds. And you can't forget XML and (WCF, Windows Communication Foundation) web services, which generate XML documents containing information to be exchanged.

SQL Server 2000 was a great first step into supporting XML with support for the T-SQL FOR XML clause. The FOR XML clause transforms the results of a T-SQL query into an XML stream. This was a huge benefit for developers who no longer needed to build XML documents in their applications. In subsequent releases Microsoft made great improvements to the FOR XML clause to make it easier to produce XML, and you see how the FOR XML clause works later in the chapter.

Querying XML wasn't a walk in the park either, in the beginning. The same XML API's that were used to produce XML were used to query the XML. This caused a lot of overhead in any application that produced or consumed XML. Something ...