Newbie Question - Importing data via XML into a Database

[kessa]

Hi All,

(Note: I originally posted this in the XML forum (http://www.webdeveloper.com/forum/showthread.php?t=137517), but was advised to try here - if anyone can help, that would be really appreciated)


I'm new to XML and have a quick question which I hope someone may be able to help me with (I'm not after any code per se' - just a pointer in the right direction / the theory behind the process, would be fab :D )

What I need to do, it import some data FROM and external site, and insert it INTO my database (Access) using an XML feed and .asp

I've tried to get started with some tutorials off the web, and although I've found lots which explain about XML and how to use it to display data, (or how to display data coming FROM a database using XML), I've not managed to find any good articles on how to INSERT / IMPORT data INTO a database using an XML feed.

Ultimately I have 3 main questions:

1) How do I use XML / .asp to insert data into a database (I'll need to check if a record already exists, etc, but I guess I can do that in the normal way using .asp / VBscript)?

2) If there are miss-matches in data, how do I map these to one another?

For example - say I have a db field called "housecode" and the XML feed has an equivalent field which is called "code" - how do I get these to map to each other?

3) Finally, (and this may go hand in hand with question 2 above) how to I change / manipulate data types? For example: if "housecode" is a number field, and yet "code" is a text field, is there any way to manipulate "code" into the format I want?

Thanks
Kessa

[russell]

1) you will want to parse the XML in the ASP (using MSXMLDomDocument object) and form INSERT statements that you can execute. May be able to add entire XML Doc as an ISAM attachment to MSACCESS (in which case u can treat the document as a table), but I'm not certain that would work -- it does with text files though.

2) if you are forming INSERT statements, doesn't matter.

3) Cast the values -- houseCode = Clng(code)

A good starting point is http://msdn2.microsoft.com/en-us/library/ms950802.aspx

and here http://msdn2.microsoft.com/en-us/library/aa468547.aspx

[kessa]

Thanks Russell.

I've taken a quick look at both of those links and they look like exactly what I need to get me started. I've also just got hold of an asp / XML book through the post today so I hope to get stuck into this over the next few days.

No doubt, once I've got my head around the basics I'll be back in a day or so :D

[russell]

Awesome. Which book did you get? Is it Wrox ASP XML by chance? It is (probably by accident) a decent database tutorial as well. They have some nice samples of shopping carts and workflow applications.

[kessa]

Hi Russell,

No, it's this one by Sybex - http://eu.wiley.com/WileyCDA/WileyTitle/productCd-0782129714.html

... but I'll be looking out for the WROX one now too :D

I think that only drawback (with the Sybex one) might be that it ws written / published back in 2001 and therefore some of it may be out of date now, but as I'm still fairly new to .asp (and everything I know has been self taught) I thought it might make a good place to start :) .

Kessa

[kessa]

Hi Russell,

OK, next question for you :D

I've started putting together my first .asp to gather the XML info and wondered how I go about looping through any tags which repeat?

For example, say I have:

<houses>
<house>
<number></number>
<info></info>
<info></info>
<info></info>
</house>
</houses>

... how do I go about looping through each occurance of <info> and then through each occurance of <house> (assuming there is more than one)?

I've tried using "for each" / "next" but I can't seem to get the correct Syntax for the XML.

