asked on

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.

SOLUTION

YZlat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

YZlat

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)

AutomaticSlim

ASKER

Where do I load the XML Data from this link. http://rss.topix.net/search/?q=Health&xml=1

YZlat

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

AutomaticSlim

ASKER

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

ASKER CERTIFIED SOLUTION

apirnia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial