When analyzing the data of existing databases, data is often analyzed. At this time, use 1.sql query analyzer? But it is often not intuitive, find a keyword, need to re-execute the new SQL.2.sqlxml template? But there is no need to have a new virtual directory, and some SQL statement SQLXML template does not support
There is also a similar problem when data is detached. Especially when different networks, different environments need to be re-imported, analyzed or detached, and difficult is particularly obvious. Can there be a method, you can get data from the database to analyze, and import it into the database when needed? XML is a good choice! The ADO itself supports data to XML conversion, only needs to parse its format, becoming its own XML file universal format, you can perform local analysis to perform database mapping for General XML format, you can complete the data to be re-imported into the database.
Below is an ADO data (basic data of the table) and the mutual conversion between the XML data, and the introduction of the table data is initially completed. Universal intervals (by XSD description), consider, I hope that you will enlighten me guilty.
An example of a call class: Example.asp
<% DIM ASQL (1, 1) DIM OXMLDATA
'====== Connect the database process ======' get database connection object odbconn '====== Connect database process ======
Asql (0) = "PubLable" ASQL (0, 1) = "SELECT * from Pubel 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
'// When the object property has a default value (DEFAULT ()), it can be assigned
SUB EXPORT () '// Export Data
OXMLDATA.ASQLDATA = ASQL 'Must 2D SQL statement array oxmldata.bissave = 1' default (1) Save as XML file oxmldata.bisoutput = 1 'default (0) Display XML data oxmldata.ssaveFileName = "data.xml" 'default (current time plus number) If the XML file name is saved, the XML file name oxmldata.ssavefilepath = "" "" "" "" "" "XML file path (relative path) oxmldata.sencoding =" GB2312 "DEFAULT ("GB2312") XML file encoding type OXMLDATA.EXPORT (ODBCONN) '// Export Data Process
IF (OXMLDATA.NERRCODE <> 0) THEN 'NERRCODE (error code) is 0, run success response.write oxmldata.GeterRexegesis (oxmldata.nerrcode)' NERRCODE (error code), get notes EnderRexegesis ()
End Sub
SUB import () '// Import data
OXMLDATA.SXMLFILE = "data.xml" 'must be a data source XML file (including relative path)
Oxmldata.svacancycols = "nLabelid" 'must specify the value of some fields that can not import (block field)' format "NID, DDATE" (with ',' separator field)
Oxmldata.Import (ODBCONN)
IF (oxmldata.nerrcode = 0) Then Response.write "Data Import Successful!" Else Response.write Oxmldata.GeterRexegesis (Oxmldata.Nerrcode) end if End sub%>
Class code: TRANSFORMDATA.ASP
<% Class TransformData
'********************************************************** **** 'Copyright (C) 2003' Created: Moonpiazza 'date: 2003.5.21' Description: ADO Data and XML Data Transition (ASP Implementation) 'version: 1.0' function: ADO data ( Basic data of the table) and the interval between XML data to be improved: the association (general) of the interval data (general), the amount of data volume, the speed problem '' version: Welcome improvement, relics: _) '** *********************************************************** *
'********************************************************** **** 'Public Method: Export, Import, Geterrexegesis' ************************************************* ***************** '============================= 中文 公 END =====================================================================================================================================================00= ============ common variable begin =============================
'============================= Error code definition begin ================= ============= Private m_nErrCode_NotArray Private m_nErrCode_XMLDOM Private m_nErrCode_ReadData Private m_nErrCode_WriteDataPrivate m_nErrCode_Save Private m_nErrCode_EnsFile Private m_nErrCode_ErrFile '======================= ====== Error code definition End =============================
'============================= attribute definition begin ================= ============ Private m_aSQlData Private m_bIsSavePrivate m_bIsOutputPrivate m_sSaveFileNamePrivate m_sSaveFilePathPrivate m_sXMLFilePrivate m_sVacancyColsPrivate m_nErrCodePrivate m_sEncodingPrivate m_sImportSQL
'********************************************************** **** 'Attribute: AsqlData' Status: Credible 'Type: 2D Group' Description: SQL statement array, 1 dimensional table name, 2D, corresponding SQL statement '********************* ***************************************************** Public property let Asqldata (byref p_asqldata) m_asqldata = p_asqldatand property
'********************************************************** **** 'Property: Bissave' Status: Selfable 'Type: Number (0, 1) Default (1)' Description: Whether to save the data to XML file '*********** **************************************************** Public Property Let Bissave (byref p_bissave) m_bissave = cint (p_bissave) End Property
'********************************************************** **** 'Property: Bisoutput' Status: Writable 'Type: Number (0) DEFAULT (0)' Description: Whether to display XML data when exporting data, ********************** ************************************************************** Public property let bisoutput (Byref p_bisoutput) m_bisoutput = CINT (p_bisoutput) End Property
'********************************************************** **** 'Property: SSAVEFileName' Status: Write, readable 'Type: String default (GetrndFileName ()) Description: When exporting data, if you save XML data, XML file name' ******* ****************************************************************** Public Property Let SsaveFileName (Byref p_ssavefilename) m_ssavefilename = p_ssavefilenamend propertyPublic property get ssavefilename () ssavefilename = m_ssavefilenamend property
'********************************************************** **** 'attribute: SSAVEFILEPATH' Status: WKVE, Readable ': String default ("")' Description: When exporting data, if you save XML data, XML file path (relative path) '**** *********************************************** Public Property Let SsaveFilePath (Byref P_ssavefilepath) m_ssavefilepath = p_ssavefilepathend protety
Public property Get SsaveFilePath () ssavefilepath = m_ssavefilepathend protety
'********************************************************** **** 'attribute: SXMLFILE' Status: Selfable 'Type: String' Description: When importing data, data source XML file (including relative path) '************** *********************************************** Public property let smlfile (byref p_sxmlfile) m_sxmlfile = p_sxmlfilend property
'********************************************************** **** 'Property: SVACACYCOLS' Status: Writable 'Type: String Default ("")' Format "NID, DDATE" Description: When importing data, specify certain fields Value can not import (blocking field) '********************************************************** ************ Public property let svacancycols (byref p_svacancycols) m_svacancycols = "," & p_svacancycols "," End Property
'********************************************************** **** 'Property: Nerrcode' Status: Readable 'Type: Digital Default (0)' Description: Error Code, can get comments by method geterrexegesis (byref p_nerrcode) '**************** ************************************************************* Public property Get Nerrcode () NERRCODE = M_NERRCODEEND PROPERTY '********************************************************* ***** 'Properties: SENCODING' Status: Sketched 'Type: String Default ("GB2312") Description: XML file encoding type' **************** ********************************************** Public Property Let SENCODING (Byref P_sencoding) m_sencoding = p_sencodingend property
'********************************************************** **** 'Property: SimportSql' Status: Readable 'Type: String Default ("GB2312") Description: When importing data, the generated SQL statement' ************* ************************************************ Public Property Get SimportSql () SimportSql = M_SIMPORTSQLEND Property ' ============================= attribute definition End ===================== ===========
'********************************************************** **** 'Initialization class' *************************************************** *********** Private sub coplass_initialize ()
Server.scripttimeout = 1000
m_nerrcode_noterr = 0 m_nerrcode_notarray = 1 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_noterr m_sencoding = "gb2312" End Sub
'********************************************************** **** 'Logout' ****************************************** *********** Private sub class_terminate () set m_oxmldom = Nothing set m_oxsldom = Nothing end sub
'============================= Data export begin =================== ============
'********************************************************** **** 'Process: Export (Byref P_odbconn) Description: Export Data' Parameters: 'P_ODBCONN: Database Connection Object' '********************** ******************************** Public SUB EXPORT (Byref P_odbconn) DIM NI, NMAXI DIM Stablename, SSQL Dim SDATAXML, SXSLSTR DIM SMLSTR IF (not isarray (m_asqldata) "THEN M_NERRCODE = m_nerrcode_notarray exit sub)
ON Error ResMe 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 = "" sdataxml = sdataxml & "
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> m_nerrcode_noterr) THEN EXIT SUB END IF
SDATAXML = SDATAXML & SMLSTR END IF NEXT
SDATAXML = SDATAXML & "" if (m_bisoutput) THEN CALL RESPONSEXML (SDATAXML) end if if (m_bissave) THEN CALLDATAXML (SDATAXML) end if End Sub
'********************************************************** **** 'function: getrndFileName () Description: Get random name, consisting of current time and 7-bit random numbers "********************************** ************************************************** Private function getrndFileName () DIM NMAX, NMIN DIM SRND, SDATE
Randomize
Nmin = 1000000 nmax = 9999999
SRND = INT ((Nmax - Nmin 1) * RND) nmin) SDATE = Replace (Replace (), ",", "", ":", ""), "," " ")
GetrndFileName = "_" & sdate & srnd & ".xml" End Function
'********************************************************** **** 'function: getXSL ()' Description: Get XSL file string '***************************************** ****************************** Private function getxsl () DIM SXSLSTR
SXSLSTR = "SXSLSTR = SXSLSTR &" "SXSLSTR = SXSLSTR &"
'********************************************************** **** 'function: getDataxml (byref p_stablename, byref p_ssql, byref p_odbconn) Description: Perform a single SQL, get the XML' parameter after data conversion: '1.p_stablename: The name of the table' 2.p_ssql: Read the data SQL statement '3.p_odbconn: Database connection object' '******************************************************* **************** Private function getDataXML (byref p_stablename, byref p_ssql, byref p_odbconn) Dim ORecordset Dim Smlstr, ScleanXML Dim NensDataon Error Resume Next
NensData = 0
SET ORECORDSET = p_odbconn.execute (p_ssql) if err.number <> 0 Then m_nerrcode = m_nerrcode_readdata exit function endiff
IF (not orecordset.eof) THENSDATA = 1 end if if (NensData = 1) Then orecordSet.save m_oxmldom, 1 orecordset.close set orecordset = Nothing
Scleanxml = m_oxmldom.transformNode (m_oxsldom)
Sxmlstr = "<" & p_stablename ">" smlstr = sxmlstr & scleanXML SMLSTR = SMLSTR & "" else sxmlstr = "<" & p_stablename & "/>" end if
GetDataxml = SMLSTR
END FUNCTION
'********************************************************** **** 'Process: SaveDataXML (Byref P_sxmlstr) Description: Save the string to file' parameter: 'p_sxmlstr: XML format string' *************** ********************************************** Private sub savedataxml (byref p_sxmlstr) DIM SFILEINFO
If (Len (m_sSaveFileName) = 0) Then m_sSaveFileName = GetRndFileName () End If 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 ifm_oxmldom.loadXML (p_sxmlstr)
ON Error ResMe next
m_oxmldom.save (server.mappath (sfileinfo)) if err.Number <> 0 THEN M_NERRCODE = m_nerrrcode_save evi
End Sub
'********************************************************** **** 'process: responsexml (Byref p_sxmlstr)' Description: Output XML format string to browser 'parameter:' p_sxmlstr: XML format string '************** ********************************************************** Private Sub Responsexml (Byref P_sxmlstr) response.charset = m_sencoding response.conteTtype = "text / xml" response.write p_sxmlstrend sub
'============================= Data export end ================================================================================================================================================= ============
'============================= Data import begin ================= ============
'********************************************************** **** 'Process: Import (Byref P_odbconn)' Description: Import Data 'Parameters:' P_odbconn: Database Connection Object '' ********************* ********************************* Public Sub IMPORT (Byref P_odbconn) Dim OrootNodeif (Len (M_SXMLFile <1) Then M_NerRcode = M_NERRCODE_ENSFILE EXIT SUB END IF
ON Error ResMe next
Set m_oxmldom = server.createObject ("Microsoft.xmldom") if err.Number <> 0 Then m_nerrcode = m_nerrcode_xmldom exit sub * f __oxmldom.async = false
m_oxmldom.Load (server.mappath (m_sxmlfile) if err.number <> 0 THEN M_NERRCODE = m_nerrcode_ensfile exit subunix
IF (len (m_oxmldom.xml) <1) Then m_nerrcode = m_nerrcode_errfile exit sub?
Set orootnode = m_oxmldom.documentelement set m_oxmldom = Nothing m_simportsql = getimportsql (orootnode)
Set Orootnode = Nothing
Call p_odbconn.execute (m_simportsql) if err.Number <> 0 Then m_nerrcode = m_nerrrcode_writedata exit sub? F i w
'********************************************************** **** 'function: getimportsql (byref p_odatabase)' Description: Get the string 'parameter of the XML data to SQL' parameter: 'p_odatabase: XML file root node' '*********** ************************************************************************************************ VREF P_ODATABASE DIM Otable, Orow, Odatas, Odata Dim Scolnames, Scolvalues Dim Scolname Dim SSQL, Stransactionsql
SSQL = ""
For Each Otable in P_odatabase.childnodes for Each OOW in Otable.ChildNodes
Set odatas = orow.selectnodes ("@ *")
Scolnames = "Scolvalues =" "
For Each Odata in Odatas
Scolname = odata.nodeename
IF (INSTR (LCase (CSTR (M_SvacancyCols)), LCase (CSTR ("," & Scolname & ","))) <1) Then Scolnames = Scolnames & Scolname & "," Scolvalues = Scolvalues & "" & OData .NodeValue & "'," End if Next
Scolnames = "(" SCOLNAMES, LEN (Scolnames) -2) & "" Scolvalues = "(" & Left (Scolvalues, Len (Scolvateues) -2) & ")
SSQL = SSQL & "INSERT INTO" & Otable.NodeName SSQL = SSQL & "& SCOLNAMES &" VALUES "& Scolvalues &"; "
NEXT
NEXT
Set Odata = Nothing set = Nothing set = Nothing set otable = nothing SETABLE = NOTHING
sTransactionSQL = "Set Xact_Abort On;" sTransactionSQL = sTransactionSQL & "Begin Transaction;" sTransactionSQL = sTransactionSQL & sSQL sTransactionSQL = sTransactionSQL & "Commit Transaction;" sTransactionSQL = sTransactionSQL & "Set Xact_Abort Off;"
Getimportsql = Stransactionsqlend Function
'============================== Data import end =================================================================================================================================================================== ============
'********************************************************** **** 'Function: GeterRexegesis (Byref P_nerRcode)' Description: Note 'Note' Parameters: 'P_odatabase: XML file root node' '**************** ******************************************************* PUBLIC FUNCTION GETERREXEGESIS (Byref P_nerRcode) Dim Sexegesis Dim NerrcodenerRcode = CINT p_nerrcode) SELECT CASE (NERRCODE)
Case m_nerrcode_noterr sxslstr = "Successful run!"
Case m_nerrcode_notArray SXSLSTR = "Property: The number of SQL statement is incorrect!"
Case m_nerrcode_xmldom sxslstr = "Can't create an XML document, the server must support MSXML!"
Case m_nerrcode_readdata sxslstr = "Read database data error!" & "
" SXSLSTR = SXSLSTR & "Please check" & "" SXSLSTR = SXSLSTR & "1. Database is connected" & "" SXSLSTR = SXSLSTR & " 2. Whether the statement is correct? "
Case m_nerrcode_writedata sxslstr = "Write database data error!" & "
" SXSLSTR = SXSLSTR & "Check" & "" SXSLSTR = SXSLSTR & "1. Database is connected" & "" SXSLSTR = SXSLSTR & " 2.SQL statement is correct "&"
"sXSLStr = sXSLStr &" SQL statements "&"
"sXSLStr = sXSLStr &" "& m_sImportSQL Case m_nErrCode_Save sXSLStr =" XML documents can not be saved, please check Whether there is a 'write authority'! "
Case m_nerrcode_ensfile sxslstr = "You cannot read XM data, the XML file does not exist '!" SXSLSTR = SXSLSTR & "file:" & m_sxmlfile
Case m_nerrcode_errfile sxslstr = "You cannot read XM data, XML file format error '!" SXSLSTR = SXSLSTR & "file:" & m_sxmlfile case else sxslstr = "Unknown error!" End Select
GeterRexegesis = "
" & sxslstr & "
" End Function
END CLASS%>
