Figures

Figure 1 Terminology

Term	Description
XML schema	Define the characteristics (including ordering, data types, and relationships) of an XML document. XML schemas are either XSD schemas or XDR schemas
XSD schema	The W3C standard format for XML schemas
XDR schema	A standard for XML schemas (worked on by Microsoft), presumably to be superseded by XSD schemas
XPath query	Part of the W3C XML standard, XPath is a language for querying XML documents
XSLT	A W3C standard for transforming XML documents into other formats. Frequently used to transform XML to HTML
Mapping schema	An XSD or XDR schema with SQL annotations
SQL annotations	Used in a mapping schema to specify the relationships between the XML elements and a relational data store
SQL template	An XML document used to extract data from SQL Server 2000 as XML. Referred to simply as templates in this article, they use SQL query extensions or a mapping schema with an XPath query. Template may also specify an XSLT stylesheet to transform the XML
SQL query extension	These are extensions to the standard SQL SELECT statement, instructing the data provider to return the result as XML: SELECT * FROM Authors FOR XML AUTO
SQL updategrams	An XML document used to insert, update, and delete relational database information. Updategrams must specify a mapping schema

Figure 2 Example SQL Annotation

<xsd:schema id="persons" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
    <xsd:annotation>
        <xsd:appinfo>
            <sql:relationship name="authors_titleauthor" parent="authors" 
            parent-key="au_id" child="titleauthor" child-key="au_id" />
            <sql:relationship name="titleauthor_authors" 
            parent="titleauthor" parent-key="au_id" child="authors" 
            child-key="au_id" />
            <sql:relationship name="titleauthor_titles" 
            parent="titleauthor" parent-key="title_id" child="titles" 
            child-key="title_id" />
            <sql:relationship name="titles_titleauthor" parent="titles" 
            parent-key="title_id" child="titleauthor" 
            child-key="title_id" />
            <sql:relationship name="titles_publishers" parent="titles" 
            parent-key="pub_id" child="publishers" child-key="pub_id" />
        </xsd:appinfo>
    </xsd:annotation>
    <xsd:element name="person" sql:relation="authors" sql:key-
                 fields="au_id">
         •••
    </xsd:element>
</xsd:schema>

Figure 3 XSD-compliant Mapping Schema

<xsd:element name="person" sql:relation="authors" sql:key-fields="au_id">
  <xsd:complexType>
    <xsd:all>
      <xsd:element name="social_security_number" sql:field="au_id" type="xsd:string" />
      <xsd:element name="first_name" sql:field="au_fname" type="xsd:string" />
      <xsd:element name="last_name" sql:field="au_lname" type="xsd:string" />
      <xsd:element name="phone" sql:field="phone" type="xsd:string" />
      <xsd:element name="address" sql:is-constant="1">
                            •••
      </xsd:element>
      <xsd:element name="contract" sql:field="contract" type="xsd:integer" />
      <xsd:element name="books" sql:is-constant="1">
        <xsd:complexType>
          <xsd:all>
            <xsd:element name="book" sql:relation="titles" sql:key-fields="title_id" 
            sql:relationship="authors_titleauthor titleauthor_titles">
              <xsd:complexType>
                <xsd:all>
                  <xsd:element name="title_id" sql:field="title_id" type="xsd:string" />
                  <xsd:element name="title" sql:field="title" type="xsd:string" />
                  <xsd:element name="type" sql:field="type" type="xsd:string"
                    minOccurs="0" />
                  <xsd:element name="publisher_id" sql:field="pub_id" type="xsd:string" 
                    minOccurs="0" />
                  <xsd:element name="price" sql:field="price" type="xsd:double" minOccurs="0" />
                    <xsd:element name="advance" sql:field="advance" type="xsd:double" 
                      minOccurs="0" />
                    <xsd:element name="royalty" sql:field="royalty" type="xsd:integer" 
                      minOccurs="0" />
                  <xsd:element name="year_to_date_sales" sql:field="ytd_sales" type="xsd:integer"
                    minOccurs="0" />
                  <xsd:element name="notes" sql:field="notes" type="xsd:string" minOccurs="0" />
                  <xsd:element name="publication_date" sql:field="pubdate" type="xsd:date"
                    minOccurs="0" />
                  <xsd:element name="publisher" sql:relation="publishers" sql:key-fields="pub_id"
                    sql:relationship="titles_publishers">
                                    •••  
        </xsd:element>

                  <xsd:element name="authors" sql:is-constant="1">
                    <xsd:complexType>
                      <xsd:all>
                        <xsd:element name="author" sql:relation="titleauthor" 
                          sql:key-fields="au_id" 
                            sql:relationship="titles_titleauthor">
                              •••  
                        </xsd:element>
                      </xsd:all>
                    </xsd:complexType>
                  </xsd:element>
                </xsd:all>
              </xsd:complexType>
            </xsd:element>
          </xsd:all>
        </xsd:complexType>
      </xsd:element>

