Hi all,
I have a folder with about 10.000 xml files that need to be transferred to a
SQL table.
the structure of the xml file is something like this:
<?xml version="1.0" encoding="UTF-8"?>
<customer>
<id>123</id>
<sex>M</sex>
<firstName>firstname</firstName>
<lastName>Lastname</lastName>
<phone>123123</phone>
<BillingAddress>
<id>23</id>
<street1>Street1</street1>
<street2></street2>
<postalCode>12312</postalCode>
<city>City</city>
<country>DE</country>
</BillingAddress>
<ShippingAddress>
<id>23</id>
<street1>Street1</street1>
<street2></street2>
<postalCode>12312</postalCode>
<city>City</city>
<country>DE</country>
</ShippingAddress>
</customer>
How can I do this?
Has anyone a sample or a brief description how to do this?
Thank you very much!
Regards, Martin
Martin,
I have just written code for an ACTIVEX controled DTS that does exactly
what you are looking for. Ive copied and pasted the code below. Im just a
jr. developer, but I hope this helps you. You will have to change the code
so that it can "read" your xml files. Also, ive added some links to sites
that helped me accomplish this.
Ben
Links (sorry, i dont know how to do HTML links) (the second link is really
good):
http://www.eggheadcafe.com/articles/20030627b.asp
http://www.perfectxml.com/articles/xml/importxmlsql.asp
my code:
'************************************************* ***********
' Visual Basic ActiveX Script
'************************************************* ***********
Option Explicit
Function Main()
' Declare FSO Related Variables
Dim sFolder
Dim fso
Dim fsoFolder
Dim fsoFile
Dim sFileName
' Import Folder
'global variable "ImportFolder" has a value like: C:\logs\
sFolder = DTSGlobalvariables("ImportFolder")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
'reads each file in the folder and moves them to a second
folder
For Each fsoFile in fsoFolder.Files
sFileName = sFolder & fsoFile.Name
call ParseFile(sFilename)
call MoveFile(sFilename)
Next
Main = DTSTaskExecResult_Success
End Function
'************************************************* **********
' ParseFile
' This method takes as input the complete name of an xml file
' (including path) and retrieves specific information from the
' file, as needed for the Webshield SPAM reporting
'************************************************* ***********
Function ParseFile(importFile)
'Declare File Related Variables
Dim objXMLDOM
Dim objNodes
Dim objEventNode
Dim objADORS
Dim objADORS2
Dim objADOCnn
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False
'No error handling done
objXMLDOM.load importFile
'setting the format of the xml files, the eventlog has nodes
called event
Set objNodes = objXMLDOM.selectNodes("/EventLog/Event")
Set objADOCnn = CreateObject("ADODB.Connection")
Set objADORS = CreateObject("ADODB.Recordset")
Set objADORS2 = CreateObject("ADODB.Recordset")
objADOCnn.Open
"PROVIDER=SQLOLEDB;SERVER=(local);UID=sa;PWD=;DATA BASE=Northwind;"
objADORS.Open "SELECT * FROM Logs", objADOCnn, adOpenKeyset, adLockOptimistic
objADORS2.Open "SELECT * FROM Imported", objADOCnn, adOpenKeyset,
adLockOptimistic
'Record file that was imported
With objADORS2
.AddNew
.fields("FileName") = importFile
.Update
End With
'Import record(s)
For Each objEventNode In objNodes
With objADORS
.AddNew
'read an attribute of the
event
.fields("Date")=
objEventNode.selectSingleNode("@.local-time").nodeTypedValue
'read specific values of the
event
.fields("From Inside Total") =
objEventNode.selectSingleNode("Info[@.name='smtp.fr om_inside.messages']").nodeTypedValue
.Update
End With
Next
objADORS.Close
objADOCnn.Close
ParseFile = DTSTaskExecResult_Success
End Function
'************************************************* **********
' MoveFile
' This method moves a file from one folder to another so that the
' file does not get imported into the SQL database twice
'************************************************* **********
Function MoveFile(importFile)
' Declare FSO Related Variables
Dim sFolder
Dim endFolder
Dim fso
Dim fsoFolder
' Import Folder
sFolder = DTSGlobalvariables("ImportFolder")
' Imported Files Folder
endFolder = DTSGlobalvariables("saveFolder")
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
'move the file
fso.movefile importFile, endFolder
MoveFile = DTSTaskExecResult_Success
End Function
|||Hi Ben,
"Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
news:996476C5-8CC6-4444-8AC8-33FE3C601403@.microsoft.com...
> Martin,
> I have just written code for an ACTIVEX controled DTS that does exactly
> what you are looking for. Ive copied and pasted the code below. Im just
> a
> jr. developer, but I hope this helps you. You will have to change the
> code
> so that it can "read" your xml files. Also, ive added some links to sites
> that helped me accomplish this.
> Ben
Thank you very much! I think this was exactly what I was searching for.
Just one more question:
Do you start the code with a ActiveX Control in a dts package as described
in
http://www.perfectxml.com/articles/x...rtxmlsql.asp#7 ?
Regards, Martin
|||Martin,
I'm not sure I understand your question. The following are the steps to
use to be able to use the code:
1. In enterprise manager, under the specific server you are using, click on
data transfer services and then right click in the right window and create a
new package
2. Right click -> add task -> activeX script task
3. Copy in the code and then modify for your xml files
To set the global variables i use,
close the tasks and make sure non are selected, click package -> properties
and then click the tab global variables.
i hope this answers your question. if not, could you please rephrase it?
thanks.
Ben
"Martin Bübl" wrote:
> Hi Ben,
> "Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
> news:996476C5-8CC6-4444-8AC8-33FE3C601403@.microsoft.com...
>
> Thank you very much! I think this was exactly what I was searching for.
> Just one more question:
> Do you start the code with a ActiveX Control in a dts package as described
> in
> http://www.perfectxml.com/articles/x...rtxmlsql.asp#7 ?
> Regards, Martin
>
>
|||Hi Ben,
thank you, again this was exactly the information I was asking you for.
Regards, Martin
"Ben" <ben_1_ AT hotmail DOT com> schrieb im Newsbeitrag
news:C3B288ED-D170-4465-97B1-62EA7AAD0086@.microsoft.com...[vbcol=seagreen]
> Martin,
> I'm not sure I understand your question. The following are the steps
> to
> use to be able to use the code:
> 1. In enterprise manager, under the specific server you are using, click
> on
> data transfer services and then right click in the right window and create
> a
> new package
> 2. Right click -> add task -> activeX script task
> 3. Copy in the code and then modify for your xml files
> To set the global variables i use,
> close the tasks and make sure non are selected, click package ->
> properties
> and then click the tab global variables.
> i hope this answers your question. if not, could you please rephrase it?
> thanks.
> Ben
> "Martin Bbl" wrote:
