Use XSLT to convert an ADO record set into XML [Post]

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:

<Persondata>
<Person>
<Name> Chapter E of Mencius </Name>
<Height> 175 <Weight> 72 </weight>
<Phone> 81793923 </phone>
</Person>
<Person>
<Name> net_lover </Name>
<Height> 180 <Weight> 75 </weight>
<Phone> 81793923 </phone>
</Person>
</Persondata>

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. 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 allow us to write applications Program Code And has 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 parsed. 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.doc umentelement = 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 = "<XML>"
Objrecordset. movefirst
Do while 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 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 = "<XML>"
Objrecordset. movefirst
Do while 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 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:

<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 = "30">

<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">

DT: TYPE = "Number"
RS: dbtype = "currency"
DT: maxlength = "8"
RS: Precision = "19"
RS: fixedlength = "true"/>

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 = "39"/>

<Z: Row
Productname = "konbu"
Unitprice = "6"
Unitsinstock = "24"/>

<Z: Row
Productname = "Tofu"
Unitprice = "23.25"
Unitsinstock = "35"/>

</RS: DATA>

</XML>

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 when more complex processing is required. However, in most cases, we use thin clients and 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 ":

<? XML version = "1.0"?>
<XSL: stylesheetversion = "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 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.

<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 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:

<? XML version = "1.0"?>
<XSL: stylesheetversion = "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 a new style, which is shorter than the node to indicate the length of the field. The transmission speed will be faster:




till now, we 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, and 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, finally, only the simple XML character ring string is returned to the ASP program. asp can use XSLT to convert the XML and send the result to the browser.