Converting pdmlink.WTChangeOrder2.formattedTextA8 for SSRS

[ClockworkOrange]

I want to create a SSRS report  that any user (Licensed or not) can run completely outside of the Windchill environment.   I was able to get the SQL I needed generated from the Windchill report builder.  It runs without issue in SSMS.  The only issue I have is that the Descriptions being entered in the change orders is now Rich Text Format (RTF) rather than plain text.  This appears to be stored in the following Table: pdmlink.WTChangeOrder2  in the following Field:formattedTextA8.  In SQL this is an example of what I see stored in formattedTextA8 which is VARCHAR(8000) DATA TYPE:rO0ABXQAPjxwPkluaXRpYWwgUmVsZWFzZSBTcGFyZXMgRHJhd2luZ3MgZm9yIEludGVybmF0aW9uYWwgU2FsZXM8L3A+.

Viewed in Windchill you see this: "Initial Release Spares Drawings for International Sales".  This is the query.

USE pdmlink
SELECT 
A1B.name AS Name,
--A1B.idA2A2,
--A0.completedBy,
USERS.fullName,
--A0.idA3A2ownership,
A1.description AS Description,
A1.formattedTextA8 AS Description,
--A0.description,
--A1.statestate as State,
--A0.classnameA2A2 as ClassName,
--A0.idA2A2 as ID,
A1B.createStampA2,
A0.createStampA2,
A0.modifyStampA2,
A0.updateStampA2,
CONVERT(varchar,A1.needDate,120) AS NeedDate,
CONVERT(bigint,(DATEDIFF(ss,A1.createStampA2,GETDATE()))/(86400.0)) as DaysOpen,
CONVERT(bigint,(DATEDIFF(ss,A0.updateStampA2,GETDATE()))/(86400.0)) as DaysFromUpdate
FROM 
pdmlink.WorkItem A0, pdmlink.WTUser USERS, pdmlink.WTChangeOrder2 A1
INNER JOIN pdmlink.WTChangeOrder2Master A1B ON (A1.idA3masterReference = A1B.idA2A2)
--LEFT JOIN pdmlink.WTUser USERS ON (A0.idA3A2ownership = USERS.idA2A2)
WHERE (NOT ((A0.status ='COMPLETED'))) AND 
(A0.classnamekeyB4 = CASE A1.latestiterationInfo WHEN 0 THEN CONCAT('OR:',A1.classnameA2A2,':',CONVERT(varchar,A1.idA2A2)) 
WHEN 1 THEN CONCAT('VR:',A1.classnameA2A2,':',CONVERT(varchar,A1.branchIditerationInfo)) END )

 I was hoping someone knew how to convert it to something meaningful in SQL.  I did some looking and it does not appear to be stored in an RTF format.  I would expect to see HTLM tags and such.  To me it looks like its hashed/encrypted.  I also attached the results from the SQL query

[DmitryC]

Hi,

I've tried Base64 decoding on the string:

rO0ABXQAPjxwPkluaXRpYWwgUmVsZWFzZSBTcGFyZXMgRHJhd2luZ3MgZm9yIEludGVybmF0aW9uYWwgU2FsZXM8L3A+

and it decodes into: 

�t><p>Initial Release Spares Drawings for International Sales</p>

It looks like the original Description has something added in front of the text, so I'm not sure if it can be reliably decoded.

You may try the native SQL Server Base64 decoding and see how it goes. Example: https://dba.stackexchange.com/questions/191273/decode-base64-string-natively-in-sql-server

Kind regards,

Dmitry.

[ClockworkOrange]

Ohhh so close!  I swapped out my column/field name for the one they called "BASE64_COLUMN" and I get the following for every record ¬í .  I wonder if I need to CAST  as a string....maybe it doesn't like the way its coming string from SQL?

[ClockworkOrange]

Got it! 

I have to strip rO0ABXQA from the beginning of the BASE64  then it converts.  It still has those HTML tags but I can  clean that up after its converted.  Thanks for your help. Before reading your reply I was talking to another guy here and I commented that these strings looked like SSL certs KEYS when opened in NotePad.

[ClockworkOrange]

So....now this is the question

We have this statement in the Select but where we see BASE64_COLUMN I need to strip the leading characters.  I am tiring to nest a TRIM in there but I'm not sure its supported.   

CONVERT
(
VARCHAR(8000),
CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)')
)

CONVERT
(
VARCHAR(8000),
CAST('' AS XML).value('xs:base64Binary(sql:column((" TRIM(''rO0ABXQA'' FROM BASE64_COLUMN) AS TEXT ")))', 'VARBINARY(MAX)')
)

[ClockworkOrange]

Well this is what ended up working

binaryDescription=

REPLACE(

REPLACE(

REPLACE(SUBSTRING(CONVERT(VARCHAR(800),cast('' as xml).value(

'xs:base64Binary(sql:column("A1.formattedTextA8"))',

'varbinary(max)')),8,8000),'<p>',''),'</p>',CHAR(13)+CHAR(10)),'<br/>',CHAR(13)+CHAR(10)),