查询从db表获取具有HTML标记的数据 [英] Query to get data which has HTML tag from db table

问题描述

我有一个带有列的表，其中可能包含或不包含带有html标签的数据。

我的要求是在excel表中导入此数据以用于报告目的。我写了一个函数，它返回数据，但用html标签做。

我想删除标签并仅返回数据。

例如，如果有< p> < / p为H.或者< br />标签，我想在excel单元格中使用换行符或至少用逗号分隔数据。

如果有图像标记，我只想显示该文件的链接。



Db表：

项目备注

< p>报警需要测试返程需要< / p> ;< p>缺少DVR将在收到时返回< / p>

< br>< img class =notesimagesrc =https://myexample.com/myImageFolder/myImage .jpgstyle =margin：5px;>









在Excel中，我希望显示为：



我尝试了什么：



这是我的功能：

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 <p> </p> or <br/> 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
<p>Alarm needs tested return trip required</p><p>Missing DVR will return when received </p>
<br><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

推荐答案

修改了一下以包含替换声明

它会给你评论在该HTML标记之间指定

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

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

这篇关于查询从db表获取具有HTML标记的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！