Learn how to a build a cloud-first strategyRegister Now


Insert XML into SQL Server with ASP

Posted on 2004-11-29
Medium Priority
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:


This will return data like this:

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

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.
Question by:AutomaticSlim
  • 3
  • 2
LVL 35

Assisted Solution

YZlat earned 200 total points
ID: 12699173
LVL 35

Expert Comment

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


Author Comment

ID: 12699770
Where do I load the XML Data from this link.  http://rss.topix.net/search/?q=Health&xml=1  
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

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

Set oXML = Nothing

Author Comment

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


Accepted Solution

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.


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

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

         sTemp = replace(sTemp, sChar, "")

      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
      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
     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(FileName) = contents
      Application(RSSURL) = Hour(now)
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
             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

 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
             'refresh in an hour
            Application(RSSURL) = Hour(now)
            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)
            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)
                  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 = _
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
                  sDesc = ""
                  end if
                         sTitle = oNode.selectSingleNode("title").Text
       response.write "<TR><TD valign=Top><A HREF ='" & sLink & "'>" & sTitle & _
      response.write "<TD valign=top width='60%'>" & sDesc & "</td>"

response.write "</table>"


response.write "The requested feed is not available"

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

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>"

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