Here's a couple of things I've tried (in this example, we'll try looping through "info"):


for each info in domDoc.SelectSingleNode("houses/house/info")
for each domDoc.SelectSingleNode("houses/house/info") in domDoc.SelectSingleNode("houses/house/info")
for each domDoc.SelectSingleNode("houses/house/info") in domDoc.SelectSingleNode("houses/house")
for each info.name in domDoc.SelectSingleNode("houses/house/info")
for each info.value in domDoc.SelectSingleNode("houses/house/info")
for each info.text in domDoc.SelectSingleNode("houses/house/info")


(OK, I know some of those are really just grasping at straws, but I thought I'd better list everything I had tried so far :o )

Sorry - this'll probably be one of those really easy things to someone who know's what they are doing ;)

Cheers
Kessa

[russell]

hey Kessa, try GetElementsByTagName() (http://www.w3schools.com/dom/met_document_getelementsbytagname.asp)

here's a better sample. sorry, that one above is for JavaScript

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

[kessa]

thanks for the link Russell.

I gave it a shot using the same code but can't get it to iterate through each occurance :( - the best result I have managed to get so far, is to not get an error message (which I guess is a start:rolleyes: ), but I can't figure out why it's not showing any data.

Any other ideas?

[kessa]

Update:
OK, managed to get it to work (kind of!)

- next problem is, it's iterating through all of the <info> tags... not just the one for that current house.

So for example, I had hoped that if I set up the loop as follows:

for each Item in house
Response.Write Item.text & "<br>"
next

... and ran it through...

<houses>
<house>
<number>1</number>
<info>a</info>
<info>b</info>
<info>c</info>
<other>bit's and pieces</other>
</house>

<house>
<number>2</number>
<info>d</info>
<info>e</info>
<info>f</info>
<other>something else</other>
</house>
</houses>

...I would get "a,b,c"... then the info would carry on and display "bit's and pieces"... I could then loop through the whole thing again for the next house, etc

In fact, what I actually get is that the code loops through all occurances of <item> regardless of house and so I get "a,b,c,d,e,f" and then "bit's and pieces"

(Note: This is the only loop I've set up so far and so I was anticipating it would loop through the info for house 1 and then stop (at least until I set up the other loop :) )

Any idea where I'm going wrong?

Cheers
Kessa

[kessa]

Hi Guys,

OK, I managed to get the <item> loop to work OK by doing:


Set Items = domDoc.SelectNodes("Houses/House[Code="""&HouseCode.Text&"""]/Item")

for each Item in Items
' do something
next


..so I thought that I was all set to simply repeat the loop to go through each <house> in <houses>..... but I've now encountered another snag.:(

I can loop through all of the <house> in <houses> by using:


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

for each House in Houses
housecount = housecount + 1

' do the loop through the info
next


... but the problem is, after the final <house> I get an error message. It seems to be wanting to loop through the info again and I'm not sure how to tell it that it's finished.

When I've done this using DB info (rather than XML an feed) I've use things like recordset.eof - but that doesn't work for the XML feed.

Also, I've tried adding:

if houses = isNULL then END for

but that didn't work either.

Any ideas?

Thanks
Kessa

[russell]

what's the error message?

[kessa]

Hi Russell,

It's:


Microsoft VBScript runtime error '800a01a8'

Object required


Interestingly enough, I've just managed to find a way to stop this error message from appearing.

If I set housecount = -1, all of the <house> data displays and then seems to stop OK (i.e - there's no error message), but I've never had to use a -1 value before and so I'm a bit confused.

Do you think I'm fixing one bug with another, or is there some weird reason why I may need to use a negative value in XML?

K

[russell]

can't tell from the code u show...if u are accessing an element by using housecount, then yeah, that makes sense. else i have no idea without seeing more. of course if u got it working, probably u r ready to move on to the next problem :)

[kessa]

:D

and as if by magic - next problem :rolleyes:

One of the loops I have currently goes through looking at <item> codes, and then writes out a description of what that code is (for testing purposes)

The problem is, I only want to select 1 of the <item> types for inserting into my Database - in the feed, I've noticed that potentially it's possible to have more than one <item> which would case problems.

For example:
In the XML feed a <house> may have <item>100</item> and <item>230</item> (which would be "Holiday Home" and "Apartment".)

Whilst this makes perfect sense in terms of describing the property, I want to just insert into my Database the value for one of these (and be able to specify which to use) - so, in the above example, I would want to use<item230</item> (Apartment)

Here's an example of the code


Set Info = domDoc.SelectNodes("Houses/House[Code="""&HouseCode.Text&"""]/Info")

for each Info in Info


if Info.Text ="10" then response.write("Rewrite: <strong>City Apartment</strong><br />") end if
if Info.Text ="20" then response.write("Rewrite: <strong>Castle</strong><br />") end if
if Info.Text ="30" then response.write("Rewrite: <strong>Cottage</strong><br />") end if
if Info.Text ="40" then response.write("Rewrite: <strong>Mansion</strong><br />") end if
if Info.Text ="50" then response.write("Rewrite: <strong>Villa</strong><br />") end if
if Info.Text ="60" then response.write("Rewrite: <strong>Chalet</strong><br />") end if
if Info.Text ="70" then response.write("Rewrite: <strong>Farmhouse</strong><br />") end if
if Info.Text ="100" then response.write("Rewrite: <strong>Holiday Home</strong><br />") end if
if Info.Text ="210" then response.write("Rewrite: <strong>Holiday Home (Detached)</strong><br />") end if
if Info.Text ="220" then response.write("Rewrite: <strong>Holiday Home (Semi Detached)</strong><br />") end if
if Info.Text ="230" then response.write("Rewrite: <strong>Apartment</strong><br />") end if

next


Any ideas?
Cheers
Kessa

[russell]

try selectSingleNode or firstChild

[kessa]

not sure if that will do what I need to it as I need to go through each of the <items> first to see what options I have available for that property, and then go through them again and "cherry pick" the <item> that I want to use.

Here's a couple of examples which may help.


<houses>
<house>
<code>1</code>
<item>10</item> ' City Apartment
<item>100</item> ' Holiday Home
<item>230</item> ' Apartment
</house>

<house>
<code>2</code>
<item>50</item> ' Villa
</house>

<house>
<code>3</code>
<item>50</item> ' Villa
<item>100</item> ' Holiday Home
<item>210</item> ' Holiday Home (Detached)
</house>
</houses>


So in the examples above, I would want to cheery pick the following as being the results:

house 1: 230 (Apartment)
house 2: 50 (Villa)
House 3: 50 (Villa)


Also, once I reach "230" the types of data in <item> chnages to become some other type of data (say "holiday type" such as "Romatic Breaks", "Golfing Holidays").

As a result, I guess I need to set up one loop to go through all results up to (and including) "230" (and process accordingly), and then run another loop to continue processing the other info, etc.

Hope that makes more sense?

Cheers
Kieran

[russell]

every node has a children collection. can use that to access unknown as yet child nodes. i'm sorry, i'm not completely getting what the problem is...

i think this (http://msdn2.microsoft.com/en-us/library/ms764730.aspx) will help...