XML Everywhere

Can’t get enough XML in your life? I know the feeling. All those carrots and backslashes. Yum. And don’t get me started on the case sensitivity.

Fortunately, with SQL Server 2000, you can even get your SQL queries returned to you as XML. It’s as simple as adding a bit to the end of your statement.

select CustomerID, CompanyName from dbo.customers
where customerID like ‘A%’

This is your traditional statement. Boring. Try this:

select CustomerID, CompanyName from dbo.customers
where customerID like ‘A%’
for xml raw

Instead of the traditional recordset, you’ll get XML:

<row CustomerID=”ALFKI” CompanyName=”Alfreds Futterkiste”/>
<row CustomerID=”ANATR” CompanyName=”Ana Emparedados”/>
<row CustomerID=”ANTON” CompanyName=”Antonio Moreno Taquería”/>
<row CustomerID=”AROUT” CompanyName=”Around the Horn”/>

Why would you want to do this? Web services. If you write web services (and if you haven’t you probably will), you’ll likely want to serialize data sets as XML to return to the user, and doing it by hand is the pits. Let SQL Server do the work for you by returning XML up front and avoid the hassle.