Language-integrated querying of XML data in SQL server

  • Published on

  • View

  • Download


  • Language-Integrated Querying of XML Data in SQL Server

    James F. TerwilligerPortland State University

    Sergey Melnik and Philip A. BernsteinMicrosoft Research, USA

    {Sergey.Melnik, Phil.Bernstein}

    ABSTRACTDevelopers need to access persistent XML data program-matically. Object-oriented access is often the preferredmethod. Translating XML data into objects or vice-versais a hard problem due to the data model mismatch and thedifficulty of query translation. Our prototype addresses thisproblem by transforming object-based queries and updatesinto queries and updates on XML using declarative map-pings between classes and XML schema types. Our proto-type extends the ADO.NET Entity Framework and lever-ages its object-relational mapping capabilities.

    We demonstrate how a developer can interact with storedrelational and XML data using the Language IntegratedQuery (LINQ) feature of .NET. We show how LINQ queriesare translated into a combination of SQL and XQuery. Fi-nally, we illustrate how explicit mappings facilitate data in-dependence upon database refactoring.

    1. INTRODUCTIONXML data has become ubiquitous in data-centric appli-cations. Many commercial database management systemssupport XML storage. Yet, the problem of translating be-tween XML and objects automatically is largely unsolved,due to differences in the expressive power of their type sys-tems [5] and the difficulty of translating object queries intoan XML query language such as XQuery. In hybrid rela-tional/XML databases, this problem is compounded by theobject-relational impedance mismatch, since XML data canbe partitioned across multiple relational tables.

    Several object-relational mapping (ORM) frameworks [2]have emerged to help application developers bridge objectsand relations. They leverage the performance and scalabilityof databases by translating queries on objects into equivalentqueries in SQL. However, typically ORMs do not handle themismatch between objects and XML. Recently, substantialresearch was done on the XML-relational mismatch, includ-ing shredding XML into relations and rewriting XQuery as

    Research conducted at Microsoft during an internship

    Permission to copy without fee all or part of this material is granted providedthat the copies are not made or distributed for direct commercial advantage,the VLDB copyright notice and the title of the publication and its date appear,and notice is given that copying is by permission of the Very Large DataBase Endowment. To copy otherwise, or to republish, to post on serversor to redistribute to lists, requires a fee and/or special permission from thepublisher, ACM.Submitted to VLDB 08 Aukland, New ZealandCopyright 2008 VLDB Endowment, ACM 000-0-00000-000-0/00/00.

    SQL. However, as far as we know this line of work did notconsider programming language integration.

    We developed a prototype, called LINQ-to-Stored XML,that enables programmatic access to persistent XML andrelational data from .NET applications by using explicitmappings between object classes, XML schema types, andrelations. The mappings drive query and update process-ing. They can be generated automatically or provided bythe developer. Using the language-integrated query (LINQ)feature of .NET [7], the developer can write object queriesthat resemble SQL but are statically compiled and type-checked in an object-oriented programming language. Ourprototype translates LINQ queries into a mixture of SQLand XQuery to execute in the database, using the nativeSQL dialect and XML features of that database. Our im-plementation extends the ADO.NET Entity Framework [1]and leverages its object-relational mapping capabilities. Thedemonstration runs on Microsoft SQL Server 2005.

    Our research goes beyond the capabilities of existingXML-to-object mapping tools in two important ways. First,existing tools work only on XML documents while theyare in memory; LINQ-to-Stored XML supports in-memorytranslation of XML documents into objects, but can alsopush queries to the database. Second, existing tools we areaware of can map XML to objects only in a pre-determined,canonical fashion. LINQ-to-Stored XML can begin with acanonical mapping, but then allows the programmer to ad-just or rewrite the mappings to suit the needs of the appli-cation, e.g., to support schema evolution.

    Section 2 details the capabilities of our prototype andwhat we demonstrate. We then describe the run-time anddesign-time components of our prototypes implementationin Section 3. We conclude in Section 4 by comparing ourcontributions against related work.

    2. WHAT IS DEMONSTRATEDOur running example is based on AdventureWorks, a sampledatabase distributed with SQL Server 2005. This databasecontains several tables whose columns store XML data. Forexample, the table JobCandidate shown in Figure 1(a) has acolumn Resume whose contents are XML documents. Fig-ure 2(a) shows part of the XML schema for those documents.

    2.1 Querying XML as Strongly-Typed ObjectsWe start by demonstrating how XML data that has an asso-ciated XML schema can be queried using classes mapped toXML schema types. The following C# program uses LINQto list email addresses and schools attended by job candi-


    Permission to make digital or hard copies of portions of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. Copyright for components of this wor k owned by others than VLDB Endowment must be honored. Abstracting with credit is permitted. To copy otherwise, to republish, to post on servers or to redistribute to lists requires prior specific permission and/or a fee. Request permission to republish from: Publications Dept., ACM, Inc. Fax +1 (212)869-0481 or PVLDB '08, August 23-28, 2008, Auckland, New Zealand Copyright 2008 VLDB Endowment, ACM 978-1-60558-306-8/08/08

  • JobCandidate




    Resume (XML)







    Name (XML)






    Employment (XML Collection)

    Education (XML Collection)

    Address (XML Collection)

    FK FK

    (a) (b)

    Telephone (XML)

    Figure 1: (a) The relational portion of the job candidate table in the AdventureWorks database and (b) analternative representation where the XML data has been refactored into multiple tables

    (a) (c)

    public partial class TResume { public TName Name; public string Skills; public List Employment; public List Education; public List Address; public TTelephone Telephone; public string EMail; public string WebSite; }


    Figure 2: Part of mapping (c) between an XML schema type (a) and a class (b)

    dates who have an email address and a Bachelors degree:

    using(AWdb db = new AWdb()) {

    var q = from c in db.JobCandidates

    from e in c.Resume.Education

    where c.Resume.EMail != null &&


    select new { c.Resume.EMail, e.School };

    foreach (var i in q)



    The var q declaration indicates that the return type of thequery is inferred by the compiler (as a collection of stringpairs). We illustrate how the query can be written easily us-ing the IntelliSense feature of Microsoft Visual Studio 2008,which automatically suggests member names such as De-gree and School (they belong to the TEducation class whosedefinition is omitted for brevity, as are subelements of Ed-ucationType). Our prototype translates the query into thefollowing SQL and XQuery:

    WITH XMLNAMESPACES(http://.../Resume AS r)


    C.Resume.value(*[1]/r:EMail, varchar(max)),

    E.value(./r:Edu.School, varchar(max))

    FROM HumanResources.JobCandidate C

    CROSS APPLY C.Resume.nodes(*[1]/r:Education) T(E)

    WHERE C.Resume.exist(*[1]/r:EMail)=1 AND


    The functions value, nodes, and exist are SQL Server-specific directives that apply XQuery expressions to XMLfragments. We generate the XPath fragments in the query(e.g., *[1]/r:EMail) based on mappings that describe rela-tionships between XML and objects. We describe thesemappings in more detail in the implementation section.

    2.2 Querying XML using Loosely-Typed Ob-jects and Embedded XPath

    Not all XML schema types can be mapped to strongly-typedclasses. For instance, the declared type of an XML elementmay be xsd:anyType, which does not have a statically-typed object counterpart any more descriptive than anyXML data. Also, mixed-content elements are hard to mapto strongly-typed objects due to text nodes that may besprinkled between child elements. Finally, the developermay prefer to query persistent XML directly using XPath.

    Our prototype supports these scenarios by mapping XMLschema elements to a .NET type called XElement in theLINQ-to-XML API that represents an XML element [8].For example, in Figure 2 the XML schema element Address,which has an unbounded number of occurrences, is mappedto a list of XElements. Each XPath axis has a counterpartin the object layer as a method of the class XElement.

    The following LINQ query illustrates how strongly-typedand loosely-typed portions of the query can be used in a sin-gle expression. The nested subquery that ends with Any()restricts the result to those job candidates who have at leastone address with the postal code 98052. ModifiedDate has astrongly-typed .NET type DateTime, which has a memberYear and is mapped to the relational type datetime. Ad-dress is loosely-typed XML; to access the PostalCode mem-ber of an Address in variable a, we must use the Elementmethod (corresponding to the XPath child axis):

    from c in db.JobCandidates

    where c.ModifiedDate.Year

  • The above query is translated into the followingSQL/XQuery expression:

    SELECT C.Resume.query(*[1]/r:Name)

    FROM HumanResources.JobCandidate AS C

    WHERE DATEPART(year, C.ModifiedDate)

  • DB



    CLR (.Net)


    Extended entity-to-relational

    mappings (from ADO.NET

    Entity Framework)

    XML-to-object mappings


    Entity-to-object mappings

    SQL +


    Translate references to XML-

    mapped members and methods into


    Translate tree according to entity-to-

    relational mappings, then into

    vendor-specific syntax





    Shred XML data into strongly-typed objects using

    XML-to-object mappings

    Transform tuples into objects using entity-to-

    relational mapping; nest collections





    Change List

    Translate change list into DML

    statements, and pass through entity-to-

    relational mappings

    Query Processing Result Materialization Update Propagation


    Package changed/added objects into

    XML; mark as changed


    Figure 3: A high-level view of our implementation

    convenient query formulation over XML content. For in-stance, the query

    from c in db.JobCandidates select c.Resume.Address

    returns a collection of collections of XElements. The EFautomatically flattens such queries prior to execution in thedatabase and nests their results on the client. Not least, wedirectly leverage EFs built-in support for .NET functionsand data types, such as DateTime.

    The query and update translation performed at runtimeis decoupled from the schema translation algorithm used bythe tool, which is essential to support schema evolution andadvanced mapping scenarios that require data reshaping.Once we have defined or generated a set of classes, we canconstruct LINQ expressions that reference both classes thatmap to relational types and classes that map to XML types.

    We also perform some optimization during query process-ing. For instance, we collapse multiple XQuery fragmentsfrom nested SQL queries into a single XQuery expression ifpossible. Also, note that the example query from Section 2.1could be expressed almost entirely in XQuery; our transla-tion algorithm often uses relational operators to leverage therelational capabilities of the query processor and to supportqueries that span both relational and XML data.

    4. RELATED WORKSeveral tools are currently available that provide access toXML documents as strongly-typed objects. XML Beans [10]can expose XML documents as typed Java objects, whileLiquid XML [6] can expose XML documents as typed ob-jects in a variety of languages. Finally, a strongly-typedLINQ interface to XML was proposed in the initial LINQ-to-XSD work of Lammel et al [4] at Microsoft.

    Each of these tools is limited to XML in main memory.They do not push any operations to a database. In addition,they each use a fixed mapping that cannot be controlledby the user. Our work addresses both of these limitations.To the best of our knowledge, LINQ-to-Stored XML is thefirst system that supports accessing typed XML stored in adatabase through LINQ. Our XML-to-object mappings can

    be generated, but are not hardwired and can be edited atdesign-time. We believe these features allow persistent XMLprogramming to be accessible to a larger developer audience.

    Tools such as XJ [3] and LINQ-to-XML offer an XPath-like interface to loosely-typed XML objects in a program-ming environment. These tools are also limited to manipu-lating XML in memory.

    Our work was introduced at the XML07 conference[8] as a future direction for LINQ-to-XSD but has neverbeen demonstrated previously. Our proposed demonstrationshows an internal Microsoft prototype and does not implyany product commitments.

    5. REFERENCES[1] A. Adya, J. A. Blakeley, S. Melnik, S. Muralidhar,

    The ADO.NET Team. Anatomy of the ADO.NETEntity Framework. In SIGMOD, 2007.

    [2] W. R. Cook, A. H. Ibrahim. Integrating ProgrammingLanguages and Databases: What is the Problem?ODBMS.ORG, Expert Article, Sept. 2006.

    [3] M. Harren, M. Raghavachari, O. Shmueli, M. G.Burke, R. Bordawekar, I. Pechtchanski, V. Sarkar. XJ:facilitating XML processing in Java. In WWW, 2005.

    [4] R. Lammel. LINQ-to-XSD. In PLAN-X, 2007.

    [5] R. Lammel, E. Meijer. Revealing the X/O ImpedanceMismatch (Changing Lead into Gold). InDatatype-Generic Programming, Lecture Notes inComputer Science. Springer-Verlag, June 2007.

    [6] Liquid XML.

    [7] E. Meijer, B. Beckman, G. M. Bierman. LINQ:Reconciling Object, Relations and XML in the .NETFramework. In SIGMOD, 2006.

    [8] S. Pather. LINQ to XML: Visual Studio 2008,Silverlight, and Beyond. XML 07,

    [9] S. Resnick, R. Crane, C. Bowen. Essential WindowsCommunication Foundation (WCF): For .NETFramework 3.5. Addison Wesley, 2008.

    [10] XML Beans.