0
**http://www.dsebd.org/latest_PE_all2_08.php**

above url contain a html table.I want to save this table value on XML and also want to save this table value on database MS2008.

How to save html table values on database

rick schott
  • 21,012
  • 5
  • 52
  • 81
shamim
  • 6,640
  • 20
  • 85
  • 151

4 Answers4

1

You could use HTML Agility pack like so:

    WebClient webClient = new WebClient();
    const string strUrl = "http://www.myspace.com/centuryman";

    // Setup proxy for internal stuff
    //System.Net.WebProxy pry = new System.Net.WebProxy("194.80.164.8", 80);
    //pry.Credentials = CredentialCache.DefaultCredentials;
    //WebRequest.DefaultWebProxy = pry;

    Stream s = webClient.OpenRead(strUrl);

    HtmlDocument doc = new HtmlDocument();
    doc.Load(s);

    HtmlNode link = doc.DocumentNode.SelectNodes("//*[@id='profile_bandschedule']")[0];

This would return you a enumarable object which you could loop around and insert values of the html into the database.

See another example:

How to use HTML Agility pack

Community
  • 1
  • 1
Mantisimo
  • 4,203
  • 5
  • 37
  • 55
  • to use HtmlDocument and HtmlNode which name space i need to add on my project.VS 2008 Show me message add references – shamim Jul 21 '10 at 12:56
  • 1
    Look at the link I added at the bottom, there is a more comprehensive example there. It also contains the download link for the HTML Agiliy pack. You'll need to download that. Add the dll to your bin directry and then reference this in your vs project. – Mantisimo Jul 21 '10 at 15:04
0

Actually, on a typical dynamic HTML page, the table's values are loaded from the database. But, if you want, you can still parse the page's source, and save every row's values into your database. Is that what you want to do?

fidler
  • 113
  • 2
  • 7
0

You can parse the table with jQuery, create client side array or object and send it back to the server with an AJAX call or form post.

HTML:

<!DOCTYPE html>
<html>
<head>
<script class="jsbin" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<meta charset=utf-8 />
<title>JS Bin</title>
<!--[if IE]>
  <script src="http://html5shiv.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->   
</head>
<body>
  <table id="tableData">
     <tr id="tableHeader">
        <td>col1</td>
        <td>col2</td>
      </tr>    
      <tr id="tableRow">
        <td>data1row1</td>
        <td>data2row2</td>
      </tr>
      <tr id="tableRow">         
        <td>data1row2</td>
        <td>data2row3</td>
      </tr>
  </table>
</body>
</html>

SCRIPT:

$(document).ready(function() {
     //parse all the data
     $('#tableData tr').each(function() {
         if($(this).attr('id')=='tableHeader')
         {
           alert('this is the header row');          
         }    
         $('td', this).each(function() {             
              alert($(this).html());
         });

    });

   //post the form or send data via AJAX

});
rick schott
  • 21,012
  • 5
  • 52
  • 81
-1

If you use this algorithm, it is easy to pull HTML into a SQL Server database

https://sourceforge.net/projects/sqldom/

I take those stored procedures and fire them in order to parse HTML and it provides REALLY beautiful parsing of HTML data into SQL Server, and it works in nearly any version of SQL Server

Step 1: Download the file named 'SQLDOM_core_persist_927.sql' from the SourceForge link above

Step 2: run this SQL to make a test database

create database SQLDom_Test1
go
use SQLDom_Test1
go

Step 3: Open the script in SSMS. Go FIND/Replace and replace all '#' symbols with empty string ''. I prefer doing it this way, because I don't want to run temporary tables and temporary stored procedures.

Step 4: Execute the script (note you might need to turn on Object (OLE) Automation/Scripting (the script from source forge gives you the command to do that, if necessary)

Step 5: View the output, and I recommend looking at the example #3. I'd go ahead and run this code (it takes me 5 seconds, and it returns 196 rows of beautifully parsed HTML. See Step 6 (at the bottom) for further analysis

--Example 3: Parse and re-render from a URL DECLARE @HTML varchar(MAX)

EXEC sputilGetHTTP
   @URL = 'http://www.google.com',
   @ResponseText = @HTML OUTPUT,
   @SuppressResultset = 1  

EXEC spactDOMLoad @HTML=@HTML
EXEC spgetDOM 
EXEC spgetDOMHTML @PrettyWhitespace=1, @PrintHTML = 1

----- FULL OUTPUT FROM THE SCRIPT INSTALLATION ----- (Again, if it is necessary to turn on OLE Automation, the script will inform you of that step)

SQLDOM version .927 has been successfully loaded and is ready for use.

In this version, stored procedures are standard persistent stored procedures that access temporary tables. Since the scope of the temporary tables needs to persist across all calls to the SQLDOM procedures on a database connection, the caller must explicitly create the temporary tables on the connection before calling SQLDOM.

To obtain the SQL statement to create the temporary tables that needs to be called by the caller, do this:

EXEC sdom.spgetInitSession

This returns the needed SQL in three ways: in the @SQLToExecute parameter, in the SQLToExecute column in the resultset returned, and output via a PRINT statement. Execute this code on the connection before attempting to use SQLDOM. REMEMBER: you can NOT do this in a call like: EXEC(@SQLToExecute) because the scope of the temporary tables would be limited to the EXEC() statement--the temp tables would be immedately dropped after the EXEC() statement. You will likely need to copy-and-paste the SQLToExec code and manually execute that before you make calls to SQLDOM.

(Temp tables HAVE been created on this connection: you can immediately try out SQLDOM here with no further initialization. But you will need to create the temp tables yourself on any new SQL connection.)

Things to try: [pardon the fact that StackOverflow is butchering this mixed HTML/SQL script]

--Example 1: Simple parse of string EXEC sdom.spactDOMLoad @HTML = 'Hello World.

SQLDOM ROCKS!

' EXEC sdom.spgetDOM

--Example 2: Render HTML from DOM (that we parsed in Example 1 above) EXEC sdom.spgetDOMHTML @PrettyWhitespace=1, @PrintHTML = 1

--Example 3: Parse and re-render from a URL DECLARE @HTML varchar(MAX)

EXEC sdom.sputilGetHTTP
   @URL = 'http://www.google.com',
   @ResponseText = @HTML OUTPUT,
   @SuppressResultset = 1  

EXEC sdom.spactDOMLoad @HTML=@HTML
EXEC sdom.spgetDOM 
EXEC sdom.spgetDOMHTML @PrettyWhitespace=1, @PrintHTML = 1

--Example 4: Parse from a string, modify the DOM, render resulting HTML

EXEC sdom.spactDOMLoad @HTML = '<html><body>Hello World.<br /><div id="myContent">Future content goes here</div></body></html>'

EXEC sdom.spactDOMLoad @HTML = '<div>Here is some neat stuff about <b>SQLDOM</b></div>',
    @Selector = '.myContent'

EXEC sdom.spgetDOM 
EXEC sdom.spgetDOMHTML @PrettyWhitespace=1,      
    @PrintHTML = 1

Step 6: --Show me all the hyperlinks (you will need to join this to tblDomAttribs to see the details)

SELECT * 
FROM [dbo].[tblDOM]
WHERE Tag = 'A'

I'm pretty impressed that this script can chew through 50k HTML characters and it's simple to parse out whatever you are looking for.

It's simple to join tblDom and tblDomAttribs, look at the tables, I believe that it is simply the DEID column you have to join on (to see the href of the A tags)

DatabaseAaron.com
  • 1
  • 1