PDA

Click to See Complete Forum and Search --> : Assistance required with comparing ASP and XML data

kessa
03-07-2007, 08:39 AM
Hi All,

I wonder if someone may be able to help me with the following - I'm part way there, but am getting stuck at the last hurdle.

I'm trying to integrate an XML feed from an external site into my site, and want to be able to compare the XML feed against our DB for the following:


check the XML feed against our DB to see if a property already exist in our DB - if it doesn't then I want to create it as a new record.
check the XML feed against our DB to see if a property already exists in our DB - if it does then I want to update the existing record in our DB.
check our DB for any properties which are not in the XML feed (which may be the case if the supplier of the XML data has removed a property) - if a property exists in our DB, but no longer exists in the XML feed I want update the record (not delete)


I've managed to get parts 1 & 2 working, but I'm not sure how to address part 3.

Here's a copy of the code I'm using so far:


<% Set domDoc = server.CreateObject("MSXML.DOMDocument")
domDoc.async = False ' load all of the XML file first
aFilename = server.MapPath("../file.xml")


if NOT domDoc.Load(aFilename) then
response.write "Could not load file " & afilename & "<br />"
response.end
end if

Set aNode = domDoc.documentElement
%>

<html>
<head>
</head>
<meta name="robots" content="noindex, nofollow" />
<body>

<%

housecount = 0
Set XMLHouses = domDoc.SelectNodes("Houses/House")



for each XMLHouse in XMLHouses

abort=0 'set the abort value to everything initially being OK
updateprop=0 ' this will be used to determine if the property should be created or updated
createprop=0 ' this will be used to determine if the property should be created or updated

Set XMLPropCode = domDoc.SelectSingleNode("Houses/House[" & housecount & "]/Code")


' first, check to make sure there is a property number in the XML feed, then check the propery code to see if it already exists in the DV database.
' if the property already exists then UPDATE record, if it doesn't then CREATE record.
if XMLPropCode.Text="" then
abort=1
response.write ("*** Abort"& XMLPropCode.Text&" - Error Location: No Property Number ***<br />")
else
strsql= "SELECT DBpropertycode FROM properties WHERE DBpropertycode='" & XMLPropCode.Text &"'"
set rs_property_code = objconn.execute(strsql)
if not rs_property_code.eof then
updateprop=1
response.write("This property already exist - update the record instead<br />")
else
createprop=1
response.write("This property doesn't already exist - go ahead and create a new record<br />")
end if
response.write "The property code is: " & XMLPropCode.Text & "<br />"
end if


if abort=1 then response.write ("*** This property was aborted ***<br />")
housecount = housecount + 1

next


' close all of the items
XMLPropCode.close
set XMLPropCode = nothing

%>

</body>
</html>


Thanks
Kessa
kessa
03-08-2007, 10:22 AM
Hi All,

Just thought I'd check if anyone had any thoughts on the issue above? Any help would be really appreciated (as always :D )

Thanks
Kessa
thechasboi
03-08-2007, 11:11 AM
Kessa

What you might want to do is read the xml feed first and do the db stuff. Then serve up the page. Since you are using asp you can make an include file that reads the xml and then does the db stuff you want then calls the functions that output the html and other stuff for the page. Hope this helps
kessa
03-08-2007, 11:27 AM
Hi thechasboi,

Thanks for the reply. I'm not sure if I follow as, if I'm reading your post correctly, it doesn't seem to say how to address issue 3? Am I mis-reading the post?

Just to recap on what the problem is - How do I check if a result is in my DB but NOT in the XML feed?

Thanks
Kessa
thechasboi
03-08-2007, 12:53 PM
kessa

The include file that will read the xml should be made up of subs and functions. Functions if you want to return something. The include file should take care of all the db stuff by reading the xml feed then checking and what ever else you need to do to the xml and the db
1. check the XML feed against our DB to see if a property already exist in our DB - if it doesn't then I want to create it as a new record.
2. check the XML feed against our DB to see if a property already exists in our DB - if it does then I want to update the existing record in our DB.
3. check our DB for any properties which are not in the XML feed (which may be the case if the supplier of the XML data has removed a property) - if a property exists in our DB, but no longer exists in the XML feed I want update the record (not delete)
then server the page up by calling a function which will return an html string to response.write to the screen. You could even make it a little more flexible by having different function output different pages depengin on the xml you recieve. Just an idea. Hope this further helps.
kessa
03-08-2007, 06:01 PM
Hi,

OK, I think I follow what you mean now, but that seems to be a seperate issue (i.e - how I have currently constructed by code) rather than addressing the issue of "what code I need to use" in order to validate part 3.

(That said; I really appreciate the feedback - I'm still very much a .asp newbie and I'll take a look at how I can improve the code as you suggested.:) )

In the mean time, if anyone can help tell me what code I should be using to address issue 3 that would be fab.

Kessa
kessa
03-10-2007, 06:42 PM
Russell.... do you have any ideas about this one?

Huge thanks in advance :D
Kessa
thechasboi
03-10-2007, 11:07 PM
kessa

Issues 1,2 and 3 can be taken care by reading the xml before the page is sent to the user. You could first "check the XML feed against our DB to see if a property already exist in our DB - if it doesn't then I want to create it as a new record" by checking for a record set if it exists after a db select call. Issue 2 "check the XML feed against our DB to see if a property already exists in our DB - if it does then I want to update the existing record in our DB" in conjunction with the first issue you could set an else if exist then update. Always update so you do not have to worry about checking to see if the record is the same or not. It does not seem to me that you are keeping track of all the records but just updating records. Next Issue 3 "check our DB for any properties which are not in the XML feed (which may be the case if the supplier of the XML data has removed a property) - if a property exists in our DB, but no longer exists in the XML feed I want update the record (not delete)" It seems pretty straight forward if the record exists in the db but not in the xml you could do nothing. But if your intent is to keep track of what is included the you might want to store a separate list of what is in the xml feed in another table and insert into the records of the xml table a column that says whether this record is not in feed any more. Not knowing the entire scope of the project this is just some suggestion. You could also instead of changing the xml records table change the xml included list table to reflect the changes. So only serve up the list to the function that are on. Regarding that function after getting the data from the db you insert that into an array so you could use a for loop to go through the feed for each individual item. Again with out knowing further and programming this for you I can not suggest more with out confusion or getting too far off.