Insert XML into SQL Server with ASP

Posted on 2004-11-29
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"></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
    LVL 35

    Assisted Solution

    LVL 35

    Expert Comment

    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

    Where do I load the XML Data from this link.  
    LVL 35

    Expert Comment

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

    myValue = oXML.Load("")

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

    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=

    LVL 9

    Accepted Solution

    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 "", "Slashdot"
    response.write "<p>"
    DisplayRSSFeed "", "Boing Boing Blog"
    response.write "<p>"

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
    This demonstration started out as a follow up to some recently posted questions on the subject of logging in: and…
    This video discusses moving either the default database or any database to a new volume.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now