Remove formatting in XML node from SSRB ouput

Transact-SQL
1

When I run MS SQL Server Report Builder, one item is pulled from an XML Node:

SELECT
tblDataStuff.XmlDataField.value('(//*[local-name()="Description"])[1]', 'nvarchar(max)')

The resulting data is:
"{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;{\f1\fnil\fcharset0 Microsoft Sans Serif; \viewkind4\uc1d\f020 TEXT I WANT\f020"

Is there any easy way to hide or strip the formatting code so I just see "TEXT I WANT"?

2

what is \f020? Is it in all rows or is it different on each row? Can you provide a few more examples?

3

Here's another with more of the XML field:

"{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 TEXT I WANT.\fs26\par}"

I'm trying to retrieve the Description information without all the formatting.

4

what is the pattern here? looks like it is always prefixed with fs20 ?
please provide real world sample data otherwise it is guess work.

create table #supply_real_data(id int identity(1,1), charlatan varchar(250))

insert into #supply_real_data
select '{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;{\f1\fnil\fcharset0 Microsoft Sans Serif; \viewkind4\uc1d\f020 TEXT I WANT\f020}' union
select '{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}\viewkind4\uc1\pard\f0\fs20 TEXT I WANT.\fs26\par}'


select dbo.[RTF2Text](charlatan), LEN(charlatan)) as saint, charlatan From #supply_real_data

drop table #supply_real_data

since this looks like Rich Text File, you might want to create a CLR or go this route which might not necessarily a good approach but seems to work

https://www.codeproject.com/Tips/821281/%2FTips%2F821281%2FConvert-RTF-to-Plain-Text-Revised-Again

5

Does this help:

SELECT
tblDataSign.Type
,tblDataPermit.XmlDataField.value('(//*[local-name()="Description"])[1]', 'nvarchar(max)') AS Description
FROM
tblDataSign
WHERE
tblDataSign.Type IN (@Type)

6

yes it would work if you gave me permission to your database and I can access tblDataSign

show us some real data from tblDataSign

7

This looks like RTF coming from the database - which SSRS does not support. Here is one way you can convert the RTF to plain text: https://support.resolver.com/hc/en-ca/articles/115005895943-Convert-RTF-to-Plain-Text-in-SSRS

1 Like
8

Thank you