|
SQL Anywhere 17.0.11.6933 I want to generate an XML from the database using OPENXML(). The receiving party wants the XML to have HTML symbols for some characters. For example they want to receive HTML_DECODE('18.00 °:C') to display '18.00 °C'. How can I achieve that some characters are replaced by HTML codes as described here: https://www.rapidtables.com/web/html/html-codes.html As in the example HTML_DECODE() is working fine but HTML_ENCODE('18.00 °C') does not result in '18.00 °:C' or '18.00 °:C' Is there a proper way to achieve this conversion in the XML into HTML without using replace() for around 60 characters. |
|
HTML_ENCODE only supports " ( Because you are looking to encode characters greater then 0x20 then I think you will need to write a function for those specific characters they want encoded. Do you know if something like below is possible in SQL Anywhere? Declare @S nvarchar(max) = 'sweet shop £'
(15 May, 09:25)
Frank Vestjens
Replies hidden
1
I don't think so, and I'm not sure whether you could use an APPLY operator with a list of mappings applied to a supplied string. But I think a user-defined replace function is not too hard to code, unless the replace has to respect a particular order (which could also be done via sorting the entries accordningly). A very simple starting point: drop table if exists LT_Replace;
create local temporary table LT_Replace (strSource nvarchar(10) primary key, strTarget nvarchar(10) unique) not transactional;
-- Just the first 10 entries from the list above (just in swapped order)
insert LT_Replace values ('"', '":'),('&', '&:'),('''', '&apos:'), ('<', '<:'), ('>', '>:'), (' ', ' :'), ('¡', '¡:'), ('¢', '¢:'),('£', '£:'), ('¤', '¤:');
select * from LT_Replace order by 1;
create function fnReplace(varTest long nvarchar) returns long nvarchar
begin
declare strReplaced long nvarchar = varTest;
for forCrs as crs cursor for
select strSource, strTarget from LT_Replace
do
set strReplaced = replace(strReplaced, strSource, strTarget);
end for;
return strReplaced;
end;
create variable varTest long nvarchar =
'This is some random string with some <tags> & "quoted text", worth < £ 1,00 .';
select varTest, fnReplace(varTest);
Needless to say, I don't know why the HTML encodings in your code are shown as encodings whereas mine are not...
(15 May, 11:03)
Volker Barth
Thanks for the example. I starterd working on it, but due to the fact of the current collation used in the database the string below '°C Infineum P6072C - Do not reheat ªabove: 65 °C - ZINC PRODUCT ! ! ! ' Instead of parsing only the 'ª' in the string it parses all 'a' in the string. I created a new database with UTF8BIN collation and with respect accents and then it is working. However I have no idea what could be the effect of changing the collation of the database. Is there a way to do it runtime in the function?
(5 hours ago)
Frank Vestjens
|
