In reply to: Patryk Kornak
Hi Daniel,
thanks for your very detailed answer. I rather will not update the compatibility level on my own :), as working on enterprise Webcon environment, then using JSON would not be possible in my case too.
The thing is that I need to integrate the comment data with comments from the obsolete system database (union them) and display in the Data Table field. Will look further for the solution.
Cheers,
Patryk
Hi Patryk,
ok, if you need to display the data in a Data Table we have to rely on the build in features.
This is not an ideal solution but in our cases it will work, but I don't have any idea how this performance in a real world scenario. :)
declare @jsonText nvarchar(max) = '[{"d":"2022-02-21T14:40:40.086895","l":"username@domain.com","a":"Name Surname","c":"Text of the comment number 1"},{"d":"2022-02-22T07:38:42.0101194","l":"username@domain.com","a":"Name Surname","c":"Text of the comment number 2"}]'
select dbo.ClearWFElemId(item) as [Date],
dbo.ClearWFElemId(dbo.ClearWFElem(item)) as Account,
dbo.ClearWFElemId(dbo.ClearWFElem(dbo.ClearWFElem(item))) as DisplayName,
dbo.ClearWFElemId(dbo.ClearWFElem(dbo.ClearWFElem(dbo.ClearWFElem(item)))) as Comment
from dbo.SplitToTable(Replace(Replace(Replace(Replace(Replace(Replace(@jsonText,'","c":"','#'),'","a":"','#'),'","l":"','#'),'"d":"',''),'[{',''),'"}]',''),'"},{')
Best regards,
Daniel