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

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
KavyaVSAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Hi,

Where exactly the HTML Page is getting loaded?
KavyaVSAuthor Commented:
The HTML page is in the C drive of sql server.

Thanks
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
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

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

KavyaVSAuthor Commented:
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
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
You might need to cast the x to nvarchar.
KavyaVSAuthor Commented:
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
KavyaVSAuthor Commented:
Thanks
Safak KAYACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.