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 " " & strXMLJobTitle & "<br>"
Dim strXMLLocation
strXMLLocation =
objElements.childNodes(StrMyCounter).childNodes(1) .text
Response.Write " " & strXMLLocation & "<br>"
Dim strXMLSalaryforWeb
strXMLSalaryforWeb =
objElements.childNodes(StrMyCounter).childNodes(2) .text
Response.Write " " & strXMLSalaryforWeb & "<br>"
Dim strXMLCategory
strXMLCategory =
objElements.childNodes(StrMyCounter).childNodes(3) .text
Response.Write " " & strXMLCategory & "<br>"
Dim strXMLSalaryFrom
strXMLSalaryFrom =
objElements.childNodes(StrMyCounter).childNodes(4) .text
Response.Write " " & strXMLSalaryFrom & "<br>"
Dim strXMLSalaryTo
strXMLSalaryTo =
objElements.childNodes(StrMyCounter).childNodes(5) .text
Response.Write " " & strXMLSalaryTo & "<br>"
Dim strXMLPayRateContract
strXMLPayRateContract =
objElements.childNodes(StrMyCounter).childNodes(6) .text
Response.Write " " & strXMLPayRateContract & "<br>"
Dim strXMLJobRefNo
strXMLJobRefNo =
objElements.childNodes(StrMyCounter).childNodes(7) .text
Response.Write " " & strXMLJobRefNo & "<br>"
Dim strXMLJobDescription
strXMLJobDescription =
objElements.childNodes(StrMyCounter).childNodes(8) .text
Response.Write " " & strXMLJobDescription & "<br>"
Dim strXMLConsultant
strXMLConsultant =
objElements.childNodes(StrMyCounter).childNodes(9) .text
Response.Write " " & strXMLConsultant & "<br>"
Dim strXMLVacancyType
strXMLVacancyType =
objElements.childNodes(StrMyCounter).childNodes(10 ).text
Response.Write " " & strXMLVacancyType & "<br>"
Dim strXMLDate
strXMLDate = objElements.childNodes(StrMyCounter).childNodes(11 ).text
Response.Write " " & 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,SalaryFro m,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 OLEDB:Database 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)."
%>
Venmore wrote:
Many Thanks Jon
Jon Paal wrote:
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" <ve*****@gmail.comwrote in message news:11**********************@f16g2000cwb.googlegr oups.com...
All I need to do is read the XML and import into access via ASP
>
>
Venmore wrote:
>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>
>>
>Jon Paal wrote:
it's not quite that simple...
>
Without knowing the xml structure there's no way of knowing how to convert it to a database table(s) or their relationships...
>
>
here's a tool that may help.. search sourceforge for others
>
http://sourceforge.net/projects/myxdm/
>
>
>
<ve*****@gmail.comwrote in message news:11**********************@k70g2000cwa.googlegr oups.com...
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
>
>
