Converting RTF to Text in SQL Part 3

captain-picard-facepalm-meme

Yes, Part 3. This technique was published some time ago and as it gets further “field testing”, new conditions have come to light which require a revision of this technique. Assuming this is the last post on this topic, I need to figure out how to duplicate this logic in PowerBI.

I consider this to be version 15 or so of this query since my first version was written in mid-September of 2010. The changes here are as follows.

First, the DTD definitions have been expanded to include all possible HTML codes. This should prevent a report blowing up when someone decides to use an unexpected symbol somewhere.

Second, I keep seeing random <br> tags in the fields, which ironically is breaking the query. I’ve added a replace statement for that as well.

Lastly, I’ve expanded the field sizes to Max where necessary to accommodate truncation issues.

Sample code is below. I strongly recommend copying into NotePad first so that you don’t accidentally get curly quotes. I’ve also left it as straight text to make it easier to copy.

declare @Headxml nvarchar(3000)
declare @Footxml nvarchar(50)

set @Headxml = N'<?xml version=”1.0″?>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
http://www.w3.org/TR/html4/loose.dtd”
[<!ENTITY quot “&#34;”>
<!ENTITY amp “&#38;”>
<!ENTITY lt “&#60;”>
<!ENTITY gt “&#62;”>
<!ENTITY nbsp “&#160;”>
<!ENTITY iexcl “&#161;”>
<!ENTITY cent “&#162;”>
<!ENTITY pound “&#163;”>
<!ENTITY curren “&#164;”>
<!ENTITY yen “&#165;”>
<!ENTITY brvbar “&#166;”>
<!ENTITY sect “&#167;”>
<!ENTITY uml “&#168;”>
<!ENTITY copy “&#169;”>
<!ENTITY ordf “&#170;”>
<!ENTITY laquo “&#171;”>
<!ENTITY not “&#172;”>
<!ENTITY shy “&#173;”>
<!ENTITY reg “&#174;”>
]><html><body>’

set @Footxml = N'</body></html>’

select   *
,ISNULL(LTRIM((CONVERT(xml,(@Headxml + replace([YourMulti-lineCustomField],'<br>’,”) + @Footxml),3).value(N'(/)’,’nvarchar(Max)’))),”)   AS [YourMulti-lineCustomFieldNewName]

,ISNULL(LTRIM((CONVERT(xml,(@Headxml + replace([YourMulti-lineCustomField2],'<br>’,”) + @Footxml),3).value(N'(/)’,’nvarchar(Max)’))),”)   AS [YourMulti-lineCustomFieldNewName2]

FROM  dbo.MSP_EpmProject_UserView

How To: Connect Project Pro to Project Online

Here's a quick article on how to connect Project Pro to Project Online. The question seems to come up on a regular basis so I'm documenting it here. In Project 2013 and Project Online, Microsoft added the ability for Project Pro to autoconfigure … [Continue reading]

How to show Implementation Milestone Dates in Project Center

image.png

This technique illustrates how to use formulas to extract dates from tagged milestone tasks within your project plans and show the dates in the Project Center. This technique has been tested with Project Online and Project Server 2013 On Premises. … [Continue reading]

Don’t Get Burned By Your Security Templates

image.png

Problem If you’ve ever tried to use the built-in security templates in Project Web App, you may have accidentally messed up your security model without realizing it. This problem applies to Project Server 2003-2013 versions. Security templates … [Continue reading]

Missed Project Conference?

image.png

Good news! All session recordings are now available online!  View them at http://channel9.msdn.com/Events/Project/2014/ Technorati Tags: Project Conference 2014,Project Server 2013,Project Online,Project Lite … [Continue reading]

Take Control of Your PWA Home Page with 4 Clicks

image.png

Have you ever looked at the Project Web App home page and felt it was simply too plain for Jane? I understand the need for design simplicity but sometimes the PWA home page simply feels like underutilized real estate. The Need Your manager wants … [Continue reading]

Project 2013 SP1 is now available!

Please use the following links to download the Service Pack 1 bits. Also, ensure you patch SharePoint and Project Server together. Project 32-bit     http://www.microsoft.com/en-us/download/details.aspx?id=42013 Project 64-bit     … [Continue reading]

New Project Portfolio Management (PPM) Guide Available

Microsoft has released the 2013 version of the Project Portfolio Management (PPM) guide for Project Server 2013 and Project Online. You can download it here. http://www.microsoft.com/en-us/download/details.aspx?id=41549   … [Continue reading]

Querying Multi-Value Custom Fields

thumbsup.jpg

Scenario You have a report where the need is to show multiple values for a given custom field. For example, you have a multi-value Project custom field for Impacted Business Organizations. You want to see your values as a comma delimited list so … [Continue reading]

Database Diagrams–Project Server Reporting Database

image.png

These high level entity relationship diagrams were first published in the deck for my Project Conference Hands On Lab deck. I’ve had a number of requests for this information so here it is. These diagrams are based on the 2010 RDB but the 2013 RDB … [Continue reading]

%d bloggers like this: