Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Insert XML into SQL Server with ASP

Posted on 2004-11-29
6
Medium Priority
?
429 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:AutomaticSlim
  • 3
  • 2
6 Comments
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 200 total points
ID: 12699173
0
 
LVL 35

Expert Comment

by:YZlat
ID: 12699445
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
 

Author Comment

by:AutomaticSlim
ID: 12699770
Where do I load the XML Data from this link.  http://rss.topix.net/search/?q=Health&xml=1  
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:YZlat
ID: 12709548
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
 

Author Comment

by:AutomaticSlim
ID: 12711992
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
 
LVL 9

Accepted Solution

by:
apirnia earned 800 total points
ID: 12720806
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question