XML-supported data solutions for Web Development

XML-supported data solutions for Web Development released on: 4/1/2004 | updated on: 4/1/2004

Scott Howlett and Darryl Jennings this article assumes that the reader is familiar with XML, ASP, and ADO. Download the Code provided in this article: sqlxml.exe (516kb)

SummaryUsing XML for data access allows you to separate data from the representation and improve reusability, scalability, and manual allocation. XML also has a simplified data model that makes testing easier. This article provides and compares five data access methods that use various technologies, including ASP, ADO, XSLT, and directxml. After a solution is built, compare it based on their speed and efficiency.

Suppose you are designing a data-driven Internet application. You need to obtain data from the database for display on the web. In addition, you want to ensure that the solution architecture is good, but does not take performance as the main consideration. You are eager to extend the solution for downstream traffic (from the database to the browser) to the upstream traffic (from the browser to the database.

This article isMsdn_MagazineSubsequent content in the article "Beyond ASP: XML and XSL-based solutions simplify your data presentation layer" in June September 1, 2000. Before discussing the various solutions implemented for the above solutions, we will first review the article quickly, and then clarify some topics based on the large amount of feedback we have received over the past 10 months.

Next, we will give an overview of SQL Server 2000's support for XML, focusing on templates and update charts using the XSD ing architecture. Then, we will introduce the sample applications that implement these ideas in practice. Basically, you can use five different methods to generate the same sample application. each time you use different combinations of ASP, ADO, XML, XSLT, SQL Server 2000, And. NET technologies. Finally, a benchmark test number is included to address performance and scalability concerns when developers use an XML-based structure.

Review

In the past, we used XML and XSL for all types of solutions, from large enterprise applications to small projects within five or ten days. We found that most of the XML resources are actually concentrated on XML as the Enterprise Technology. Therefore, most websites are still being constructed using the traditional ASP method, this method has not been changed since it was first introduced in Microsoft _ Internet Information Server (IIS) 3.0.

Since the year when the "Beyond asp" article was published, the situation has changed a lot. It is worth noting that MSXML October 2000 (W3C standard XSLT supported) was released in 3.0, and later. and then released SQL Server 2000, and soon two web releases that enhance its XML function. In short, these new technologies enhance XML-centric methods for almost all Internet-based applications. In fact, to optimize the development of Microsoft. NET-based servers, XML must be considered as the center of the structure.

XML-centered Internet development methods have become the mainstream. At the same time, database interfaces are being transformed from SQL to bidirectional XML, which may provide many new powerful functions. Here, we will mainly discuss how emerging technologies can turn this design concept into reality.

Another thing to note-we have spent a lot of time on the sample code (or even provided a self-reported file to help set the content), so please try it. All the code in this article can be found in the link at the top of this article.

Nine reasons for using XML

XML promotes the separation of representation and data, improving reusability, scalability, and manual allocation. In addition, it has a simplified data model that allows a transaction at a location and helps simplify testing. It is also increasingly popular because XML can be integrated with legacy systems (which will continue to grow in the future.

Here, we will focus on three of them:

•	1. Manual allocation. Different team members have different skills, and well-designed solutions give full play to the skill levels of each team member. To effectively execute this task, you must abstract the relational data model from the front-end developers.
•	2. One Transaction in one location. XML provides complete transactions and complex datasets in a single portable entity. This Portable Data container spans the record set or other specialized data formats.
•	3. Looking forward to the future. Microsoft has adopted XML as one of the key technologies of the. NET platform. We will discuss the. NET platform when verifying. net enterprise-level servers.

Each of the five sample applications will be measured based on the three standards.

 

XML and XSLT

It is important to realize that there is no cross-browser problem when XSLT is used-if the client does not support XSLT, the conversion can always be performed on the server. In addition, you will find that the HTML generated by XSLT is more in line with the HTML standard than the HTML obtained by other methods.

You will also find that XSLT is as powerful as Process Code in generating complex HTML pages. In addition, you can always call out the content of numerous exceptions to the script. As an example of XSLT capabilities, you can convert EDI documents into XML BizTalk mapper using the underlying XSLT. Once you are familiar with it, XSLT is more modular (and therefore more maintainable) than the Process Code with embedded HTML ).

Using XML does not cause disk input/output problems. Since all XML documents are loaded into the memory, the XML/XSLT method does not produce more disk I/O operations than other methods. Developers have been explicitly concerned with the performance when using script code (rather than compiled COM objects. Frankly speaking, we use scripts to generate sample applications because they are easier for users to download, install, and run on their own systems. In any event, there will be negligible performance differences between the script code and the com code based on Visual Basic _ on Windows _ 2000. Don't you trust us? See architecture decisions for Dynamic Web applications: performance, scalability, and reliability.

Developers also pay attention to the performance when using the recordsettoxmldoc function. This function loops through records to convert ADO record sets into XML documents to generate xml. Why shouldn't I use ADODB. recordset. Save adpersistxml (which generates a two-dimensional XML structure )? Obviously, it has more advantages than simply using record sets. Besides, you cannot control the generated XML structure. The powerful feature of XML is that it can be used to model any deep layered data-limiting yourself to a two-dimensional hierarchy (results caused by using adpersistxml) greatly reduces the XML functionality.

Another performance concern is the number of execution layers exposed by the XML solution. If you allow manual conversion of record sets to XML, you can add another layer. However, the software is completely abstract, which usually means introducing another layer. You will soon see that using the features introduced in SQL Server 2000 can achieve all the abstract benefits (without introducing another layer), and the performance will be greatly improved at the same time.

Like any new technology, it takes some time to get familiar with XML and XSLT. Remember, sooner or later you must learn XML-just glance at BizTalk and. Net at will and you will see that Microsoft has used XML somewhere. Figure 1 is a convenient reference guide for some terms you need to know.

 

XML Functions of SQL Server 2000

Many developers have pointed out that (indeed) the conversion from record set to XML introduces another execution layer. Even so, we will still find many benefits in the XML/XSLT design pattern. SQL Server 2000 comes with local XML support, and SQL Server 2000 WEb Release 2 beta 1 (wr2b1) even adds more XML-based functions, thus avoiding layer issues. Now, we can get all the benefits of XML without introducing an additional execution layer, thus avoiding the complexity caused by introducing the execution layer.

Microsoft developers have done a lot of work in accessing SQL Server 2000 XML through HTTP. This access method requires the virtual directory to be configured on the server. For more information, see the installation instructions for the sample application. From WEb Release 2 beta 1, SQL Server 2000 XML can also be programmed using ADO commands, streams, and connection objects. To better understand how to use both HTTP and programming access methods and their execution methods, we use them to develop third-party sample applications. To our surprise, HTTP-based access is much faster than programming through ADO (see the performance section at the end of this Article ).

There are two different ways to generate an XML view based on the content of the SQL database. You will see that the generated XML can be based on one or more tables, and can be any depth. The first method uses the SELECT statement syntax extended by SQL Server 2000, and the second method uses SUPPORT FOR THE XPath query and ing architecture. The SQL Extension function can be used together with the template or not, and the ing architecture is not required. On the contrary, the XPath query must be used in the template and the ing architecture must be specified in this template.

 

SQL query Extension

The syntax of the SELECT statement has been extended to support retrieving XML documents directly from SQL Server 2000 using the new for XML clause. The for XML clause has three usage modes. Each mode allows different control over the appearance of the generated XML document. These modes are raw, auto, and explicit. The complete explanation of how to use these methods is beyond the scope of this article, but the SQL Server 2000 documentation provides a good description. A major limitation of the SQL query Extension function is that they are not conducive to the use of update graphs (the update graph requires ing architecture ).

RAW mode (used for XML raw) to generate an XML document. recordset. the document generated by the Save adpersistxml method is similar (for more information about this method, see the ADO document ). This means that the generated XML document will have a shortest level, and will only work like a traditional record set, rather than any complex tree structure available when using XML.

Auto Mode (used for XML auto) can automatically generate XML documents based on the number and order of tables and the join policies used in the from clause of the SELECT statement. The innovative use of various join styles helps to generate many simple XML document structures, but its limitations will soon be exposed when you try to release documents with complex structures.

The explicit it mode can be used to completely control the performance of generated XML documents, so it is more complicated to use. Basically, you get full control over the XML document, but at the same time lose the benefits of the ing solution.

Note that the for XML clause has an option called xmldata. If this option is specified, it will include an automatically generated XDR scheme with XML view inline, but it is not a ing scheme.

 

SQL Template

As described above, templates can be used by specifying select statements that use SQL query extension for XML, or by specifying the ing scheme and XPath query. We believe that using the template and XSD ing scheme together with XPath queries is the most powerful mechanism for getting XML-based data directly from SQL Server 2000.

You can recall that if the XSD scheme includes all the necessary annotations required by SQL Server 2000 to process the ing from the relational data storage to the XML view, it is a ing scheme. In particular, the SQL: Relationship tag must be used to clearly define foreign key relationships. These relationships must be included in the XSD: annotation mark to make the ing scheme conform to the XSD. Figure 2 shows an example of these annotations.

Figure 3 shows the XSD ing scheme for the sample application without displaying the annotations in Figure 2 to save space. (If you are not familiar with the XSD solution, see http://www.w3.org/tr/xmlschema-0 .) Note how to map tables and columns in relational data storage to XML elements using properties: SQL: Relation ing table, SQL: Key-fields ing primary key, SQL: Field ing column, SQL: relationship ing key relationship.

For many reasons, the ing scheme and template are preferred for SQL expansion. For example, in any case, you need a ing scheme to use an update graph, and the complex ing scheme is easier to write than the for XML explicit equivalent scheme. In addition, you need to write select statements to use the SQL query Extension function, but you do not need to add the ing scheme. You will also find that the ing scheme is very useful for both. NET and BizTalk schemes. Finally, the best implementation of XML specifies the use of the scheme.

In addition, the XSD preferred level is higher than XDR because XSD is W3C standard. This template generates an XML view of the data using the XSD ing scheme.

<Persons xmlns: SQL = "urn: Schemas = Microsoft = com: xml = SQL"> <SQL: XPath-query mapping-schema = :. /persons. XSD:> person </SQL: XPath-query> </persons> Visual Studio. net designer will show you the XSD Scheme (SQLXML/persons. XSD ). The preceding template also generates XML data as shown in Figure 4.

 

SQL update Diagram

If you are not interested in extracting data from an SQL database using a customizable form that complies with the XSD ing scheme, remember that you can also use the update graph. The update graph allows you to provide the XML document that conforms to the scheme to be mapped back to the database, instead of writing your own decomposition code and inserting code.

The second SQL Server 2000 web version includes support for graph update. They allow you to insert, update, and delete information about a relational database based on the XML document and the corresponding ing scheme. This allows SQL Server 2000 to become a two-way XML library! Users can now request information, modify, add, and delete information in XML format, and send it back in XML format so that it can be correctly mapped and stored in relational databases.

Update graphs are easy to use (although the error messages are usually not clear at this time ). In the update diagram, all work is completed between <sync> blocks. It can include any number of <sync> blocks, and all the changes made in them are transactional. Each transaction can contain any number of before blocks and after blocks.

The functions of these blocks are very intuitive. <Before> A block contains a part of an existing XML view. <after> A block is the view that you want to display when you make changes. Based on the differences between <before> and <after> blocks and the ing scheme you provide, you can complete the insertion, update, and deletion tasks for you. (For more information about how to use the update graph, see the "XML for SQL" document provided with the second Web release ). Note that updating a graph can affect multiple rows and multiple tables. Figure 5 shows an example of an update chart for modifying data in the pubs database.

How does the update chart handle concurrent access to the database, that is, to prevent user B from overwriting user a's updates? The answer lies in the flexible use of Optimistic Locking policies in the update chart. Basically, SQL Server 2000 compares the content of the <before> block in the update graph with the existing content in the database. If the data is different, this operation will not continue because Optimistic Locking is detected.

The more information you contain in the <before> block, the stricter the conflict detection. By only including key fields, your updates will always be applied unless the key field no longer exists. If you download the sample code in this article, you will find the sample update graph and the ADO Execution Code. The Execution Code only includes the primary key in the <before> block.

Therefore, we have a mechanism to use the template and XSD ing scheme to retrieve XML directly from SQL Server 2000. We also have a mechanism to use the update graph and the same XSD ing scheme to directly obtain the XML to SQL Server 2000. All these operations do not even need to be written into a row of SQL statements. Cool.

 

Sample Application

Next, let's jump directly to the sample application so that you can understand what is being generated. We decided to display the author and related books from the pubs database. Figure 6 shows the data generation view. The data view is somewhat complex. It includes a DHTML generated by a server that allows you to expand or collapse the books of each author as needed.

 

Figure 6 DHTML read-only view

The sample page is generated using five different methods shown in 7. Each of these solutions/sample applications is described in the following sections.

 

Solution 1

The Code required to generate this page using ASP and ADO (the conceptual display shown in Figure 8) looks familiar.

 

You can find the complete code list in straightasp. asp. Figure 9 shows a part of the required code. Obviously, the data access code is helplessly entangled with the representation code. Figure 10 shows how well the solution is arranged by various standards.

 

Solution 2

In solution 2 (the conceptual definition is given in figure 11), we make the same calls to the database as in solution 1, however, HTML is not generated during execution. Instead, all the data in a function call (getxmlfromdb) is obtained, and the XSLT style sheet is applied. In this case, the data has been completely deleted from any representation format. Figure 12 shows the Code required to generate xml from the database.

 

Figure 11 #2

The article in November 2000 contains the recordsettoxmldoc function. You can find the updated version of this function in the XML/xmlutil2.asp file in the Code download. With data, it is very easy to convert. Figure 13 shows the Code required to perform the conversion.

You will notice that there are three different conversions based on the values in the query string. First, use defaultss. XSL (which displays the original XML) for conversion. The difference between transformxml and transformxml2 is that transformxml2 uses the cached style sheet stored in Application variables (through the transform template object ). If you want to use the internal template, it is ideal to start with reading the article "Inside msxml3 performance". This article also discusses how to improve the performance of this method. In INC/xmlutil2.asp (in download), you can find the transformxml and transformxml2 functions. Solution 2 is summarized in Figure 14.

 

Solution 3

Solution 3 (as shown in Figure 15) looks similar to the last solution, but we use the XML feature of SQL Server 2000 instead of generating XML manually. This process is explained in detail in the "XML Functions of SQL Server 2000" section, but what we have done is to create a data view using the XSD solution. Using this view, we specify an XPATH expression to extract the desired data. Figure 16 shows how to use the sqlxmloledb provider that comes with SQL Server 2000 wr2b1 to programmatically complete this operation.

 

Figure 15 #3

You can use ADO (through the sqxmloledb provider) or SQL Server 2000 HTTP-based access mechanism to programmatically execute an SQL template. This solution also shows two execution options using the same template for comparison. HTTP-based access is not simpler than a program, but it is faster (see the performance section at the end of this Article ).

The showauthorsview code is almost identical to the Code in solution 2, but added support for HTTP-based data access. In addition, support for different XPath expressions to be used in the template is added. Figure 17 shows the sample application that has an XPATH filter applied (this filter can only display authors with zero protocol values.

 

Use XPath for filtering

XPath queries do not always work as expected (remember to test the software ?). Here is the content found when the XPath query is executed. First, try to filter the XPath expression based on the subnode that is not immediately subnode will fail (person [books/book/title_id = 'ps333333']), and the error "XPath: an Unexpected internal error occurred "(XPath: An Unexpected internal error occurred ). Second, an attempt to filter the XPath expressions (for example, person [Address/State = 'CA']) based on the child level contained in the node marked with SQL: Is-constant fails, the error "the column prefix '_ q1g' does not match with a table name or alias name used in the query. "(the column prefix '_ q1g' does not match the table name or alias used in the query ).

It is expected that when wr2b1 for SQL Server 2000 is released, we will be able to use a large number of XPath expressions, such as person [books/book/price> 10]. it indicates the person who edited the book. The sales price of the book is more than 10 US dollars. person [books/book/publisher/Location/country = 'usa'] indicates the person who edited the book, the book publisher is in the United States. For a summary of solution 3 levels, see Figure 18.

 

Solution 4

Solution 4 (the model shown in Figure 19) works in almost the same way as solution 3. The difference is that. Net XML webcontrol is used for conversion.

 

Figure 19 #4

In the page_load method (from dotnettransform. aspx. CS), we only need to write four lines of code to load the source XML document (using XML access based on SQL Server 2000 HTTP) and style sheet.

private void Page_Load(object sender, System.EventArgs e){    // Put user code to initialize the page here    XmlDocument doc = new XmlDocument();    doc.Load("http://localhost/pubs/templates/alldata.xml");    ctlXML.Document = doc;    ctlXML.TransformSource = "xsl//authors3.xsl";}

This solution does not require code in INC/xmlutil. asp. Therefore, Dom knowledge is not required to use. Net to execute conversions. Figure 20 summarizes solution 4.

Solution 5

For solution 5 (Figure 21 provides an overview), we once thought about how to use it. net to view the functions that can be obtained without writing any code (C # Or XSLT.

 

Figure 21 #5

Obviously, generating a data view in exactly the same format as solution 1 to solution 4 is beyond the scope of this article. However, generating the author's simple view (without using the client's DHTML) is indeed a simple exercise. Almost everything can be done without writing any code! The procedure is as follows:

•	1. First, use the XSD architecture mentioned above and right-click Project | add component | data set to create a new dataset class. This will create a new XSD file and the underlying CS file automatically generated by Visual Studio. Then paste the schema (created manually) into the XSD file. You can also use the XML designer to create an XSD file.
•	2. Next, drag the dataset component from the toolbox to the page. This opens a wizard where you can create a dataset of the category based on the dataset class created in step 1. You can see in the code in Figure 22 that we call the dspersons dataset and the updated page_load event handler to retrieve data from SQL Server 2000 over HTTP.
•	3. From there, right-click the DataGrid | property builder and drag the DataGrid component from the toolbox to the ASPX page. We select the dspersons as the data source and the candidates as data members.
•	4. Next, we use the property generator to select the columns to be displayed.
•	5. From there, we configured the "pagination", "format", and "border" areas in the property generator. We added an event handler for the onpageindexchange event (by adding the onpageindexchanged = "dgperson_pageindexchanged" attribute to the <asp: DataGrid> flag in the. aspx file ). The following code is from dotnetgrid. aspx. CS and displays the server implementation to change the page.

public void dgperson_PageIndexChanged(object source,   System.Web.UI.WebControls.DataGridPageChangedEventArgs e){  dgperson.CurrentPageIndex = e.NewPageIndex;  dgperson.DataBind();}

At the end of a day's work, a data view is generated as the start point for generating a view similar to solution 1 to solution 4 (see figure 23 ).. Net complies with a large number of XML-based datasets. Figure 24 summarizes solution 5.

Figure 23. Net DataGrid

Figure 23. Net DataGrid

 

Performance

For comprehensive consideration, we conducted a benchmark test on the five solutions to compare the performance of each method. Note that solution 5 should not be compared with other solutions because their data views are different.

The test uses Homer (Microsoft's load test application) and runs on servers running Windows 2000 Server, SQL Server 2000 WEb Release 2 beta 1, and. NET Framework beta 2. Homer stores its configuration information in the Microsoft Access. MDB file. The sample code zip file already contains the Homer. MDB file, so you can open it and benchmark each of your own configurations.

The server is a Dell dimension XP st500, 550 MHz, 256 mb ram. Homer runs on a separate workstation. Pay special attention to the relative performance shown in the last column in Figure 25 (compared with the ASP/ADO method ).

Please take a look at the use of HTTP-based data access and XSLT (solution 3) seasonal exciting 11.1 times of performance improvement, using HTTP-based data access and. net transform (solution 4) has improved performance by almost nine times. Although these statistics are not completely scientific, they show the amazing performance of SQL Server 2000 and. net. In addition, the cache XSLT mechanism with over-single purification cannot always improve the performance.

 

Summary

In this Internet-centric era, there is no doubt whether XML will continue to play an important role. Microsoft has a complete XML-based product, from existing published products (such as BizTalk and SQL Server 2000) to testing products (such as. net ). The challenge for software architects is to make full use of these server products in their applications by using XML.

It is very useful to use the XSD ing architecture to enable inbound and outbound SQL Server 2000 for XML-based data. By using XSD, you can immediately port to. NET and use W3C industry standards. At the same time, you will also realize the maximum performance when using XSLT and HTTP-based data access.

For more information, see:

Beyond ASP: XML and XSL-based solutions simplify your data presentation layer

A survey of Microsoft SQL Server 2000 XML featuresFor more information, see:

A Guide to xml and its technologies

Inside msxml3 Performance

Scott HowlettAndDarryl JenningsIs imason Inc. is an Internet consulting company. Its business is mainly to generate "enterprise-to-enterprise (B2B)" and "Business Department (LOB) "the company of the application performs legacy integration. If you want to contact them, send an email to the scott.howlett@imason.com and darryl.jennings@imason.com.

This is part of msdn magazine's January 1, 2002 issue.

This magazine can be purchased either through a newsstand orSubscription.

Go to the original English page