selecting certain xml elements

Expand Post »

Hello All,

I have a question regarding searching and selecting certain elements in an XML document using asp. The xml script basically consist of news headings, contents, date, and category elements that tell what the news is related to and where it should be displayed and stored. For example Finance catagory stories will be stored in a different database to the business one.

RSS, Web Services and SOAP Syntax (Toggle Plain Text)
<Article Created="16:01:59" ID="15105602">
 
  	<Heading>Equitable drops claim against former directors</Heading> 
 
  	<Date>02/12/2005</Date> 
 
 	<Contents>
 		<news story goes in here> 
  	</Contents>
 
 	<Categories>
  		<Category ID="430009725">Finance</Category> 
 		<Category ID="430009734">Economy</Category> 
 		<Category ID="430009735">Business</Category> 
  		<Category ID="438000159">Insurance</Category> 
 	</Categories>
 
</Article>
<Article Created="16:01:59" ID="15105602">

  	<Heading>Equitable drops claim against former directors</Heading> 

  	<Date>02/12/2005</Date> 

 	<Contents>
 		<news story goes in here> 
  	</Contents>

 	<Categories>
  		<Category ID="430009725">Finance</Category> 
 		<Category ID="430009734">Economy</Category> 
 		<Category ID="430009735">Business</Category> 
  		<Category ID="438000159">Insurance</Category> 
 	</Categories>

</Article>

So basically I need a script that can check the XML for certain stories and store them in a database. I already have the script (see below) that takes ALL the news stories from the XML file and puts them in a database. So how can I get the script to check if a story is, say a Finance story and continue to store it in a database? Would I use an if else statment? if so, where should I put it?

RSS, Web Services and SOAP Syntax (Toggle Plain Text)
 
Dim XMLDom
Dim ItemID
Dim DbConn
Dim SQLString
Dim ANArticleNode
Dim CollectionOfArticleNodes
Dim cst
 
Set XMLDom = CreateObject("MSXML2.DomDocument.4.0")
XMLDom.async = False
XMLDom.setProperty "ServerHTTPRequest", True
 
Set DbConn = Createobject("adodb.connection")
'DbConn.open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=pokernewsxml; OPTION=3"
DbConn.open "Driver={MySQL ODBC 3.51 Driver};" & _ 
        "Server=82.195.128.88;" & _ 
        "Database=johnfog_xml;" & _ 
        "Uid=johnfog_xml;" & _ 
        "Pwd=wordword;"
 
 
'-- Load the XML data from your live URL
XMLDom.Load("http://feeds.directnews.org.uk/?ad96035d-f9fe-4a3f-a5b2-ad546b2ed850") 
 
'-- Create a reference to a collection of all Article Tags within the downloaded XML Document
Set CollectionOfArticleNodes = XMLDom.SelectNodes("InfoStreamResults/Article")
 
'-- Iterate the collection of Article Tags 
For Each ANArticleNode in CollectionOfArticleNodes 
	ItemID = ANArticleNode.SelectSingleNode("@ID").text
	Heading = ANArticleNode.SelectSingleNode("Heading").text	
	Contents = ANArticleNode.SelectSingleNode("Contents").text
	sDate = ANArticleNode.SelectSingleNode("Date").text
 
	'-- Delete the item from the local database if it exists
 
	SQLString = "DELETE FROM DeHavillandNews WHERE trim(ItemID)='" & trim(ItemID) & "';"
    DbConn.Execute(SQLString)
 
	'-- Insert the item into the local database
	SQLString = "INSERT INTO DeHavillandNews (ItemID,Heading,Contents,strDate) " _
		  & "VALUES('" & ItemID & "','" & EncodeIt(Heading) & "','" & EncodeIt(Contents) & "', '" & sDate & "');" 
	DbConn.Execute(SQLString)	
 
Next
 
 
 
'-- Handles quotations in text
Function EncodeIt(TextString)
	TextString = Replace(CStr(TextString), "''", "'")
	TextString = Replace(TextString, "'", "''")
	EncodeIt = TextString
End Function
Dim XMLDom
Dim ItemID
Dim DbConn
Dim SQLString
Dim ANArticleNode
Dim CollectionOfArticleNodes
Dim cst

Set XMLDom = CreateObject("MSXML2.DomDocument.4.0")
XMLDom.async = False
XMLDom.setProperty "ServerHTTPRequest", True

Set DbConn = Createobject("adodb.connection")
'DbConn.open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=pokernewsxml; OPTION=3"
DbConn.open "Driver={MySQL ODBC 3.51 Driver};" & _ 
        "Server=82.195.128.88;" & _ 
        "Database=johnfog_xml;" & _ 
        "Uid=johnfog_xml;" & _ 
        "Pwd=wordword;"


