Importing XML into Access 2000

[venmore]

Hi
Can someone please point in the right direction.
I have an XML file that gets updated every 4 hours on a web server.
I can check the XML modification time in ASP and compare to the
databse.
If it is newer, I need some ASP code to then import the XML into
Access.
Been trawling the net for an answer for a while - no joy

thanks

ven

 

[Venmore]

Sample data:

<?xml version="1.0" encoding="ISO-8859-1"?>
<vacancies>
<job>
<JobTitle>Fresh Fish Chef</JobTitle>
<Location>Thame</Location>
<SalaryforWeb>20000</SalaryforWeb>
<Category>Catering</Category>
<SalarytoPerm>15000.00</SalarytoPerm>
<SalaryfromPerm>25000.00</SalaryfromPerm>
<PayRateContract>0</PayRateContract>
<JobDescription>Must like fish and not fussy about
scales.</JobDescription>
<Consultant>George Best</Consultant>
<VacancyType>Unfilled Perm Job</VacancyType>
<Date>130906</Date>
<JobRefNo>1254</JobRefNo>
</job>
<job>
<JobTitle>Network Engineer</JobTitle>
<Location>Aylesbury</Location>
<SalaryforWeb>40000</SalaryforWeb>
<Category>IT</Category>
<SalarytoPerm>20000.00</SalarytoPerm>
<SalaryfromPerm>22000.00</SalaryfromPerm>
<PayRateContract>20</PayRateContract>
<JobDescription>Must know CAT5e networks as we
don't!</JobDescription>
<Consultant>Superman</Consultant>
<VacancyType>Unfilled Perm Job</VacancyType>
<JobRefNo>1286</JobRefNo>
</job>
</vacancies>

 

[Venmore]

All I need to do is read the XML and import into access via ASP

 

[Venmore]

Many Thanks Jon

example of reading an xml file is here:

http://www.15seconds.com/issue/990527.htm


loop through the info

--creating an sql insert statement for each new record

--execute the insert statement

loop

 

[Venmore]

My working code if anyone needs it:

<%@LANGUAGE="VBSCRIPT"%>
<% OPTION EXPLICIT %>
<%
'######## FOR DEBUGGING, DO FIND AND REPLACE ON RESPONSE.WRITE TO
REMOVE LEADING '


'ON ERROR RESUME NEXT

'-----------------------------------------------
'start timer before task begins:
'-----------------------------------------------
Dim starttime, z
starttime = Timer()
Do While z < 350000
z = z + 1
Loop
'-----------------------------------------------
'CREATE A CONNECTION OBJECT
'-----------------------------------------------
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
'-----------------------------------------------
'GENERATE A DSNLESS CONNECTION STRING
'-----------------------------------------------
Dim connString
connString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & server.mappath("../Private/Vacancies/jobs.mdb") &
";"
'-----------------------------------------------
'OPEN THE CONNECTION
'-----------------------------------------------
objConn.Open connString




Response.Write "<font face=""verdana"" size=""1"">"
Response.Write "<h2>DTRC XML TESTING</h2>"

' ############## STEP 1. GET MODIFICATION DATE OF XML FILE
##############
dim fs,f
set fs=Server.CreateObject("Scripting.FileSystemObject")
'set f=fs.GetFile(server.mappath("../Private/Vacancies/vacancies.xml"))
'LIVE FILE
set f=fs.GetFile(server.mappath("../Private/Vacancies/yvacancies.xml"))
'TEST FILE
Response.Write "<p><font color=#0033FF>Debug: Modification Dates of
:<br>"
Response.Write("The XML file = ")
Response.Write("<strong>")
Response.Write(f.DateLastModified) & "</strong><br>"

%>

<% '################ STEP 2. GET THE DATE FROM THE DATABASE
#############

'-----------------------------------------------
'GENERATE A QUERY
'-----------------------------------------------
Dim SQLString
SQLString = "SELECT Date FROM tblDate"
'-----------------------------------------------
'GENERATE A RECORDSET
'-----------------------------------------------
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS = objConn.Execute (SQLString)

Response.Write "<font color=#0033FF>Debug: DatabaseDate = <strong>" &
objRS("Date") & "</strong>"

%>
<P>
<a href="XML-Date2dB.asp">update date in dB to current</a><br>
<a href="XML-deletedB.asp">clear contents of dB</a><br>
<a href="XML-dBContents.asp">view contents of dB</a><br>
<P>
<%

'################ STEP 3. COMPARE DB AND XML TIMES #############

Dim MyDate
Mydate=f.DateLastModified
Response.Write "<font color=red>"

IF DateDiff("s", objRS("Date"), Mydate) > 0 THEN
Response.Write "<p><font color=#0033FF>Debug: "
Response.Write "Debug: going to write date into database<br>time in
seconds: "
Response.Write DateDiff("s", objRS("Date"), Mydate)

'################ STEP 4. IF XML NEWER - DELETE CONTENTS OF DB
#############


'-----------------------------------------------
'Create a Connection Object
'-----------------------------------------------
Dim objConn2
Set objConn2 = Server.CreateObject("ADODB.Connection")
'-----------------------------------------------
'Open the connection
'-----------------------------------------------
objConn2.Open connString
'-----------------------------------------------
'Generate a query
'-----------------------------------------------
Dim SQLString2
SQLString2 = "DELETE * FROM tblJobs"
'-----------------------------------------------
'GENERATE A RECORDSET
'-----------------------------------------------
Dim objRS2
Set objRS2 = Server.CreateObject("ADODB.Recordset")
Set objRS2 = objConn.Execute (SQLString)

Response.Write "<p><font color=#0033FF>Debug: Database has been
emptied</font>"

'-----------------------------------------------
'NOW CLOSE THE CONNECTION OBJECT
'-----------------------------------------------
objConn2.close
Set objConn2 = Nothing



'################ STEP 5. READ XML AS NODES VIA ASP!!!!!!
#############
Response.Write "<font color=""green"">"
Dim MyXML
Set MyXML = Server.CreateObject("Microsoft.XMLDOM")
MyXML.async = False
MyXML.Load (Server.MapPath("../Private/Vacancies/yvacancies.xml"))
'TEST
'MyXML.Load (Server.MapPath("../Private/Vacancies/vacancies.xml"))
'LIVE

'-----------------------------------------------
'COUNT NUMBER OF JOBS IN DB
'-----------------------------------------------
Dim objCount
Set objCount = MyXML.getElementsByTagName("Job")
Response.Write "<P><i>Number of jobs = <b>" & objCount.length &
"</b></i><p>"

'-----------------------------------------------
' START THE COUNTER
'-----------------------------------------------
Dim StrMyCounter
StrMyCounter = 0

'-----------------------------------------------
' BECAUSE MICROSOFT STARTED LOOPS IN XML AT 0 AND NOT 1 AS W3C
RECOMENDED
' WE SUBTRACT -1 FROM TOTAL
'-----------------------------------------------
Dim ii
For ii = 0 to objCount.length - 1

Response.Write "<font color=#0033FF>Debug: ======= XML NODE NUMBER " &
StrMyCounter & " ========= <br>"
Response.Write "<font color=#006600>"

Dim objElements
set objElements = MyXML.SelectSingleNode("Jobs")

Dim strXMLJobTitle
strXMLJobTitle =
objElements.childNodes(StrMyCounter).childNodes(0).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLJobTitle & "<br>"

Dim strXMLLocation
strXMLLocation =
objElements.childNodes(StrMyCounter).childNodes(1).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLLocation & "<br>"

Dim strXMLSalaryforWeb
strXMLSalaryforWeb =
objElements.childNodes(StrMyCounter).childNodes(2).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLSalaryforWeb & "<br>"

Dim strXMLCategory
strXMLCategory =
objElements.childNodes(StrMyCounter).childNodes(3).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLCategory & "<br>"

Dim strXMLSalaryFrom
strXMLSalaryFrom =
objElements.childNodes(StrMyCounter).childNodes(4).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLSalaryFrom & "<br>"

Dim strXMLSalaryTo
strXMLSalaryTo =
objElements.childNodes(StrMyCounter).childNodes(5).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLSalaryTo & "<br>"

Dim strXMLPayRateContract
strXMLPayRateContract =
objElements.childNodes(StrMyCounter).childNodes(6).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLPayRateContract & "<br>"

Dim strXMLJobRefNo
strXMLJobRefNo =
objElements.childNodes(StrMyCounter).childNodes(7).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLJobRefNo & "<br>"

Dim strXMLJobDescription
strXMLJobDescription =
objElements.childNodes(StrMyCounter).childNodes(8).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLJobDescription & "<br>"

Dim strXMLConsultant
strXMLConsultant =
objElements.childNodes(StrMyCounter).childNodes(9).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLConsultant & "<br>"

Dim strXMLVacancyType
strXMLVacancyType =
objElements.childNodes(StrMyCounter).childNodes(10).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLVacancyType & "<br>"

