Insert XML into SQL Server with ASP

I want to insert some XML data into my database. Basically the user will type a search word in a text box field in an ASP page and then clicks on the button and it will pass the search word to a RSS string and it will load an XML page. You can try the following link. Below the word "Health" was passed:

http://rss.topix.net/search/?q=Health&xml=1


This will return data like this:

  <item>
  <title>Health News</title>
  <link>http://www.cnn.com/health</link>
  <description>Health News continually updated from </description>
  <source url="/health/health">Topix.net</source>
  <pubDate>Wed, 24 Nov 2004 23:23 GMT</pubDate>
  </item>

I have a Database table called ImportedXmlNews. The fields are same as the XML tags.
How do I go through this XML file and Insert the Info in to my DB table fields.
AutomaticSlimAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

YZlatCommented:
Dim xmlData, nodeItem, sSQL, sTitle, sLink, sDesc, sSource, sDate
Set xmlData = Server.CreateObject("Msxml2.DOMDocument.4.0")
Set nodPerson = xmlData.documentElement.selectSingleNode("person[@id='1']")

sTitle = nodPerson.selectSingleNode("title")
sLink = nodPerson.selectSingleNode("link").text
sDesc = nodPerson.selectSingleNode("desc").text
sSource = nodPerson.selectSingleNode("source").text
sDate = nodPerson.selectSingleNode("pubdate").text

''build a sql string
sSQL = "INSERT INTO Table1(title,link,description,source,pubDate) " &_
"VALUES('" & sTitle & "','" & sLink & "','" & sDesc & "','" & sSource & "','" & sDate & "')"

''execute sql statement
conn.Execute(sSQL)

0
AutomaticSlimAuthor Commented:
Where do I load the XML Data from this link.  http://rss.topix.net/search/?q=Health&xml=1  
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

YZlatCommented:
Dim oXml, myError, myValue, i
Set oXml = Server.CreateObject("MSXML2.DOMDocument")
oXML.async = False

myValue = oXML.Load("http://rss.topix.net/search/?q=Health&xml=1")

''check if document was loaded
If myValue = False Then
      Set myError = oXML.ParseError
      Response.Write "ERROR:" & myError.ErrorCode & " - " & myError.Reason & "  URL=" & myError.URL & chr(13) & chr(10)


      Set myError = Nothing
End If


For i = 0 to oXml.childNodes.length
      ''loop through the nodes
Next

Set oXML = Nothing
0
AutomaticSlimAuthor Commented:
I have used XML.Load with an actuall .XML file on my computer but I haven't used it with a RSS feed. The code that you provided gives me the following error:

ERROR:-2146697209 - No data is available for the requested resource. URL=http://rss.topix.net/search/?q=Health&xml=1

Thanks
0
apirniaCommented:
This should do, either use the code I have pasted or go to the URL listed.
To insert the Info in DB you have to do it at the part where we Response.write towards the end.



'********************************************************
'http://www.freevbcode.com/ShowCode.Asp?ID=5665
'********************************************************


bUseApp = false 'set to true if you
'want to cache responses in memory
'(Application variable) rather than
'file.

function ReplaceMultiple(InputString, CharsToReplace)
      iCount = len(CharsToReplace)
      sTemp = InputString
      for iCtr = 1 to iCount
         sChar = mid(CharsToReplace, iCtr, 1)

         sTemp = replace(sTemp, sChar, "")

      Next
      ReplaceMultiple = sTemp
End Function


function WriteToFile(FileName, Contents)
      iMode = 2 'overwrite
      set oFs = server.createobject("Scripting.FileSystemObject")
      set oTextFile = oFs.OpenTextFile(FileName, 2, True)
      oTextFile.Write Contents
      oTextFile.Close
      set oTextFile = nothing
      set oFS = nothing
end function


Private Function ReadTextFile(fName)
 set oFs = server.createobject("Scripting.FileSystemObject")
      If oFs.FileExists(fName) Then
     Set FSTR = ofs.OpenTextFile(fName)
     ReadTextFile = FSTR.ReadAll
     FSTR.Close
     Set FSTR = Nothing
      Set FSO = Nothing
end if
End Function


Private Function ReadAppVariable(fName)
      ReadAppVariable = Application(fName)
End Function


