Query to get data which has HTML tag from db table

Question

0.00/5 (No votes)

See more:

I have a table with a column which may or may not have data with html tags in it.
My requirement is to import this data in excel sheet for report purpose. I wrote a function which returns the data but does it with html tags.
I would like to strip down the tags and returns the data only.
For example if there is or tag, I would like have line break in excel cell or atleast a comma to separate the data.
and if there is image tag, i would like to display only links to that file.

Db table:
Project Notes
Alarm needs tested return trip requiredMissing DVR will return when received 
 <img class="notesimage" src="https://myexample.com/myImageFolder/myImage.jpg" style="margin: 5px;">

In Excel I would like to display as :

What I have tried:

this is my function:

ALTER FUNCTION [dbo].[ufnGetProjectNotes](@ProjectID int)  
RETURNS nvarchar(max)   
AS   

BEGIN  
    DECLARE @projectNotes nvarchar(max);  
    SELECT  @projectNotes =stuff( (SELECT ','+Notes 
                FROM [StoreDevelopment].[dbo].[ProjectNotes] p2
               WHERE p2.ProjectID = p1.ProjectID
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
            ,1,1,'') 
       FROM [StoreDevelopment].[dbo].[ProjectNotes] p1
      GROUP BY ProjectID having projectid=@ProjectID;
   
    RETURN @projectNotes;  
END

Posted 18-Oct-17 9:38am

s23user

Updated 24-Nov-17 0:05am

RDBurmon

v2

Add a Solution

Comments

Maciej Los 19-Oct-17 1:52am

Excel is able to read html files...

s23user 19-Oct-17 8:57am

It did not in my case. data get imported as it's in database.

s23user 19-Oct-17 9:50am

What my current function does is concates projects Notes from all rows for a given project Id. If some way i could dump the return results in a temp table where i can use any function Replace Substring etc to trim and replace tag and return the massaged result back to caller function.
But I am beginner in Sql Server. I do not know how to write it. I appreciate the help and I could learn as well.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Accepted Answer · 2017-11-24T00:04:00

modified a bit to include replace statement
it will give your the comment which is specified between that HTML tag

SQL

ALTER FUNCTION [dbo].[ufnGetProjectNotes](@ProjectID int)  
RETURNS nvarchar(max)   
AS   
 
BEGIN  
    DECLARE @projectNotes nvarchar(max);  
    SELECT  @projectNotes =stuff( (SELECT ','+Notes 
                FROM [StoreDevelopment].[dbo].[ProjectNotes] p2
               WHERE p2.ProjectID = p1.ProjectID
               FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
            ,1,1,'') 
       FROM [StoreDevelopment].[dbo].[ProjectNotes] p1
      GROUP BY ProjectID having projectid=@ProjectID;
   
    projectNotes = Replace(projectNotes,'</P><P>','')
    projectNotes = SUBSTR(projectNotes,3,INDEX(projectNotes,'</p')))
    RETURN @projectNotes;  
END