Using XSLT to transform an ADO recordset into XML

Ado|xml| Recordset | Conversion due to the real platform-independent nature of XML (Extensible Markup Language: Extensible Markup Language), it is becoming the primary medium for data transmission. XML is a self-describing language, and the data itself already contains metadata, that is, information about the data itself. For example: "Mencius E Chapter 1757281793923net_lover1807581793923" This set of data, literally it is hard to see what it means, and it is not clear that it has several data segments, but, if you use XML to do the following description, We can clearly see what each data segment means:

<PersonData>
<Person>
< name > Mencius e Chapter </name >
< height >175< weight >72</weight >
< telephone >81793923</Tel >
</Person>
<Person>
< name >net_lover</name >
< height >180< weight >75</weight >
< telephone >81793923</Tel >
</Person>
</PersonData>

From the previous section of XML, we can see not only what each data represents, but also where the data is divided. In our usual application, we can get the results of arrays, sets, or recordsets, and how do we convert them into self-describing XML format data? In the form of data, XML is a simple, plain string text format, strings are very simple, fast, and easy to pass, and arrays are sometimes slow to pass through references and cumbersome to handle, and collections and recordsets are objects that can degrade computer performance when processed. And these objects are associated with a particular platform, which requires that the platform has built-in processing mechanisms to handle the object's operations. XML is already the standard of the consortium, is platform-independent, our computer's only requirement is to be able to handle simple XML strings, that is, XML parser, it can parse XML strings, can easily decompose the data into separate data segments through an interface so that we can access. XML parsers are small and perform well, and can be found on every platform. Once we receive the XML data and parse it into the style of the example above, we can convert them to different representations through XSLT (exstensible Stylesheet Language transformations). Using the data format of XML to carry out the transfer will make our application code easier to write and have good scalability.
Next, let's look at how to transform our data. Our example was written under Microsoft Windows 2000,IIS5,MSXML3 and ADO2.6, with sample data taking the sample Northwind database from Microsoft SQL Server7.0. Using SQL Server7 instead of XML-supported SQL Server2000 is the principle of versatility, and our goal is to handle the recordset of different types of data sources, not just the data sources that support XML output like SQL Server2000. ADO is used because it is diverse and can handle different types of data sources, using XML because it can be quickly transmitted and parsed. However, the processing of this example is also appropriate in any environment that has a micrsoft XML parser, ADO2.5 or above version of Windows,iis,sql server.
For the sake of simplicity, we only select the unit price is less than or equal to 20 U.S. dollars, inventory is greater than or equal to 20, product name is less than 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're going to use 3 ways to convert the recordset we've got into XML format.
First, we can iterate through the recordset, using the XML DOM (document Object Model) to create the XML node tree:

<%
Dim objxmldom, Objrootnode, Objnode
Set objxmldom = Server.CreateObject ("MSXML2. DOMDocument ")

Set Objrootnode = objxmldom.createelement ("xml")
Objxmldom.documentelement = Objrootnode

Do as 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've got an XML DOM object. This approach is not ideal for a large recordset, because the ADO Recordset object and the XML DOM object are saved in the system memory at the same time.
The second approach, traversing the recordset, directly generates the XML string itself:

<%
Dim strxml
Strxml = "<xml>"
Objrecordset.movefirst
Do as not objrecordset.eof
Strxml = strxml & "<row>"
Strxml = strxml & "<ProductName>" _
& ObjRecordset.Fields.Item ("ProductName"). Value _
& "</ProductName>"
Strxml = strxml & "<UnitPrice>" _
& ObjRecordset.Fields.Item ("UnitPrice"). Value _
& "</UnitPrice>"
Strxml = strxml & "<UnitsInStock>" _
& ObjRecordset.Fields.Item ("UnitsInStock"). Value _
& "</UnitsInStock>"
Strxml = strxml & "</row>"
Objrecordset.movenext
Loop
Strxml = strxml & "</xml>"
Set objRecordSet = Nothing
%>

However, the biggest drawback of the above two methods is not to be able to reuse the code, we have the names of the nodes are written dead, if we do different fields of query, we must also manually change our code to meet the needs of different nodes. The following methods will become more common.
A third approach: a reusable approach.

<%
Dim strxml
Strxml = "<xml>"
Objrecordset.movefirst
Do as not objrecordset.eof
Strxml = strxml & "<row>"
For each varitem in Objrecordset.fields
Strxml = Strxml _
& "<" & Varitem.name & ">" _
& Varitem.value _
& "</" & Varitem.name & ">"
Next
Strxml = strxml & "</row>"
Objrecordset.movenext
Loop
Strxml = strxml & "</xml>"
Set objRecordSet = Nothing
%>

A more efficient approach, we can directly use the Save method built in the recordset, which automatically converts the contents of the recordset to XML format, and when we call the Save method, we can immediately release the memory Recordset object instance. The Save method has two parameters: one is where the XML is to be saved, and one is an indicator of what format the data is saved in. We can save the data as an XML DOM object (ADO stream object) or directly as an ASP Response object, for general purposes, we save it as an XML DOM, and the second parameter is a 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 quick, and it is not easy to make mistakes, and it is not necessary to manually change the node name for different queries. However, the XML generated by this method is not concise enough to look at the results it produces:

<xml
Xmlns:s= "uuid:bdc6e3f0-6da3-11d1-a2a3-00aa00c14882"
xmlns:dt= "uuid:c2f41010-65b3-11d1-a29f-00aa00c14882"
Xmlns:rs= "Urn:schemas-microsoft-com:rowset"
xmlns:z= "#RowsetSchema" >

<s:schema id= "Rowsetschema" >

<s:elementtype
Name= "Row"
Content= "Eltonly"
Rs:commandtimeout= ">"

<s:attributetype
Name= "ProductName"
Rs:number= "1"
Rs:writeunknown= "true" >

<s:datatype
Dt:type= "string"
Dt:maxlength= "40"
Rs:maybenull= "false"/>
</s:AttributeType>

        <s:attributetype
             name= "UnitPrice"
            Rs:number= "2"
            rs:nullable= "true"
            rs:writeunknown= "true"

            <s:datatype
                 dt:type= "number"
                 rs:dbtype= "Currency"
                 dt:maxlength= "8"
                 rs:precision= "a"
                 rs:fixedlength= "true"/>
        </s:attributetype>

        <s:attributetype
             name= "UnitsInStock"
             rs:number= "3"
            rs:nullable= "true"
            rs:writeunknown= "true";

<s:datatype
Dt:type= "I2"
Dt:maxlength= "2"
Rs:precision= "5"
Rs:fixedlength= "true"/>
</s:AttributeType>

<s:extends type= "Rs:rowbase"/>

</s:ElementType>

</s:Schema>

<rs:data>

<z:row
Productname= "Chai"
Unitprice= "18"
unitsinstock= "/>"

<z:row
Productname= "Konbu"
Unitprice= "6"
unitsinstock= "/>"

<z:row
Productname= "Tofu"
Unitprice= "23.25"
unitsinstock= "/>"

</rs:data>

</xml>

ADO automatically produces XML that contains schema information that describes what nodes and attributes are allowed in the XML and what data types are used, and that data nodes add namespaces. Schema information may be useful where data validation is required or more complex processing, but, in most cases, we are using thin clients and we do not need schema information. We can use XSLT to isolate the information we want, and remove the unwanted information. So, we write the following "datacleaner.xsl":

<?xml version= "1.0"?>
<xsl:stylesheet version= "1.0"
Xmlns:xsl= "Http://www.w3.org/1999/XSL/Transform"
Xmlns:s= "uuid:bdc6e3f0-6da3-11d1-a2a3-00aa00c14882"
xmlns:dt= "uuid:c2f41010-65b3-11d1-a29f-00aa00c14882"
Xmlns:rs= "Urn:schemas-microsoft-com:rowset"
xmlns:z= "#RowsetSchema" >

<xsl:output omit-xml-declaration= "yes"/>
<xsl:template match= "/" >
<xsl:element name= "xml" >
<xsl:for-each select= "/xml/rs:data/z:row" >
<xsl:element name= "Row" >
<xsl:for-each select= "@*" >
<xsl:element name= "{name ()}" >
<xsl:value-of select= "." />
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:template>

</xsl:stylesheet>

This XSLT has a reusable feature that works for different query results, and here 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.

<xml>
<row>
<ProductName>Chai</ProductName>
<UnitPrice>18</UnitPrice>
<UnitsInStock>39</UnitsInStock>
</row>
<row>
<ProductName>Konbu</ProductName>
<UnitPrice>6</UnitPrice>
<UnitsInStock>24</UnitsInStock>
</row>
</xml>

The XML string in this format is the style of the node set we often see, and if you don't want to process the field as a node and treat it as an attribute node, then we just need to change the datacleaber.xsl slightly:

<?xml version= "1.0"?>
<xsl:stylesheet version= "1.0"
Xmlns:xsl= "Http://www.w3.org/1999/XSL/Transform"
Xmlns:s= "uuid:bdc6e3f0-6da3-11d1-a2a3-00aa00c14882"
xmlns:dt= "uuid:c2f41010-65b3-11d1-a29f-00aa00c14882"
Xmlns:rs= "Urn:schemas-microsoft-com:rowset"
xmlns:z= "#RowsetSchema" >

<xsl:output omit-xml-declaration= "yes"/>

<xsl:template match= "/" >
<xsl:element name= "xml" >
<xsl:for-each select= "/xml/rs:data/z:row" >
<xsl:element name= "Row" >
<xsl:for-each select= "@*" >
<xsl:attribute name= "{name ()}" >
<xsl:value-of select= "." />
</xsl:attribute>
</xsl:for-each>
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:template>

</xsl:stylesheet>

The following is the result of adopting a new style, which is much shorter than the length of the field represented by the node. Faster to transmit:

<xml>
<row productname= "Chai" unitprice= "unitsinstock="/>
<row productname= "Konbu" unitprice= "6" unitsinstock= "a"/>
</xml>

So far, we have introduced several ways to get XML format data from the ADO Recordset, and got the simplest string. But there are a few questions you still need to be aware of, some of the field values are not supported characters in XML, such as: "' <>&, like the name of P&g, Chef Anton ' Gumbo Mix product name, and so on, to do the conversion of the code processing. There are issues to note when using the Save method in the SDK for Microsoft ADO 2.6: The 1,save method only works on the open Recordset, 2 does not support Advariant,adidispatch, The savw;3 of a recordset for a adIUnknown type of field has two limitations when saving a hierarchical Recordset (data shapes): You cannot save a recordset that is parameterized and contains unresolved updates.
To further improve performance, you can put the conversion work into the com/com+ component, the ASP code only to perform the final performance of the data. The business layer, the data layer and the performance layer separate, the ASP only needs to call the data component, the data component calls the database the stored procedure, transforms the result to the XML, finally only then the simple XML character token string returns to the ASP program, the ASP can use the XSLT to transform the XML, the result delivers to the browser.