Private Function WriteAppVariable(FileName, Contents, RSSURL)
      Application.lock
      Application(FileName) = contents
      Application(RSSURL) = Hour(now)
      Application.unlock
end function




Function DisplayRSSFeed(RSSURL, FeedName)
'caches the feed, updates every hour, uses a file cache
'if it hasn't been update in an hour or running for the first time
'get it and save from file, else read it from file



  Set objXML = Server.CreateObject("MSXML2.DOMDocument")
  objXML.async = False
  'necessary because MSXML doesn't seem to work very well when an external DTD is referred to
  objXML.validateOnParse = false
  objXML.resolveExternals = false



iPos = InstrRev(RSSURL, "/")
if iPos = 0 then
             sFileName = RSSURL
else
             sFileName = mid(RSSURL, iPos + 1)
end if


         sFileName = FeedName &  "_" & sFileName & ".xml"
             'remove invalid/unwanted chars
             sFileName = ReplaceMultiple(sFileName,"[]/\\(^+)$,)?&:=")
             sFileName = Server.MapPath(".") & "\" & sFileName

if Application(RSSURL) <> Hour(Now) then

 set objXMLHTTP = Server.CreateObject("MSXML2.SERVERXMLHTTP")

 objXMLHTTP.Open "GET", RSSURL, false

 objXMLHTTP.SetRequestHeader "Content-type", "text/html"
on error resume next
 objXMLHTTP.Send

 sAns = objXMLHTTP.ResponseText
on error goto 0

set objXMLHTTP = nothing



'Ensure you have a valid XML response
 bAns = objXML.loadXML(sAns)

             if bAns = true then

         'save to file
             'Requires ASP user has write permissions to'
             'path you use
      if bUseApp = false then
             WriteToFile sFileName, sAns
            Application.Lock
             'refresh in an hour
            Application(RSSURL) = Hour(now)
             Application.unlock
      else
            WriteAppVariable sFileName, sAns, RSSURL
      end if
            
       
      else 'if invalid, try using a previous version
            'response.write "Loading from file " & sFileName & " due to failure"
            if bUseApp = false then
            sContents = ReadTextFile(sFileName)
            else
            sContents = ReadAppVariable(sFileName)
            end if

                  
            bAns = objXML.loadXML(sContents)
            'bAns = objXML.load(sFileName)
      end if
else 'try to load from cache on failure to refresh
             if bUseApp= false then
                  sContents = ReadTextFile(sFileName)
            else
                  sContents = ReadAppVariable(sFileName)
            end if


            bAns = objXML.loadXML(sContents)


End if

if bAns then

'RSS implementations vary. Some use item as a child of channel
'some don't that is what the below is about

'rss .9x and 2.0 implementation
set objItemNodes = objXML.DocumentElement.SelectNodes("item")

if objItemNodes.length = 0 then
'rss 1.x implemenation
  set objItemNodes = _
     objXML.DocumentElement.SelectSingleNode("channel").SelectNodes("item")
end if


'display as table

response.write "<p align=center><b>" & FeedName & " recent headlines</b></p>"
response.write "<table cellspacing=5>"
response.write "<TR><TD><B>Item</B></TD>"
response.write "<TD><B>Summary</B></TD>"
response.write "</TR>"
response.write "<TR><TD>&nbsp;</TD></TR>"
for each oNode in objItemNodes

      sLink = oNode.selectSingleNode("link").Text
set oDescriptionNode = oNode.selectSingleNode("description")
                  if not oDescriptionNode is nothing then
                  sDesc= oDescriptionNode.Text
                  else
                  sDesc = ""
                  end if
                         sTitle = oNode.selectSingleNode("title").Text
       response.write "<TR><TD valign=Top><A HREF ='" & sLink & "'>" & sTitle & _
              "</a></td>"
      response.write "<TD valign=top width='60%'>" & sDesc & "</td>"

next
response.write "</table>"

else

response.write "The requested feed is not available"

end if
set oNode = Nothing
set objItemNodes = Nothing
set objXML = Nothing
End function


'DEMO
DisplayRSSFeed "http://rss.topix.net/search/?q=Health&xml=1", "Slashdot"
response.write "<p>"
DisplayRSSFeed "http://boingboing.net/rss.xml", "Boing Boing Blog"
response.write "<p>"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.