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 & "<br>"
End If

If Node.nodeName = "link" Then
                Response.Write Node.xml & "<br>"
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 ?
Dutch_guyAsked:
Who is Participating?
 
mikoshaConnect With a Mentor Commented:
I've modified the code (the xml parsing part)  and this time it has to work:)  

Try to replace the old one with this :


Set XMLDestination = ServerXML.getElementsByTagName("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.childNodes
              
                              If Child_Node.nodeName = "description" Then
                                    description_value = replace(Child_Node.text,chr(39),chr(96))
                                    Response.Write description_value & "<br>"
                              End If

                              If Child_Node.nodeName = "link" Then
                                    link_value = replace(Child_Node.text,chr(39),chr(96))  
                                    Response.Write link_value & "<br>"
                              End If
                        Next

                        strSQL = "insert into xml_test(link,description) values(" & chr(39) &_
                                    link_value & chr(39) & "," & chr(39) & description_value & chr(39) & ")"

                        'response.Write      strSQL & "<br><br><br><br>"
                              objConnection.execute(strSQL)
            Next
0
 
mikoshaCommented:
Sql:
insert into your_table(link_field_name,description_field_name)
values(link_value,description_value)

Run this using ADODB.Connection object Execute method.
 
0
 
Dutch_guyAuthor Commented:
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 & "<br>", 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 ?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
rohanbairat3Commented:
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

0
 
rohanbairat3Commented:
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

0
 
mikoshaCommented:
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 & "<br>"
End If

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

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

         objConnection.execute(strSQL )
Next

0
 
Dutch_guyAuthor Commented:
"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: &#128; 1.350,00</description>
- <content:encoded>
- <![CDATA[ Aangeboden: Wellcraft 186 eclips, Prijs: &#128; 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: &#128; 10.750,00</description>
- <content:encoded>
- <![CDATA[ Aangeboden: Bayliner 1702 cuddy 1997, Prijs: &#128; 10.750,00
  ]]>
  </content:encoded>
  </item>

0
 
mikoshaCommented:
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 & "<br>"
                 End If

                 If Child_Node .nodeName = "link" Then
                       link_value = Child_Node .xml  
                      ' Response.Write Child_Node .xml & "<br>"
                 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...



0
 
Dutch_guyAuthor Commented:
Unfortunately not working. It only inserts empty rows. Been struggling with this all night, but can't seem to find the right solution.
0
 
Dutch_guyAuthor Commented:
This is a link to the XML I want to import:

http://www.marktplaats.nl/markt/watersport/speedboten.xml

I want to import every "link" and "description" within "item" to my database.

This XML page shows 10 "items".

So in my database there should be 10 records inserted.

Like this:

ID | Link | Description
1 http://www.marktplaats.nl/markt/watersport/speedboten/32046.htm  Description item 1
2 http://www.marktplaats.nl/markt/watersport/speedboten/32047.htm  Description item 2
3 http://www.marktplaats.nl/markt/watersport/speedboten/32048.htm  Description item 3
4 http://www.marktplaats.nl/markt/watersport/speedboten/32049.htm  Description item 4
5 http://www.marktplaats.nl/markt/watersport/speedboten/32050.htm  Description item 5
6 http://www.marktplaats.nl/markt/watersport/speedboten/32051.htm  Description item 6
7 http://www.marktplaats.nl/markt/watersport/speedboten/32052.htm  Description item 7
8 http://www.marktplaats.nl/markt/watersport/speedboten/32053.htm  Description item 8
9 http://www.marktplaats.nl/markt/watersport/speedboten/32054.htm  Description item 9
10 http://www.marktplaats.nl/markt/watersport/speedboten/32055.htm  Description item 10

Maybe this clarify things ?
0
 
mikoshaCommented:
Hi there,
Is it working now?
0
 
Dutch_guyAuthor Commented:
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.
0
All Courses

From novice to tech pro — start learning today.