Microsoft SQLXML

Introduction

In 1999, Adam Bosworth hired me into the WebData team at Microsoft to implement XSLT on top of SQL Server as part of the SQLXML project. By the time I started in October, however, the team had decided that was too ambitious, and wanted to start with just XPath. I implemented several features, starting with XPath over Annotated Schemas, and eventually became Technical Lead for the team.

The idea behind SQLXML was that most of the world’s data at the time was locked up in relational databases, and by enabling this data to be queried and retrieved as XML, we would enable applications to combine data from many sources. We were enabling “mashups” before that term had been invented. This idea later became my inspiration for the Yahoo Query Language.

SQLXML was used by millions of customers around the world, including the European Environmental Protection Agency’s website, Jim Gray’s PhotoServer, and MSN Music Search, to name just a few.¹

Annotated XML Schemas

SQLXML required you to first write an XML Schema with annotations to describee how your desired XML structure maps to your database. These were W3C standard XSD schemas (and before that, XDR) with additional namespaced attributes to describe the mapping.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xs:element name="Customer" sql:relation="Customers">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Order" sql:relation="Orders" >
          <xs:complexType>
            <xs:attribute name="OrderID" type="xs:int" />
          </xs:complexType>
          <xs:annotation>
            <xs:appinfo>
              <sql:relationship parent="Customer" child="Order"
                                   parent-key="CustomerID" child-key="CustomerID"/>
            </xs:appinfo>
          </xs:annotation>
        </xs:element>
      </xs:sequence>
      <xs:attribute name="Region" type="xs:string" />
    </xs:complexType>
  </xs:element>
</xs:schema>

You could materialize this entire document as XML by performing the root-level XPath /. However, it was more common to leave the document virtualized and extract only parts of it. For example, an XPath like /Customer[@Region='WA']/Order retrieves all the orders for all customers in WA state. Conceptually, this is similar to the SQL query shown in Example 2:

select * from Orders
where exists (
    select * from Customers
    where Customers.Region = 'WA'
      and Customers.CustomerID = Orders.CustomerID
)

Implementation

My initial C++ implementation consisted of a recursive-descent parser for XPath and then code that walked the ASTs for the XPath query and the Annotated Schema together. This worked ok, and shipped in the first few versions of SQLXML to customers.

However, I noticed that assigning a developer to every combination of query lanuage ✕ data store (in-memory XML, SQL Server databases) ✕ platform (C++,C#) was very inefficient, and a classic compiler problem. I invented the Common Query Runtime platform to solve this, which we shipped in Microsoft’s .NET Framework as System.Xml.Query².

One of the key insights was that what we were doing was related to the nested relational algebra³ and query/view composition. The annotated schema and XPath above are conceptually similar to the XQuery shown in Example 3, with a hyptothetical sql:table() function that retrieves records from the database.

declare function sql:view() {
    for $i in sql:table("Customers")
    return <Customer Region="{$i/@Region}">{
               for $j in sql:table("Orders")
               where $i/@CustomerID = $j/@CustomerID
               return <Order ID="{$i/@OrderID}"/>
          }</Customer>
};
sql:view()/Customer[@Region='WA']/Order

Instead of executing one query to construct the entire document and then another query to extract only part of it, SQLXML combines (composes) the XPath with the XML View to create a more efficient query, and then converts only this query’s smaller result set into XML.

for $i in sql:table("Customers")
where $i/@Region = "WA"
return
  for $j in sql:table("Orders")
  where $i/@CustomerID = $j/@CustomerID
  return <Order ID="{$i/@OrderID}"/>

Chris Suver, my mentor and at the time our team Architect, and I invented an XML query algebra to represent views and queries from many different XML query languages and enable performing this composition in a more rigorous way.

Additional Reading

Websites and Newsgroups

• Microsoft’s SQLXML Developer Center
• SQLXML.org
• Google Group microsoft.public.sqlserver.xml

###Books

• Programming Microsoft SQL Server 2000 with XML. Graeme Malcolm. Microsoft Press, 2002.
• The Guru’s Guide to SQL Server Stored Procedures, XML, and HTML. Ken Henderson. Addison-Wesley, 2001.
• Professional XML Databases. Kevin Williams, Michael Brundage, et al. Wrox Press, 2000.

Presentations

• Microsoft SQL Server 2000 XML Features. TechEd Europe, Barcelona, Spain. July, 2001.
• Developing XML-Driven Applications with SQL Server 2000. TechEd, Atlanta, GA and TechEd Europe, Barcelona, Spain. June-July, 2001.
• XML Views in SQL Server 2000. XML DevCon Fall, San Jose. November, 2000.

Footnotes