DIV CSS 佈局教程網

您现在的位置: DIV+CSS佈局教程網 >> 網頁腳本 >  >> XML學習教程 >> XML詳解
ADO 資料與 XML 資料互轉的類別實作
編輯:XML詳解     

在對現有資料庫進行分析時,往往需要針對其中某一部分資料加以檢視。此時,你可能會遇到幾個難題:

1. SQL 查詢分析器?——它往往不夠直觀,想搜尋某個關鍵字,又得重新執行新的 SQL。
2. SQLXML 模板?——但不一定有權限建立新的虛擬目錄,而且某些 SQL 語法 SQLXML 模板並不支援。

資料拆分時也有類似問題。尤其當資料橫跨不同網路、不同環境,需要重新匯入再分析或拆分時,困難更是格外明顯。那麼,能不能有這樣一種做法:讓資料脫離資料庫來分析,需要時再匯入資料庫?XML 就是一個很好的選擇!

ADO 本身就支援資料到 XML 的轉換,只要解析其格式、轉成自己的 XML 通用格式,就能在本機進行分析;而對通用 XML 格式做資料庫映射,則能完成資料重新匯入資料庫的工作。

下面是一個把 ADO 資料(表的基本資料)與 XML 資料相互轉換的類別(以 ASP 實作),初步完成了表資料的匯入與匯出。至於通用表間關聯的映射(透過 XSD 描述),仍在考慮之中,歡迎先進前輩不吝指教。

呼叫類別的範例:example.ASP

<!--#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
%>

類別程式碼:TransformData.ASP

這個類別對外公開三個方法: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 之間雙向流動,擺脫對特定環境與權限的依賴。

XML學習教程| jQuery入門知識| AJAX入門| Dreamweaver教程| Fireworks入門知識| SEO技巧| SEO優化集錦|
Copyright © DIV+CSS佈局教程網 All Rights Reserved