'-- Load the XML data from your live URL
XMLDom.Load("http://feeds.directnews.org.uk/?ad96035d-f9fe-4a3f-a5b2-ad546b2ed850") 

'-- Create a reference to a collection of all Article Tags within the downloaded XML Document
Set CollectionOfArticleNodes = XMLDom.SelectNodes("InfoStreamResults/Article")

'-- Iterate the collection of Article Tags 
For Each ANArticleNode in CollectionOfArticleNodes 
	ItemID = ANArticleNode.SelectSingleNode("@ID").text
	Heading = ANArticleNode.SelectSingleNode("Heading").text	
	Contents = ANArticleNode.SelectSingleNode("Contents").text
	sDate = ANArticleNode.SelectSingleNode("Date").text

	'-- Delete the item from the local database if it exists

	SQLString = "DELETE FROM DeHavillandNews WHERE trim(ItemID)='" & trim(ItemID) & "';"
    DbConn.Execute(SQLString)
	
	'-- Insert the item into the local database
	SQLString = "INSERT INTO DeHavillandNews (ItemID,Heading,Contents,strDate) " _
		  & "VALUES('" & ItemID & "','" & EncodeIt(Heading) & "','" & EncodeIt(Contents) & "', '" & sDate & "');" 
	DbConn.Execute(SQLString)	
	
Next



'-- Handles quotations in text
Function EncodeIt(TextString)
	TextString = Replace(CStr(TextString), "''", "'")
	TextString = Replace(TextString, "'", "''")
	EncodeIt = TextString
End Function

Any help would be greatly appreciated as I am seriously stuck with this one.

Thanks folks

J

Similar Threads

Parse a XML element with AJAX in JavaScript / DHTML / AJAX
500 ways to print [1..10]! in IT Professionals' Lounge
Inserting Elements into existing XML in C#
XML Serialization in C#
Access unqualified XML element in C#

fogofogo

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Dec 2005

Permalink

Dec 5th, 2005

Re: selecting certain xml elements

problem solved

If anyone is interested, here is the solution:

RSS, Web Services and SOAP Syntax (Toggle Plain Text)

Dim XMLDom
Dim ItemID
Dim DbConn
Dim SQLString
Dim ANArticleNode
Dim ANArticleNode2
Dim CollectionOfArticleNodes
Dim CollectionOfArticleNodes2
Dim cst
 
Set XMLDom = CreateObject("MSXML2.DomDocument.4.0")
XMLDom.async = False
XMLDom.setProperty "ServerHTTPRequest", True
 
Set DbConn = Createobject("adodb.connection")
DbConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=test1.mdb"
 
'-- Load the XML data from your live URL
XMLDom.Load("http://feeds.directnews.org.uk/?ad96035d-f9fe-4a3f-a5b2-ad546b2ed850") 
 
'-- Create a reference to a collection of all Article Tags within the downloaded XML Document
Set CollectionOfArticleNodes = XMLDom.SelectNodes("InfoStreamResults/Article")
 
'-- Iterate the collection of Article Tags 
For Each ANArticleNode in CollectionOfArticleNodes 
	'-- Now create a reference to the category tag
	Set CollectionOfArticleNodes2 = ANArticleNode.SelectNodes("Categories/Category")
	'-- And iterate through the nodes to test for a match
	For Each ANArticleNode2 in CollectionOfArticleNodes2
		ItemID = ANArticleNode2.SelectSingleNode("@ID").text
		if ItemID = "430009735" then
			'-- Retrieve the value of the heading node from the current article
			Heading = ANArticleNode.SelectSingleNode("Heading").text
 
			'-- Insert the item into the local database
			SQLString = "INSERT INTO test (Heading) " _
			& "VALUES('" & EncodeIt(Heading) & "');" 
			DbConn.Execute(SQLString)	
		end if
	Next '-- check the next category ID
Next '-- move to the next article
 
'-- Handles quotations in text
Function EncodeIt(TextString)
	TextString = Replace(CStr(TextString), "''", "'")
	TextString = Replace(TextString, "'", "''")
	EncodeIt = TextString
End Function
%>

fogofogo

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

8 posts
since Dec 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in RSS, Web Services and SOAP Forum Timeline: Before there was AJAX - there was TAXI ?

Next Thread in RSS, Web Services and SOAP Forum Timeline: Retrieve the data from the url api

DaniWeb Message

Cancel Changes

User Name
Password