Figure 4 XML from SQL Server

<persons xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <person>
    <social_security_number>172-32-1176</social_security_number>
    <first_name>NAME CHANGE</first_name>
    <last_name>White</last_name>
    <phone>408 496-7223</phone>
    <contract>1</contract>
    <address>
      <street>10932 Bigge Rd.</street>
      <city>Menlo Park</city>
      <state>CA</state>
      <zip_code>94025</zip_code>
    </address>
    <books>
      <book>
        <title_id>PS3333</title_id>
        <title>Prolonged Data Deprivation: Four Case Studies
        </title>
        <type>psychology  </type>
        <publisher_id>0736</publisher_id>
        <price>19.99</price>
        <advance>2000</advance>
        <royalty>10</royalty>
        <year_to_date_sales>4072</year_to_date_sales>
        <notes>What happens when the data runs dry?  Searching evaluations 
        of information-shortage effects.</notes>
        <publication_date>1991-06-12</publication_date>
        <publisher>
        ...
        </publisher>
        <authors>
          <author>
            <social_security_number>172-32-1176
            </social_security_number>
            <title_id>PS3333</title_id>
            <author_order>1</author_order>
            <royalty_percentage>100</royalty_percentage>
            <last_name>White</last_name>
            <first_name>Johnson</first_name>
          </author>
        </authors>
      </book>
    </books>
  </person>
  <person>
  ...
  </person>
  <person>
  ...
  </person>
  <person>
  ...
  </person>

Figure 5 Example Updategram

<persons xmlns:updg='urn:schemas-microsoft-com:xml-updategram'>
  <updg:sync mapping-schema='persons.xsd'>
    <updg:before>
      <person>
        <social_security_number>267-41-2394
        </social_security_number>
      </person>
    </updg:before>
    <updg:after>
      <person>
        <social_security_number>267-41-2394
        </social_security_number>
        <first_name>NAME CHANGE</first_name>
      </person>
    </updg:after>
  </updg:sync>
</persons>

Figure 7 Sample Implementation Methods

Name	Data Format	Presentation Tool	Notes
ASP/ADO	ADO Recordset	ASP	The traditional approach. Get recordsets from the database and loop through them, outputting HTML
ManualXML/XSLT	ADO Recordset XML Document	XSLT	Get recordsets from the database. Convert to XML and apply XSLT to output HTML
DirectXML/XSLT	XML Document	XSLT	Get XML document directly from SQL Server 2000 using XSD schema. Apply XSLT to output HTML
DirectXML/.NET	XML Document	.NET XML Web Control	Get XML document directly from SQL Server 2000 using XSD schema. Use server control to perform XSLT transform
DirectXML/.NET	XML Document	.NET DataGrid Web Control	Get XML document directly from SQL Server 2000 using XSD schema. Use DataGrid to display the table. Note that this sample does not include the client-side DHTML

Figure 9 ShowAuthorsView

sub ShowAuthorsView()
    Dim objCon, objRS1, objRS2, objRS3, strTitleId
    Set objCon = Server.CreateObject("ADODB.Connection")
    objCon.Open "Provider=SQLOLEDB;Initial Catalog=pubs;Data _
    Source=(local)", "sa", ""

    set objRS1 = Execute(objCon, "SELECT* FROM authors ORDER BY au_lname")
    InsertAuthorHeader 
    while objRS1.EOF = false
        InsertAuthorRow( objRS1 )
        Response.Write "<tr><td colspan=4>"

        'Get this authors books
        Set objRS2 = Execute(objCon, "SELECT titles.* FROM titles, 
                titleauthor" & _
                " WHERE au_id = '" & objRS1("au_id") & _
                "' AND titles.title_id = titleauthor.title_id")
        InsertBookHeader objRS1("au_id")
        while objRS2.EOF = false
            'Get this book's authors
            strTitleId = objRS2("title_id")
            Set objRS3 = Execute(objCon, _
                 "SELECT authors.au_id, au_fname, " & _ 
                 "au_lname, au_ord, royaltyper FROM authors, titleauthor"
                 "WHERE title_id = '" & strTitleId & _
                 "' AND authors.au_id = titleauthor.au_id" & _
                 " ORDER BY au_ord")
            InsertBookRow objRS1, objRS2, objRS3
            objRS2.MoveNext
        
        wend
        InsertBookFooter
        Response.Write "</td></tr>"
        objRS1.MoveNext
    wend
    InsertAuthorFooter
End Sub

Figure 10 Summary of Solution 1

Goal	Rank	Notes
Division of labor	Very poor	All the code written required expert knowledge of the data model and the nuances of ASP and HTML. No abstraction of the data model
One transaction, one place	Very poor	Requires 49 separate recordsets (and 49 round-trips to the database!) to get the data required for the view
Look to the future	Very poor	Would not port well to .NET. No opportunity to make use of the dataset in other applications. Tight coupling with the data model

Figure 12 GetXMLFromDB

function GetXMLFromDB()
    Dim objCon, objRS1, objAuthorNode, objXML, objXMLBooks
    Dim objBookNode, objXMLAuthors, strAuId, strTitleId

    Set objCon = Server.CreateObject("ADODB.Connection")
    objCon.Open "Provider=SQLOLEDB;Initial Catalog=pubs;Data 
    Source=(local)", _
                "sa", ""
    set objRS1 = Execute(objCon, "SELECT * FROM authors ORDER BY 
        au_lname")
    set objXML = RecordsetToXMLDoc( objRS1, "author" )

    for each objAuthorNode in objXML.selectNodes("authors/author")
        'Get this authors books
        strAuId = objAuthorNode.selectSingleNode("au_id").text
        Set objRS1 = Execute(objCon, "SELECT titles.* FROM titles," &_
        titleauthor WHERE titles.title_id = " &_
        titleauthor.title_id AND au_id = strAuId & "'")

        Set objXMLBooks = RecordsetToXMLDoc( objRS1, "book" )

        for each objBookNode in objXMLBooks.selectNodes("books/book")
            'Get this book's authors
            strTitleId = objBookNode.selectSingleNode("title_id").text
            Set objRS1 = Execute(objCon, 
            "SELECT authors.au_id, au_fname, " "au_lname, au_ord," & _
            "royaltyper FROM authors, titleauthor " & _
            "WHERE authors.au_id = titleauthor.au_id and " & _
            "title_id = '" & strTitleId & "' ORDER BY au_ord")
            Set objXMLAuthors = RecordsetToXMLDoc( objRS1, "author" )
            'Add the authors to this book node
            objBookNode.appendChild 
            objXMLAuthors.selectSingleNode("authors")
        next
        'Now add this books node, to the author node
        objAuthorNode.appendChild objXMLBooks.selectSingleNode("books")        
    next
    Set GetXMLFromDB = objXML
end function

Figure 13 ShowAuthorsView

sub ShowAuthorsView()
    if Request("xmlonly") = "true" then
        Response.Write "<a href=xmlbyhand.asp>View Sample Application
                        </a><br><br>"
        Response.Write TransformXML(GetXMLFromDB(), "xsl/defaultss.xsl")
    else
        Response.Write "<a href=xmlbyhand.asp?xmlonly=true>View XML
                        </a><br><br>"
        If Request("cache") = "off" then 
            Response.Write TransformXML( GetXMLFromDB(), 
            "xsl\authors2.xsl")
        ElseIf Request("cache") = "clear" then
            ClearCachedXSLTemplate( "xsl\authors2.xsl" )
        Else    
            Response.Write TransformXML2( GetXMLFromDB(), _
                    GetCachedXSLTTemplate( "xsl\authors2.xsl") )
        End If
    end if
end sub

Figure 14 Summary of Solution 2

Goal	Rank	Notes
Division of labor	Good	It is plausible that different people could have written this code. One group writes GetXMLFromDB and the other writes the XSLT and ShowAuthorsView
One transaction, one place	Good	It still requires 49 separate recordsets (and 49 round-trips to the database!) to get the data required for the view, but eventually the data is available as one XML document
Look to the future	Good	Would port well to .NET. Dataset could be used in other applications and XSLT could be used in .NET. Dataset still has tight coupling with the data model because the element names are derived from the data model. Hierarchy of the document roughly reflects the relational model as well

Figure 16 GetXMLFromDB

function GetXMLFromDB( xsl, xpath )
    Dim objStream, objCon, objCmd, strPath
    
    strPath = Left(Server.MapPath("xmlbysql2000.asp"), _
                Len(Server.MapPath("xmlbysql2000.asp"))-16) & "sqlxml/"
    Set objStream = Server.CreateObject("ADODB.Stream")
    Set objCmd = Server.CreateObject("ADODB.Command")
    Set objCon = Server.CreateObject("ADODB.Connection")
    objCon.Open _
            "provider=SQLXMLOLEDB.2.0;data provider=SQLOLEDB;" & _
            "data source=(local);initial catalog=pubs", "sa", ""
    objCmd.ActiveConnection = objCon

    objCmd.CommandText = _
            "<persons xmlns:sql='urn:schemas-microsoft-com:xml-sql'> " & _
            " <sql:xpath-query mapping-schema='persons.xsd'> " & _
            xpath & "</sql:xpath-query></persons> "
    objStream.Open 

    ' You need the dialect if you are executing a template.
    objCmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
    objCmd.Properties("Output Stream").Value = objStream
    objCmd.Properties("Base Path").Value = strPath
    if Len(xsl) <> 0 Then
        'specify path relative to 'Base Path'
        objCmd.Properties("xsl").Value = "..\" & xsl 
    End If
    objCmd.Properties("Output Encoding") = "utf-8"

    on error resume next 'Errors are written out as XML to the stream
    objCmd.Execute "", "", 1024 '1024 = adExecuteStream
    on error goto 0

    objStream.Position = 0
    objStream.Charset = "utf-8"
    GetXMLFromDB = objStream.ReadText(-1) ' -1 = adReadAll
end function

Figure 18 Summary of Solution 3

Goal	Rank	Notes
Division of labor	Excellent	One group would write the map-ping schema, based on detailed knowledge of the database. Another group would write the XSLT and ShowAuthorsView
One transaction, one place	Excellent	One trip to the database, and the dataset is available in one XML document
Look to the future	Good	Would port well to .NET. Dataset could be used in other applications, and XSLT could be used in .NET. Dataset is not tied to the data model as all element names are specified in the template (can be different from what's stored in the database). Complete control of the XML hierarchy is also available

Figure 20 Summary of Solution 4

Goal	Rank	Notes
Division of labor	Excellent	One group would write the mapping schema, based on detailed knowledge of the database. Another group would write the XSLT. No expertise required to write the transform code because this is handled by the XML Web control
One transaction, one place	Excellent	One trip to the database, and the dataset is available in one XML document
Look to the future	Good	Uses .NET XML Web control. Dataset is not tied to the data model as all element names are specified in the template (can be different from what's stored in the database). Complete control of the XML hierarchy is also available

Figure 22 Page Load

private void Page_Load(object sender, System.EventArgs e)
{
    // Put user code to initialize the page here
    dsPersons.ReadXml(new 
        XmlTextReader("http://localhost/pubs/templates/alldata.xml"));
            dgperson.DataBind();
            
    // Set the css class for all of the column headers
    for (int i = 0; i < dgperson.Columns.Count; i++ )
        dgperson.Columns[i].HeaderStyle.CssClass = "clsHeader";
            
        }

Figure 24 Summary of Solution 5

Goal	Rank	Notes
Division of labor	Excellent	One group would write the mapping schema, based on detailed knowledge of the database. Another group required to configure the DataGrid
One transaction, one place	Excellent	One trip to the database, and the dataset is available in one XML document
Look to the future	Excellent	Uses .NET DataGrid Web control. The DataGrid has many possibilities for providing more enhanced features (paging, editing, validation, and so forth) that don't have to be hand-coded. Dataset is not tied to the data model as all element names are specified in the template (can be different from what's stored in the database). Complete control of the XML hierarchy is also available

Figure 25 Benchmarking Results

Solution Number	Name	HTTP-based Data Access	XSL Caching	Trips to DB	Execution Time (ms) **	How Much Faster than ASP/ADO Approach
1	ASP/ADO			49	1292	N/A
2	ManualXML/XSLT			49	324	4.0×
2A	ManualXML/XSLT			49	316	4.1×
3	DirectXML/XSLT			1	128	10.1×
3A	DirectXML/XSLT			1	140	9.2×
3B	DirectXML/XSLT			1	116	11.1×
3C	DirectXML/XSLT			1	129	10.0×
4	DirectXML/.NET (transform)			1	150	8.6×
5	DirectXML/.NET (DataGrid)			1	158	8.2×
* XSLT Transform, using cached stylesheet and XSLTemplate ** As recorded by Homer (time to last byte)