Use XSLT to convert an ADO record set into XML

Because xml (Extensible Markup Language: eXtensibleMarkupLanguage) is truly platform independent, it is gradually becoming the main medium for data transmission. XML is a self-describing language. The data itself contains metadata, that is, information about the data itself. Because xml (eXtensible Markup Language: eXtensible Markup Language) is truly platform-independent, it is gradually becoming the main medium for data transmission. XML is a self-describing language. The data itself contains metadata, that is, information about the data itself. For example, in the data set "chapter 1757281793923net_lover1807581793923" of Mencius, it is hard to see what it means or whether it contains several data segments. However, if XML is used as the following description, we can clearly see the meaning of each data segment:

   
     
   <姓名>
    
Chapter E of Mencius
      
   <身高>
    
175
      
   <体重>
    
72
      
   <电话>
    
81793923
     
      
     
   <姓名>
    
Net_lover
      
   <身高>
    
180
      
   <体重>
    
75
      
   <电话>
    
81793923
     
   
 

From the XML above, we can not only clearly see what each data represents, but also know the data split location. In our common applications, the result may be an array, set, or record set. how can we convert them into self-described XML data? In terms of data form, XML is a simple text format of pure strings, which is very simple, fast, and easy to transmit, arrays are sometimes very slow to pass through references, and difficult to process. collections and record sets are both objects, which may lead to a reduction in computer performance during processing, these objects are associated with specific platforms, which requires the platform to have a built-in processor to process object operations. XML is already W3C standard and platform-independent. The only requirement of our computer is to be able to process simple XML strings, that is, the XML parser, which can parse XML strings, data can be easily divided into independent data segments through an interface, so that we can access the data in advance. The XML parser is very small and has good performance. it can be found on every platform. Once we receive XML data and parse it into the style of the above example, we can convert them into different forms through XSLT (eXstensible Stylesheet Language Transformations. Using XML data format for data transmission will make it easier for us to write application code and have good scalability.
Next, let's look at how to convert our data. The example is written in Microsoft Windows 2000, IIS5, MSXML3, and ADO2.6. the sample data adopts the Northwind sample database that comes with Microsoft SQL Server7.0. SQL Server2000, which uses SQL Server7 instead of XML, is a universal principle. our goal is to process the record set obtained from different data sources, it is not just a data source that supports XML output like SQL Server2000. ADO is used because it has various forms and can process different types of data sources. XML is used because it can be quickly transmitted and analyzed. However, this example is applicable to any Windows, IIS, and SQL Server with the Micrsoft XML parser, ADO2.5 or later versions.
For simplicity, we only select products with a unit price less than or equal to 20 US dollars, inventory greater than or equal to 20, and product names less than or equal to 6 characters:

<%  Dim objRecordset  Set objRecordset = Server.CreateObject("ADODB.Recordset")  objRecordset.open _      "SELECT PRoductName, UnitPrice, UnitsInStock " _          & "FROM Products " _          & "WHERE UnitPrice <= 20 " _          & "AND UnitsInStock >= 20 " _          & "AND LEN(ProductName) <= 6 " _          & "ORDER BY ProductName", _      "Provider=SQLOLEDB;" _          & "Data Source=SomeSQLServer;" _          & "Initial Catalog=Northwind;" _          & "User ID=MyUserName;" _          & "PassWord=MyPassword;"  %>

Now, we can convert the set of records into XML format in three ways.
First, we can traverse the entire record set and use xml dom (Document Object Model) to create an XML node tree:

<%  Dim objXMLDOM, objRootNode, objNode  Set objXMLDOM = Server.CreateObject("MSXML2.DOMDocument")    Set objRootNode = objXMLDOM.createElement("xml")  objXMLDOM.documentElement = objRootNode    Do While NOT objRecordset.EOF      Set objRowNode = objXMLDOM.createElement("row")        Set objNode = objXMLDOM.createElement("ProductName")      objNode.text = objRecordset.Fields.Item("ProductName").Value      objRowNode.appendChild(objNode)        Set objNode = objXMLDOM.createElement("UnitPrice")      objNode.text = objRecordset.Fields.Item("UnitPrice").Value      objRowNode.appendChild(objNode)        Set objNode = objXMLDOM.createElement("UnitsInStock")      objNode.text = objRecordset.Fields.Item("UnitsInStock").Value      objRowNode.appendChild(objNode)        objRootNode.appendChild(objRowNode)        objRecordset.MoveNext  Loop    Set objNode = Nothing  Set objRowNode = Nothing  Set objRootNode = Nothing    Set objRecordset = Nothing  %>

Now we get an xml dom object. This method is not ideal for the performance when the record set is large, because the ADO record set object and xml dom object must be stored in the system memory at the same time.
The second method is to traverse the record set and directly generate the XML string itself:

 <%  Dim strXML  strXML = "
 
  "  objRecordset.MoveFirst  Do While NOT objRecordset.EOF      strXML = strXML & "
  
   "      strXML = strXML & "
   
    " _          & objRecordset.Fields.Item("ProductName").Value _          & "
   "      strXML = strXML & "
   
    " _          & objRecordset.Fields.Item("UnitPrice").Value _          & "
   "      strXML = strXML & "
   
    " _          & objRecordset.Fields.Item("UnitsInStock").Value _          & "
   "      strXML = strXML & "
  "      objRecordset.MoveNext  Loop  strXML = strXML & "
 "  Set objRecordset = Nothing  %>

However, the biggest drawback of the above two methods is that code cannot be reused. we have left the node names dead. if we query different fields, we must also manually change our code to meet the needs of different nodes. The following methods will become more common.
Method 3: reusable methods.

  <%  Dim strXML  strXML = "
 
  "  objRecordset.MoveFirst  Do While NOT objRecordset.EOF      strXML = strXML & "
  
   "      For Each varItem In objRecordset.Fields          strXML = strXML _              & "<" & varItem.name & ">" _              & varItem.value _              & "
   "      Next      strXML = strXML & "
  "      objRecordset.MoveNext  Loop  strXML = strXML & "
 "  Set objRecordset = Nothing  %>

A more effective method, we can directly use the built-in save method of the record set, which can automatically convert the content of the record set into XML format. after we call the save method, we can immediately release the record set object instance in the memory. The save method has two parameters: one is where XML is to be saved, and the other is an indicator indicating the format in which data is saved. We can save the data as an xml dom object (ado stream object) or an asp RESPONSE object. for general purpose, we can save the data as an xml dom, the second parameter uses the adPersistXML ADO constant. The method is as follows:

  <%  Const adPersistXML = 1  Dim objXMLDOM  Set objXMLDOM = Server.CreateObject("MSXML2.DOMDocument.3.0")  objRecordset.save objXMLDOM, adPersistXML  Set objRecordset = Nothing  %>

This method is convenient and fast, and is not prone to errors. you do not need to manually change the node name for different queries. However, the XML generated by this method is not concise enough to see the result:

The XML automatically generated by ADO contains schema information, which describes the nodes and attributes allowed in the XML and the data type used, and the namespace is also added for the data nodes. Schema information may be useful where data verification is required or for more complex processing. However, in most cases, we use thin clients, and we do not need schema information. We can use XSLT to separate the desired information and remove unnecessary information. Therefore, we compile the following "DataCleaner. xsl ":

This XSLT is reusable and applicable to different query results. The following is an example of how to use this XSLT:

  <%  Dim strCleanXML, objXMLDOM_XSLT    Set objXMLDOM_XSLT = CreateObject("MSXML2.DOMDocument")  objXMLDOM_XSLT.load(Server.MapPath("DataCleaner.xsl"))  strCleanXML = objXMLDOM.transformNode(objXMLDOM_XSLT)    Set objXMLDOM = Nothing  Set objXMLDOM_XSLT = Nothing  %>

After the above processing, strClaenXML is the XML string we want.

 
       
            
   
    Chai
             
   
    18
             
   
    39
         
        
            
   
    Konbu
             
   
    6
             
   
    24
         
    
 

The XML string in the preceding format is the style of the node set we often see. if you do not want to process a field as a node and process it as a property node, you only need to perform the following operations on DataCleaber. xsl can be slightly changed:

The following is the result of using a new style, which is shorter than the length of the field expressed by nodes. The transmission speed will be faster:

So far, we have introduced several methods to get XML format data from the ADO record set, and also obtained the most simplified string. However, you still need to pay attention to several issues. some field values also contain characters not supported in XML, such as: "'<> &, such as the name of P & G company, chef Anton's Gumbo Mix product name, etc. encoding is required during conversion. Note the following when using the save method in the SDK of Microsoft ADO 2.6: 1. the save method only works for open Recordset; 2. adVariant and adIDispatch are not supported, the savw of the record set of the adIUnknown type field; 3. when the hierarchical record set (data shapes) is saved, there are two restrictions: the parameterized record and the record set that contains unsolved updates cannot be saved.
To further improve the performance, you can put the conversion work in the COM/COM + component. ASP code only performs the final performance of data. Separate the business layer, data layer, and presentation layer. ASP only needs to call the data component. The data component calls the stored procedure of the database and converts the result to XML, the last step is to return the simple XML character ring string to the ASP program. ASP can use XSLT to convert the XML and send the result to the browser.

The preceding section uses XSLT to convert an ADO record set to XML. For more information, see PHP Chinese website (www.php1.cn )!