Improve company productivity with a Business Account.Sign Up

x
?
Solved

How to import the HTML page body contents into sql Data Table

Posted on 2013-12-25
9
Medium Priority
?
2,476 Views
Last Modified: 2016-06-15
The page_Details column type declared as (nvarchar(max) in the sql Table. How  to import the HTML page body contents into sql Data Table.

Thanks
0
Comment
Question by:KavyaVS
  • 4
  • 4
9 Comments
 
LVL 20

Expert Comment

by:Rikin Shah
ID: 39739075
Hi,

Where exactly the HTML Page is getting loaded?
0
 

Author Comment

by:KavyaVS
ID: 39739076
The HTML page is in the C drive of sql server.

Thanks
0
 
LVL 20

Assisted Solution

by:Rikin Shah
Rikin Shah earned 2000 total points
ID: 39739090
And you want whole HTML file to be dumped to the SQL Column?

I think you must have got the code to read the content of the file... All you need to do is remove HTML tags from the content. Here is the function which will help you get plain text from the HTML string...

private string GetPlainTextFromHtml(string htmlString)
{
    string htmlTagPattern = "<.*?>";
    var regexCss = new Regex("(\\<script(.+?)\\</script\\>)|(\\<style(.+?)\\</style\\>)", RegexOptions.Singleline | RegexOptions.IgnoreCase);
    htmlString = regexCss.Replace(htmlString, string.Empty);
    htmlString = Regex.Replace(htmlString, htmlTagPattern, string.Empty);
    htmlString = Regex.Replace(htmlString, @"^\s+$[\r\n]*", "", RegexOptions.Multiline);
    htmlString = htmlString.Replace("&nbsp;", string.Empty);

    return htmlString;
}

Open in new window

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Comment

by:KavyaVS
ID: 39739357
I don't want to remove html tags from from the html page. I want to save as it is into
Sql Data Table column. I don't want the whole html page. I want to save the body tag contents in the sql column(data type nvarchar(max))
Any suggestions please.


The following query inserting the HTML page content into Sql DataTable
 when the page_Details column type declared as (XML(.),null(The content
 inside the body tags in .aspx page was saved as xml file)
 Ex:<PageContents>

     - <![CDATA[
 <div>
 </div>

  ]]>

   </PageContents>
 Now the page_Details column type declared as (nvarchar(max). The below
 query is not inserting data.The column type can not be changed. How to
 insert the html data there.

 UPDATE [Content_Site].[dbo].t_Page_List

 SET Page_Details =(

 SELECT * FROM OPENROWSET(

    BULK 'C:\PagedETAILS_Xml\Page1content.xml’,

            SINGLE_BLOB

 ) AS x

 )

 WHERE PageID = 1

 GO

Thanks
0
 
LVL 20

Accepted Solution

by:
Rikin Shah earned 2000 total points
ID: 39739659
Hi,

I'm not proficient in my SQL but you can do something like this-

DECLARE @xml NVARCHAR(MAX)

SET @xml = SELECT * FROM OPENROWSET(
   BULK 'C:\SampleFolder\SampleData3.txt',
           SINGLE_BLOB
) AS x


UPDATE [Content_Site].[dbo].t_Page_List
SET Page_Details = @xml
WHERE PageID = 1

Open in new window

0
 
LVL 20

Expert Comment

by:Rikin Shah
ID: 39739660
You might need to cast the x to nvarchar.
0
 

Author Comment

by:KavyaVS
ID: 39746243
I've requested that this question be closed as follows:

Accepted answer: 167 points for rikin_shah's comment #a39739659
Assisted answer: 166 points for rikin_shah's comment #a39739075
Assisted answer: 0 points for KavyaVS's comment #a39739076
Assisted answer: 167 points for rikin_shah's comment #a39739090

for the following reason:

Thanks
0
 

Author Closing Comment

by:KavyaVS
ID: 39746244
Thanks
0
 

Expert Comment

by:Safak KAYA
ID: 41654852
Hello, I am new in sql but I have the same issue.  I want to import a particular data from a web page's html source code. to sql table.

Is it possible?

Thanks
0

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

579 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