asked on

Import XML to database

I have the following code to show two nodes out of a XML file:

Set ServerXML = Server.CreateObject("MSXML2.DOMDocument")
ServerXML.resolveExternals = False
ServerXML.validateOnParse = False
ServerXML.async = False
ServerXML.setProperty "ServerHTTPRequest", True

If ServerXML.Load("http://www.test.com/file.xml") then
Set XMLDestination = ServerXML.selectNodes("/rss/channel/item/*")

For Each Node In XMLDestination

If Node.nodeName = "description" Then
Response.Write Node.xml & " "
End If

If Node.nodeName = "link" Then
Response.Write Node.xml & " "
End If

Now I want to save the information from "description" and from "link" to two seperate fields in the database in the same record.

How can I do this in one insert statement ?

mikosha

Sql:
insert into your_table(link_field_name,description_field_name)
values(link_value,description_value)

Run this using ADODB.Connection object Execute method.

Dutch_guy

ASKER

I know how to make an insert query, but where do I place it in my code ?

If I wanted to import only one node of the XML there wouldn't be a problem.

Instead of: Response.Write Node.xml & " ", I would place my insert query there, but I also want the other node to import for the same record.

I can take the ID from the first insert and use it in my second insert guery, but that's not so efficient. I want to insert both nodes in one insert query. Maybe I need to change the way I fetch the information from the XML ?

rohanbairat3

What database ???

Also are these nodes going to be alternate i.e desc and then link or they are random ... if they are random u can do this ...
'set save as 0 first
save=0
strdesc =""

For Each Node In XMLDestination

If Node.nodeName = "description" Then
'check if u need to save ..ie one desc node is followed by another
if save = 1 then
insert into your_table( link_field_name,description_field_name)values("",strdesc)
end if
strdesc = Node.xml
save = 1
End If

If Node.nodeName = "link" Then
'check if u need to save ..ie one desc node is followed by link
if save = 1 then
insert into your_table( link_field_name,description_field_name)values(Node.xml ,strdesc)
strdesc =""
else
insert into your_table( link_field_name,description_field_name)values(Node.xml ,"")
strlink=""
end if

End If

rohanbairat3

also add save = 0 in

If Node.nodeName = "link" Then
'check if u need to save ..ie one desc node is followed by link
if save = 1 then
insert into your_table( link_field_name,description_field_name)values(Node.xml ,strdesc)
strdesc =""
else
insert into your_table( link_field_name,description_field_name)values(Node.xml ,"")
strlink=""
end if

save = 0

End If

How is ur xml organized can u paste it ?

-rohan

mikosha

may be i understand not right ,but you want to take pair (link,description) and insert them into some table using one sql statement. is it right?

So , if yes , then try this:

Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Provider = ....
objConnection.ConnectionString = ...
objConnection.open

Set ServerXML = Server.CreateObject("MSXML2.DOMDocument")
ServerXML.resolveExternals = False
ServerXML.validateOnParse = False
ServerXML.async = False
ServerXML.setProperty "ServerHTTPRequest", True

If ServerXML.Load("http://www.test.com/file.xml") then
Set XMLDestination = ServerXML.selectNodes("/rss/channel/item/*")

For Each Node In XMLDestination

description_value = "whatever..."
link_value = "whatever..."

If Node.nodeName = "description" Then
description_value = Node.xml
Response.Write Node.xml & " "
End If

If Node.nodeName = "link" Then
link_value = Node.xml
Response.Write Node.xml & " "
End If

strSQL = "insert into your_table(link_field_name,description_field_name) values(" &_
cstr(link_value) & "." & cstr(description_value) & ")"

objConnection.execute(strSQL )
Next

Dutch_guy

ASKER

"may be i understand not right ,but you want to take pair (link,description) and insert them into some table using one sql statement. is it right?"

That is exactly right.

This is part of the XML that I have to work with:

- <item>
<title>Wellcraft 186 eclips</title>
<link>http://www.test.nl/test123.htm</link>
<guid isPermaLink="true">http://www.test.nl/test123.htm</guid>
<description>Aangeboden: Wellcraft 186 eclips, Prijs: 1.350,00</description>
- <content:encoded>
- <![CDATA[ Aangeboden: Wellcraft 186 eclips, Prijs: 1.350,00
]]>
</content:encoded>
</item>
- <item>
<title>Bayliner 1702 cuddy 1997</title>
<link>http://www.test.nl/markt/watersport/speedboten/32021.htm</link>
<guid isPermaLink="true">http://www.test.nl/markt/watersport/speedboten/32021.htm</guid>
<description>Aangeboden: Bayliner 1702 cuddy 1997, Prijs: 10.750,00</description>
- <content:encoded>
- <![CDATA[ Aangeboden: Bayliner 1702 cuddy 1997, Prijs: 10.750,00
]]>
</content:encoded>
</item>

mikosha

ok ,now i can understand what you're doing:)
so, by my opinion it should be this way:

' start definitions blah-blah..

Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Provider = ....
objConnection.ConnectionString = ...
objConnection.open

Set ServerXML = Server.CreateObject("MSXML2.DOMDocument")
ServerXML.resolveExternals = False
ServerXML.validateOnParse = False
ServerXML.async = False
ServerXML.setProperty "ServerHTTPRequest", True

If ServerXML.Load("http://www.test.com/file.xml") then

If (ServerXML.parseError.errorCode <> 0) Then

'error handling for parse error

Else
' In XMLDestination you're gettinng collection of <item> elements using .selectNodes (as is now)
'or .getElementsByTagName() .

Set XMLDestination = ServerXML.selectNodes("/rss/channel/item/*")

'or Set XMLDestination = ServerXML.getElementsByTagName("item")

' Here will be two nested loops : outer loop for each member of XMLDestination collection and
'inner loop for each member of XMLDestination collection .childNodes collection

For Each Node In XMLDestination

description_value = "whatever..."
link_value = "whatever..."
For Each Child_Node In Node.chilNodes

If Child_Node .nodeName = "description" Then
description_value = Child_Node .xml
'Response.Write Child_Node .xml & " "
End If

If Child_Node .nodeName = "link" Then
link_value = Child_Node .xml
' Response.Write Child_Node .xml & " "
End If
Next

strSQL = "insert into your_table(link_field_name,description_field_name) values(" &_
cstr(link_value) & "." & cstr(description_value) & ")"

objConnection.execute(strSQL )
Next
End If

Hope this will help...

Dutch_guy

ASKER

Unfortunately not working. It only inserts empty rows. Been struggling with this all night, but can't seem to find the right solution.

Dutch_guy

ASKER

ASKER CERTIFIED SOLUTION

mikosha

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mikosha

Hi there,
Is it working now?

Dutch_guy

ASKER

Yes! It works!

Thank you very much, this has been very helpfull and learnfull.

One little question. If I output the XML data to the screen, the Euro sign isn't displayed correctely. It is shown as a "?".

When I take a direct look at the XML file, the Euro sign also isn't displayed.

Any idea how to replace the "?" with the Euro sign ?

I tried to replace the "?", with the replace function, like normal, but it doesn't work. Also replacing the ASCII code of the "?" doens't do anything.