Bulk load XML files to SQL SERVER 2K

Server|xml

For project requirements, to export data from one server's SQL and then import it to another SQL Server, project requirements cannot be synchronized with simple data, so you want to use XML data format processing. On the internet to see a lot of articles are the same, said not very detailed, examples are all, the basic can not understand. Now there are two questions:

1. Is there a better way to deal with datetime type fields?

2, primary key duplication problem, do not ignore but replace how to deal with? What's the argument?

Here is my Code, which consists of three parts: form a schema file, create XML data from SQL Server, import XML into SQL Server

Include file tablename content:

<%
CONST TABLENAME = "UserInfo"
Const ConnString = "Provider=SQLOLEDB.1;Data source=.; Uid=sa; pwd=;d Atabase=reporthzej "
%>

Form a schema file:

<!--#include file= "tablename.asp"-->
<%
Dim sFileName
sFileName = Server.MapPath (".") & "\schema\" &tableName& ". xml"

Dim Conn
' Database connection objects
Set conn = CreateObject ("ADODB. Connection ")
Conn. ConnectionString = connstring
Conn.provider = "sqlxmloledb.3.0"
Conn.Open
' Conn.Open ' provider=sqlxmloledb.3.0;data provider=sqloledb;data source=jcbdell;initial catalog=lwgtest;user id= SAJT;PASSWORD=SAJT; "
' conn.cursorlocation = 3 ' adUseClient

Dim cmd
' Create a Command object
Set cmd = CreateObject ("Adodb.command")
Cmd. ActiveConnection = conn
' CMD. Properties ("clientsidexml") = "True"

' Generate the SQL statement for the mapping file
Cmd.commandtext = "SELECT top 0 * out" &tableName& "FOR XML auto, XMLData"

Dim Stmxmlout
Set stmxmlout = CreateObject ("ADODB. Stream ")
Stmxmlout.open
Cmd. Properties ("Output Stream"). Value = Stmxmlout
Cmd. Properties ("XML root") = "root"

Cmd. Execute, 1024 ' adExecuteStream

stmxmlout.position = 0
' Stmxmlout.charset = ' utf-8 '
' Stmxmlout.readtext (Adreadall)

Set Fs = Server.CreateObject ("Scripting.FileSystemObject")
' Save the generated XML file to the schema directory, with the file name as the table name and the extension. Xml
IF fs.fileexists (sfilename) Then
Fs.deletefile sFileName, True
End IF

Stmxmlout.savetofile (sFileName)

' Load the generated XML file
Set xmldoc = Server.CreateObject ("MSXML2. DOMDocument ")
Xmldoc.async = "false"

Xmldoc.load (sFileName)

' Adding attributes to schema elements
Set objnodelist = Xmldoc.getelementsbytagname ("Schema")
Call Objnodelist.item (0). setattribute ("Xmlns:sql", "Urn:schemas-microsoft-com:xml-sql")

' Add attributes to the ElementType element
Set objnodelist = Xmldoc.getelementsbytagname ("ElementType")
Call Objnodelist.item (0). setattribute ("Sql:relation", TableName)

' Save XML file
Xmldoc.save (sFileName)

Set Xlmdoc = Nothing
Set stmxmlout = Nothing
Conn. Close
Set conn = Nothing

%>
<script language= "JavaScript" >
Alert ("The mapping file has been regenerated!") ");
</script>

To create XML data from SQL Server:

<!--#include file= "tablename.asp"-->
<%
' Generate XML file
Dim Conn
' Database connection objects
Set conn = CreateObject ("ADODB. Connection ")
Conn. ConnectionString = connstring
Conn.provider = "sqlxmloledb.3.0"
Conn.Open

Set Fsser = Server.CreateObject ("Scripting.FileSystemObject")

Dim Cmdser
Set Cmdser = CreateObject ("Adodb.command")
Set cmdser.activeconnection = conn

Dim Stmxmloutser
Set Stmxmloutser = CreateObject ("ADODB. Stream ")

Stmxmloutser.open
Cmdser.properties ("Output Stream") = Stmxmloutser
' Cmdser.commandtext = ' select * from ' &tableName& ' for XML Auto '
Cmdser.commandtext = "Select UName, Passwd,deptno, Deptname, DEPTSERVER,DEPTDATABASE,BZ, convert (nvarchar (80), Createtime as Createtime, convert (nvarchar, regtime,) as Regtime,parcomcode,parcomname,parcomserver, Parcomdatabase,mustparcom,lxr,tel,email, Handtel,nprop,nreg,parcomdataserver,deptdataserver,ftpuser,ftppass from UserInfo FOR XML Auto "' has date-time fields
Cmdser.properties ("XML root") = "root"

Cmdser.execute, 1024 ' adExecuteStream

Call DEALERROR (conn)

stmxmloutser.position = 0

FileName = "d:\test\" &tableName& ". xml"

IF fsser.fileexists (FileName) Then
Fsser.deletefile Filename,true
End IF

Stmxmloutser.savetofile (FileName)

Set conn = Nothing
Set Cmdser = Nothing
Set Stmxmloutser = Nothing
Set Fsser = Nothing
Response.Write "Generated XML file"
Response.End

' Error handling, displaying error messages for data connections
Sub Dealerror (Conn)
If Conn.Errors.Count > 0 Then
Response.Write "<table align=center>"
Response.Write "<tr><td Align=center><font face= Song body size=2 color=red> system has taken place" &Conn.Errors.Count & "a mistake! </font>< td></tr> "
For ErrorID = 0 to Conn.errors.count-1

Response.Write "<TR><TD align=center><font face= Arial size=2 color=red> Error" &ErrorId& ":" & Conn.errors (ErrorID). description& "</font><td></tr>"
Next
Response.Write "</table>"
End If
End Sub
%>

Import XML to SQL SERVER:

<!--#include file= "tablename.asp"-->
<%
' Storing data for XML files

' first create an XML file bulk loaded object
Set Objblser = CreateObject ("Sqlxmlbulkload.sqlxmlbulkload")
Objblser.connectionstring= connstring

' The path and file name of the error log when an error occurred
Objblser.errorlogfile = Server.MapPath (".") & "\error.xml"
' Ignore primary health repeat error message
Objblser.ignoreduplicatekeys = True
' Check Constraint
Objblser.checkconstraints = True

' Execute (map file, data file)
Schemafile = "D:\test\Schema\" &tableName& ". xml"
datafile = "d:\test\" &tableName& ". xml"
Response.Write Schemafile & "<br>"
Response.Write DataFile & "<br>"
Objblser.execute Schemafile, DataFile

Set objblser=nothing
%>

The above method speed is quite fast! If the project is not constrained to apply.