Dim strXMLDate
strXMLDate = objElements.childNodes(StrMyCounter).childNodes(11).text
Response.Write "&nbsp;&nbsp;&nbsp;" & strXMLDate & "<br>"

StrMyCounter = StrMyCounter + 1

' ################### STEP 6. INSERT XML CONTENTS INTO dB
######################
'-----------------------------------------------
' CREATE A CONNECTION OBJECT
'-----------------------------------------------
Dim objConn3
Set objConn3 = Server.CreateObject("ADODB.Connection")

'-----------------------------------------------
' OPEN THE CONNECTION
'-----------------------------------------------
objConn3.Open connString
'-----------------------------------------------
' GENERATE A QUERY
'-----------------------------------------------
Dim SQLString3
SQLString3 = "INSERT INTO tblJobs
(JobTitle,Location,SalaryforWeb,Category,SalaryFrom,SalaryTo,PayRateContract,JobRefNo,JobDescription,Consultant,VacancyType,PostDate)
VALUES ('" & strXMLJobTitle & "','" & strXMLLocation & "','" &
strXMLSalaryforWeb & "','" & strXMLCategory & "','" & strXMLSalaryFrom
& "','" & strXMLSalaryTo & "','" & strXMLPayRateContract & "','" &
strXMLJobRefNo & "','" & strXMLJobDescription & "','" &
strXMLConsultant & "','" & strXMLVacancyType & "','" & strXMLDate &
"')"

Response.Write "<font color=#0033FF>Debug: " & SQLString3
'-----------------------------------------------
' GENERATE A RECORDSET
'-----------------------------------------------
Dim objRS3
Set objRS3 = Server.CreateObject("ADODB.Recordset")
Set objRS3 = objConn3.Execute (SQLString3)

Response.Write "<br>Debug: ======== ADDED RECORD TO DB ========<br>"

'-----------------------------------------------
'NOW CLOSE THE CONNECTION OBJECT
'-----------------------------------------------
objConn3.close
Set objConn3 = Nothing


' ####### LOOP TO NEXT RECORD IN XML
NEXT
Response.Write "<p><font color=#0033FF>Debug: DB Emptied and new XML
imported"




'################ STEP 7. COMPACT AND REPAIR DATABASE #############

Dim fso, Engine, strDbPath
Dim strDbPath2Compact, strDbPathTemp1, strDbPathTemp2
Set fso = CreateObject("Scripting.FileSystemObject")
Set Engine = CreateObject("JRO.JetEngine")
strDbPath2Compact = server.mappath("../Private/Vacancies/jobs.mdb")
strDbPathTemp1 =
left(strDbPath2Compact,instrrev(strDbPath2Compact,"\")) & "temp1.mdb"
strDbPathTemp2 =
left(strDbPath2Compact,instrrev(strDbPath2Compact,"\")) & "temp2.mdb"
'-----------------------------------------------
'COPY FILE TO TEMP LOCATION
'-----------------------------------------------
fso.CopyFile strDbPath2Compact,strDbPathTemp1
'-----------------------------------------------
'COMPACT THE DATABASE
'-----------------------------------------------
Dim strDatabasePassword
strDatabasePassword=""
Engine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& strDbPathTemp1 & ";Jet OLEDBatabase Password=" &
strDatabasePassword, _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
strDbPathTemp2
'-----------------------------------------------
'COPY ACROSS THE NEW COMPACTED DATABASE
'-----------------------------------------------
fso.CopyFile strDbPathTemp2,strDbPath2Compact
'-----------------------------------------------
' DELETE THE TEMP FILES
'-----------------------------------------------
fso.DeleteFile strDbPathTemp1
fso.DeleteFile strDbPathTemp2
Set fso = nothing
Set Engine = nothing

Response.Write "<p><font color=#0033FF>Debug: Database repaired and
compacted"



' ############## LOOP FROM STEP 3 ############################
ELSE
Response.Write "<p><font color=#0033FF>Debug: Not going to write date
into database<br>time in seconds: "
Response.Write DateDiff("s", objRS("Date"), Mydate)
END IF


set f=nothing
set fs=nothing
objRS.close


'-----------------------------------------------
' GET TIMER AFTER TASK COMPLETES:
'-----------------------------------------------
Dim endtime
endtime = Timer()
'-----------------------------------------------
' DISPLAY RESULTS:
'-----------------------------------------------
Response.Write "<p><font color=#0033FF>Debug: The task completed in
" & endtime-starttime & " s"
Response.Write " (" & (endtime-starttime)*1000 & " milliseconds)."

%>