Meng Xianhui
Due to the true platform independence of XML (Extensible Markup Language: eXtensible Markup Language), it is gradually becoming the main 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 Chapter E 1757281793923net_lover1807581793923" This set of data, it is difficult to see what it means literally, and it is not clear how many data segments it consists of. However, if we use XML to describe it as follows, we can clearly see The meaning of each data segment:
<PersonData>
<Person>
<Name>Mencius Chapter E</Name>
<Height>175</Height>
<Weight>72</Weight>
<Tel>81793923</Tel>
</Person>
<Person>
<name>net_lover</name>
<Height>180</Height>
<Weight>75</Weight>
<Tel>81793923</Tel>
</Person>
</PersonData>
From the above piece of XML, we can not only clearly see what each data represents, but also know where the data is divided. In our usual applications, the results we get may be in the form of arrays, collections or recordsets. How do we convert them into self-describing XML format data? From the data form point of view, XML is a simple text format of pure strings. Strings are very simple, fast and easy to transfer. Arrays are sometimes very slow to transfer by reference and are very troublesome to process. , and collections and record sets are both objects, which will cause a decrease in computer performance during processing, and these objects are associated with a specific platform, which requires the platform to have a built-in processing mechanism to handle object operations. XML is already a W3C standard and is platform-independent. The only requirement for our computers is to be able to process simple XML strings, that is, an XML parser. It can parse XML strings and can easily decompose data through an interface. into independent data segments so that we can access them. XML parsers are small, performant and can be found on every platform. Once we receive the XML data and parse it into the style of the above example, we can convert them into different representations through XSLT (eXstensible Stylesheet Language Transformations). Using XML data format for data transmission will make our work of writing application code simpler and easier, and has good scalability.
Next, let's take a look at how to convert our data. Our example is written under Microsoft Windows 2000, IIS5, MSXML3 and ADO2.6. The sample data uses the Northwind sample database that comes with Microsoft SQL Server7.0. The reason why we use SQL Server7 instead of SQL Server2000 that supports XML is to consider the principle of universality. Our purpose is to process the record sets obtained from different types of data sources, not just to support XML output like SQL Server2000. data source. Use ADO because it has various forms and can handle different types of data sources; use XML because it can transmit and parse quickly. But the processing method in this example is also suitable for any environment with Microsoft XML parser, ADO2.5 or above version of Windows, IIS, SQL Server.
For simplicity, we only select products whose unit price is less than or equal to 20 US dollars, whose inventory is greater than or equal to 20 US dollars, and whose product name is 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 use 3 methods to convert the record set we obtained into XML format.
First, we can traverse the entire record set, use XML DOM (Document Object Model), and build 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 have an XML DOM object. The performance of this method is not ideal when the recordset is large, because the ADO recordset object and the 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 flaw of the above two methods is that the code cannot be reused. We have written down the names of the nodes. If we query different fields, we must also manually change our code to meet the needs of different nodes. Our approach below will become more general.
The third method: reusable method.
<%
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 is to directly use the built-in save method of the recordset, which can automatically convert the contents of the recordset into XML format. After we call the save method, we can immediately release the recordset object instance in the memory. . The save method has two parameters: one is the place where the XML is to be saved, and the other is an indicator indicating the format in which the data is saved. We can save the data as an XML DOM object (ADO STREAM object), or directly save it as an ASP RESPONSE object. For the sake of generality, we save it as an XML DOM, and use the adPersistXML ADO constant for the second parameter. Here's how:
<%
Const adPersistXML = 1
Dim objXMLDOM
Set objXMLDOM = Server.CreateObject("MSXML2.DOMDocument.3.0")
objRecordset.save objXMLDOM, adPersistXML
Set objRecordset = Nothing
%>
This method is convenient, fast, and error-free. There is no need to manually change the node name for different queries. However, the XML produced by this method is not concise enough. Take a look at the result 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="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">
<s:datatype
dt:type="number"
rs:dbtype="currency"
dt:maxLength="8"
rs:precision="19"
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="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 what nodes and attributes are allowed in this XML and what data types are used, and the data nodes are also increased namespace. Schema information may be useful where data validation is required or for more complex processing, but, in most cases, we are using thin clients and we do not need schema information. We can use XSLT to separate out the information we want and remove redundant information. Therefore, 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 reusable features and is 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 above format is the style of node set we often see. If you don't want to process the field into a node, but process it into an attribute node, then we only need to make slight changes to DataCleaber.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: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 using the new style, which is much shorter than using nodes to represent the length of the field. The transfer speed will be faster:
<xml>
<row ProductName="Chai" UnitPrice="18" UnitsInStock="39"/>
<row ProductName="Konbu" UnitPrice="6" UnitsInStock="24"/>
</xml>
So far, we have introduced several ways to obtain XML format data from ADO record sets, and we have also obtained the most simplified string. But there are several issues that you still need to pay attention to. Some field values have characters that are not supported in XML, such as: "'< >&, such as the name of P&G Procter & Gamble, the name of Chef Anton's Gumbo Mix product, etc. You must do this when converting Carry out encoding processing. There are issues to note when using the save method in the Microsoft ADO 2.6 SDK: 1. The save method only works on open Recordset; 2. Recordsets with fields of adVariant, adIDispatch, and adIUnknown types are not supported. savw; 3. There are two limitations when saving hierarchical recordsets (data shapes): parameterizations and recordsets containing unresolved updates cannot be saved.
In order to further improve performance, you can put the conversion work into COM/COM+ components, and the ASP code only performs the final presentation of the 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, converts the result into XML, and finally only strings the simple XML string back to the ASP program, and ASP can Use XSLT to convert the XML and send the result to the browser.