在對現有資料庫進行分析時,往往需要針對其中某一部分資料加以檢視。此時,你可能會遇到幾個難題:
1. SQL 查詢分析器?——它往往不夠直觀,想搜尋某個關鍵字,又得重新執行新的 SQL。
2. SQLXML 模板?——但不一定有權限建立新的虛擬目錄,而且某些 SQL 語法 SQLXML 模板並不支援。
資料拆分時也有類似問題。尤其當資料橫跨不同網路、不同環境,需要重新匯入再分析或拆分時,困難更是格外明顯。那麼,能不能有這樣一種做法:讓資料脫離資料庫來分析,需要時再匯入資料庫?XML 就是一個很好的選擇!
ADO 本身就支援資料到 XML 的轉換,只要解析其格式、轉成自己的 XML 通用格式,就能在本機進行分析;而對通用 XML 格式做資料庫映射,則能完成資料重新匯入資料庫的工作。
下面是一個把 ADO 資料(表的基本資料)與 XML 資料相互轉換的類別(以 ASP 實作),初步完成了表資料的匯入與匯出。至於通用表間關聯的映射(透過 XSD 描述),仍在考慮之中,歡迎先進前輩不吝指教。
<!--#include file="transformData.ASP"-->
<%
Dim aSQL(1,1)
Dim oXMLData
'====== 連接資料庫過程 ======
' 取得資料庫連接物件 oDbConn
'====== 連接資料庫過程 ======
aSQL(0,0) = "PubLable"
aSQL(0,1) = "Select * from PubLabel where cLabelName like '%abc%' Order by nLabelID"
aSQL(1,0) = "PubUser"
aSQL(1,1) = "Select * from PubUser where cUserName like '%abc%' Order by nUserID"
set oXMLData = New TransformData
Call Export()
'Call Import()
set oXMLData = nothing
Sub Export() ' 匯出資料
oXMLData.aSQlData = aSQL ' 必須為二維 SQL 語法陣列
oXMLData.bIsSave = 1 ' default(1) 是否存成 XML 檔
oXMLData.bIsOutput = 1 ' default(0) 是否輸出 XML 資料
oXMLData.sSaveFileName = "Data.XML" ' 存檔名稱
oXMLData.sSaveFilePath = "" ' 存檔路徑(相對路徑)
oXMLData.sEncoding = "gb2312" ' XML 編碼類型
oXMLData.Export(oDbConn) ' 執行匯出
IF (oXMLData.nErrCode <> 0) Then
Response.Write oXMLData.GetErrExegesis(oXMLData.nErrCode)
End IF
End Sub
Sub Import() ' 匯入資料
oXMLData.sXMLFile = "Data.xml" ' 必須,資料來源 XML 檔
oXMLData.sVacancyCols = "nLabelID" ' 必須,指定可略過不匯入(屏蔽)的欄位
' 格式 "nID,dDate"(以逗號分隔欄位)
oXMLData.Import(oDbConn)
IF (oXMLData.nErrCode = 0) Then
Response.Write "資料匯入成功!"
Else
Response.Write oXMLData.GetErrExegesis(oXMLData.nErrCode)
End IF
End Sub
%>
這個類別對外公開三個方法:Export、Import 與 GetErrExegesis,並透過一組屬性來控制行為。以下保留其關鍵結構與註解:
<%
Class TransformData
'=====================================================
' 描述:ADO 資料與 XML 資料間的相互轉換(ASP 實作)
' 功能:ADO 資料(表的基本資料)與 XML 資料間的相互轉換
' 待改進:表間資料的關聯性(通用)、資料量大時的速度問題
' 公開方法:Export, Import, GetErrExegesis
'=====================================================
'------------- 私有變數 -------------
Private m_oXMLDOM
Private m_oXSLDOM
'------------- 錯誤代碼定義 -------------
Private m_nErrCode_NotArray ' 1
Private m_nErrCode_XMLDOM ' 2
Private m_nErrCode_ReadData ' 3
Private m_nErrCode_WriteData ' 4
Private m_nErrCode_Save ' 5
Private m_nErrCode_EnsFile ' 6
Private m_nErrCode_ErrFile ' 7
'------------- 屬性私有變數 -------------
Private m_aSQlData ' 二維陣列:一維為表名,二維為對應 SQL
Private m_bIsSave ' 是否存成 XML 檔
Private m_bIsOutput ' 是否輸出 XML 資料
Private m_sSaveFileName ' XML 檔名
Private m_sSaveFilePath ' XML 存檔路徑(相對路徑)
Private m_sXMLFile ' 匯入時的資料來源 XML 檔
Private m_sVacancyCols ' 匯入時要屏蔽的欄位
Private m_nErrCode ' 錯誤代碼
Private m_sEncoding ' XML 編碼類型
Private m_sImportSQL ' 匯入時產生的 SQL 語法
'------------- 屬性:aSQlData(可寫,二維陣列)-------------
Public Property Let aSQlData(ByRef p_aSQlData)
m_aSQlData = p_aSQlData
End Property
'------------- 屬性:bIsSave(可寫,0/1,default 1)-------------
Public Property Let bIsSave(ByRef p_bIsSave)
m_bIsSave = Cint(p_bIsSave)
End Property
'------------- 屬性:bIsOutput(可寫,0/1,default 0)-------------
Public Property Let bIsOutput(ByRef p_bIsOutput)
m_bIsOutput = Cint(p_bIsOutput)
End Property
'------------- 屬性:sSaveFileName(可讀寫)-------------
Public Property Let sSaveFileName(ByRef p_sSaveFileName)
m_sSaveFileName = p_sSaveFileName
End Property
Public Property Get sSaveFileName()
sSaveFileName = m_sSaveFileName
End Property
'------------- 屬性:sSaveFilePath(可讀寫)-------------
Public Property Let sSaveFilePath(ByRef p_sSaveFilePath)
m_sSaveFilePath = p_sSaveFilePath
End Property
Public Property Get sSaveFilePath()
sSaveFilePath = m_sSaveFilePath
End Property
'------------- 屬性:sXMLFile(可寫)-------------
Public Property Let sXMLFile(ByRef p_sXMLFile)
m_sXMLFile = p_sXMLFile
End Property
'------------- 屬性:sVacancyCols(可寫)-------------
Public Property Let sVacancyCols(ByRef p_sVacancyCols)
m_sVacancyCols = "," & p_sVacancyCols & ","
End Property
'------------- 屬性:nErrCode(可讀,default 0)-------------
Public Property Get nErrCode()
nErrCode = m_nErrCode
End Property
'------------- 屬性:sEncoding(可寫,default "gb2312")-------------
Public Property Let sEncoding(ByRef p_sEncoding)
m_sEncoding = p_sEncoding
End Property
'------------- 屬性:sImportSQL(可讀)-------------
Public Property Get sImportSQL()
sImportSQL = m_sImportSQL
End Property
'------------- 初始化類 -------------
Private Sub Class_Initialize()
Server.ScriptTimeout = 1000
m_nErrCode_NotArray = 1
m_nErrCode_XMLDOM = 2
m_nErrCode_ReadData = 3
m_nErrCode_WriteData = 4
m_nErrCode_Save = 5
m_nErrCode_EnsFile = 6
m_nErrCode_ErrFile = 7
m_bIsSave = 1
m_bIsOutput = 0
m_sSaveFilePath = ""
m_sSaveFileName = ""
m_sXMLFile = ""
m_sVacancyCols = ""
m_nErrCode = m_nErrCode_NotArray * 0 ' 0,運行成功
m_sEncoding = "gb2312"
End Sub
'------------- 注銷類 -------------
Private Sub Class_Terminate()
Set m_oXMLDOM = Nothing
Set m_oXSLDOM = Nothing
End Sub
'=====================================================
' 資料匯出
'=====================================================
' 過程:Export(ByRef p_oDbConn) —— 匯出資料
' 參數:p_oDbConn 為資料庫連接物件
Public Sub Export(ByRef p_oDbConn)
Dim nI, nMaxI
Dim sTableName, sSQL, sDataXML, sXSLStr, sXMLStr
If (Not IsArray(m_aSQlData)) Then
m_nErrCode = m_nErrCode_NotArray
Exit Sub
End If
ON ERROR RESUME NEXT
Set m_oXSLDOM = Server.CreateObject("Microsoft.XMLDOM")
Set m_oXMLDOM = Server.CreateObject("Microsoft.XMLDOM")
If Err.Number <> 0 Then
m_nErrCode = m_nErrCode_XMLDOM
Exit Sub
End If
sXSLStr = GetXSL()
m_oXMLDOM.async = false
m_oXSLDOM.async = false
m_oXSLDOM.loadXML(sXSLStr)
sDataXML = "<?XML version='1.0' encoding='" & m_sEncoding & "'?>"
sDataXML = sDataXML & "<DataBase>"
nMaxI = Ubound(m_aSQlData, 1)
For nI = 0 To nMaxI
sTableName = m_aSQlData(nI, 0)
If (Len(sTableName) > 0) Then
sSQL = m_aSQlData(nI, 1)
sXMLStr = GetDataXML(sTableName, sSQL, p_oDbConn)
IF (m_nErrCode > 0) Then Exit Sub
sDataXML = sDataXML & sXMLStr
End If
Next
sDataXML = sDataXML & "</DataBase>"
IF (m_bIsOutput) Then Call ResponseXML(sDataXML)
IF (m_bIsSave) Then Call SaveDataXML(sDataXML)
End Sub
' 函數:GetXSL() —— 取得用來把 ADO 的 rowset XML
' 轉成「以屬性表示每筆資料」的 XSL 字串
Private Function GetXSL()
Dim sXSLStr
sXSLStr = "<?XML version='1.0' encoding='" & m_sEncoding & "'?>"
sXSLStr = sXSLStr & "<xsl:stylesheet version='1.0' " _
& "xmlns:xsl='https://www.w3.org/1999/XSL/Transform' " _
& "xmlns:rs='urn:schemas-microsoft-com:rowset' xmlns:z='#RowsetSchema'>"
sXSLStr = sXSLStr & "<xsl:output omit-XML-declaration='yes'/>"
sXSLStr = sXSLStr & "<xsl:template match='/'>"
sXSLStr = sXSLStr & "<xsl:for-each select='/XML/rs:data/z:row'>"
sXSLStr = sXSLStr & "<xsl:element name='Row'>"
sXSLStr = sXSLStr & "<xsl:for-each select='@*'>"
sXSLStr = sXSLStr & "<xsl:attribute name='{name()}'><xsl:value-of select='.'/></xsl:attribute>"
sXSLStr = sXSLStr & "</xsl:for-each>"
sXSLStr = sXSLStr & "</xsl:element>"
sXSLStr = sXSLStr & "</xsl:for-each>"
sXSLStr = sXSLStr & "</xsl:template>"
sXSLStr = sXSLStr & "</xsl:stylesheet>"
GetXSL = sXSLStr
End Function
' 函數:GetDataXML —— 執行單條 SQL,取得轉換後的 XML
Private Function GetDataXML(ByRef p_sTableName, ByRef p_sSQL, ByRef p_oDbConn)
Dim oRecordset, sXMLStr, sCleanXML, nEnsData
ON ERROR RESUME NEXT
nEnsData = 0
Set oRecordset = p_oDbConn.Execute(p_sSQL)
If Err.Number <> 0 Then
m_nErrCode = m_nErrCode_ReadData
Exit Function
End If
IF (Not oRecordset.eof) Then nEnsData = 1
IF (nEnsData = 1) Then
oRecordset.save m_oXMLDOM, 1 ' 存成 XML DOM
oRecordset.close
Set oRecordset = Nothing
sCleanXML = m_oXMLDOM.transformNode(m_oXSLDOM) ' 用 XSL 轉換
sXMLStr = "<" & p_sTableName & ">" & sCleanXML & "</" & p_sTableName & ">"
Else
sXMLStr = "<" & p_sTableName & "/>"
End IF
GetDataXML = sXMLStr
End Function
' 過程:SaveDataXML —— 把 XML 字串存成檔案
Private Sub SaveDataXML(ByRef p_sXMLStr)
Dim sFileInfo
If (Len(m_sSaveFileName) = 0) Then m_sSaveFileName = GetRndFileName()
If (Len(m_sSaveFilePath) = 0) Then
sFileInfo = m_sSaveFileName
Else
IF (Right(m_sSaveFilePath,1) = "/") Then
sFileInfo = m_sSaveFilePath & m_sSaveFileName
Else
sFileInfo = m_sSaveFilePath & "/" & m_sSaveFileName
End IF
End If
m_oXMLDOM.loadxml(p_sXMLStr)
ON ERROR RESUME NEXT
m_oXMLDOM.save(Server.MapPath(sFileInfo))
If Err.Number <> 0 Then
m_nErrCode = m_nErrCode_Save
Exit Sub
End If
End Sub
' 過程:ResponseXML —— 把 XML 字串輸出到瀏覽器
Private Sub ResponseXML(ByRef p_sXMLStr)
Response.CharSet = m_sEncoding
Response.ContentType = "text/XML"
Response.write p_sXMLStr
End Sub
'=====================================================
' 資料匯入
'=====================================================
' 過程:Import(ByRef p_oDbConn) —— 匯入資料
Public Sub Import(ByRef p_oDbConn)
Dim oRootNode
If (Len(m_sXMLFile) < 1) Then
m_nErrCode = m_nErrCode_EnsFile
Exit Sub
End If
ON ERROR RESUME NEXT
Set m_oXMLDOM = Server.CreateObject("Microsoft.XMLDOM")
If Err.Number <> 0 Then
m_nErrCode = m_nErrCode_XMLDOM
Exit Sub
End If
m_oXMLDOM.async = false
m_oXMLDOM.load(Server.MapPath(m_sXMLFile))
If Err.Number <> 0 Then
m_nErrCode = m_nErrCode_EnsFile
Exit Sub
End If
If (Len(m_oXMLDOM.XML) < 1) Then
m_nErrCode = m_nErrCode_ErrFile
Exit Sub
End If
Set oRootNode = m_oXMLDOM.documentElement
Set m_oXMLDOM = Nothing
m_sImportSQL = GetImportSQL(oRootNode) ' 由 XML 反推 INSERT 語法
Set oRootNode = Nothing
Call p_oDbConn.Execute(m_sImportSQL)
If Err.Number <> 0 Then
m_nErrCode = m_nErrCode_WriteData
Exit Sub
End If
End Sub
' 函數:GetImportSQL —— 把 XML 資料轉成 INSERT 語法(含交易)
Private Function GetImportSQL(ByRef p_oDataBase)
Dim oTable, oRow, oDatas, oData
Dim sColNames, sColValues, sColName, sSQL, sTransactionSQL
sSQL = ""
For Each oTable In p_oDataBase.childNodes
For Each oRow In oTable.childNodes
Set oDatas = oRow.selectNodes("@*")
sColNames = ""
sColValues = ""
For Each oData In oDatas
sColName = oData.nodeName
If (Instr(Lcase(Cstr(m_sVacancyCols)), _
Lcase(Cstr("," & sColName & ","))) < 1) Then
sColNames = sColNames & sColName & ", "
sColValues = sColValues & "'" & oData.nodeValue & "', "
End If
Next
sColNames = "(" & Left(sColNames, Len(sColNames)-2) & ") "
sColValues = "(" & Left(sColValues, Len(sColValues)-2) & ") "
sSQL = sSQL & " Insert Into " & oTable.nodeName _
& " " & sColNames & " Values " & sColValues & " ; "
Next
Next
Set oData = Nothing: Set oDatas = Nothing
Set oRow = Nothing: Set oTable = Nothing
sTransactionSQL = "Set Xact_Abort On; "
sTransactionSQL = sTransactionSQL & " Begin Transaction; "
sTransactionSQL = sTransactionSQL & sSQL
sTransactionSQL = sTransactionSQL & " Commit Transaction; "
sTransactionSQL = sTransactionSQL & " Set Xact_Abort Off; "
GetImportSQL = sTransactionSQL
End Function
' 函數:GetErrExegesis —— 取得錯誤代碼的說明文字
Public Function GetErrExegesis(ByRef p_nErrCode)
Dim sExegesis, nErrCode
nErrCode = Cint(p_nErrCode)
Select Case (nErrCode)
Case 0
sExegesis = "運行成功!"
Case m_nErrCode_NotArray
sExegesis = "屬性:SQL 語法陣列 不正確!"
Case m_nErrCode_XMLDOM
sExegesis = "不能建立 XML 文件,伺服器必須支援 MSXML!"
Case m_nErrCode_ReadData
sExegesis = "讀取資料庫資料發生錯誤!請檢查:1.資料庫是否已連接 2.語法是否正確"
Case m_nErrCode_WriteData
sExegesis = "寫入資料庫資料發生錯誤!請檢查:1.資料庫是否已連接 2.SQL 語法是否正確"
Case m_nErrCode_Save
sExegesis = "不能儲存 XML 文件,請檢查是否對該目錄或檔案有寫入權限!"
Case m_nErrCode_EnsFile
sExegesis = "不能讀取 XML 資料,XML 檔案不存在!檔案:" & m_sXMLFile
Case m_nErrCode_ErrFile
sExegesis = "不能讀取 XML 資料,XML 檔案格式錯誤!檔案:" & m_sXMLFile
Case Else
sExegesis = "未知錯誤!"
End Select
GetErrExegesis = "<BR>" & sExegesis & "<BR>"
End Function
End Class
%>
這個類別的巧妙之處在於:匯出時,先以 Recordset.Save 把查詢結果存成 ADO 標準的 rowset XML,再用一段 XSL 把它轉換成「每筆資料以屬性表示」的精簡結構,最後包上自訂的資料表標籤;匯入時則反過來,遍歷 XML 的節點與屬性,動態拼出帶交易的 INSERT 語法寫回資料庫。如此一來,資料便能輕鬆地在資料庫與 XML 之間雙向流動,擺脫對特定環境與權限的依賴。