SQL Anywhere

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 &#176: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 &#176:C' or '18.00 &deg:C'

Is there a proper way to achieve this conversion in the XML into HTML without using replace() for around 60 characters.

asked 12 May, 02:57

Frank%20Vestjens's gravatar image

Frank Vestjens
accept rate: 23%

edited 12 May, 07:34

HTML_ENCODE only supports " (&quot;),' (&#39;), & (&amp;), < (&lt ;), > (&gt ;) and codes nn less than 0x20 (&#xnn;)

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.

permanent link

answered 15 May, 07:22

pcollins's gravatar image

accept rate: 0%

Do you know if something like below is possible in SQL Anywhere?

Declare @S nvarchar(max) = 'sweet shop £'

Select @S = replace(@S,MapFrom,MapTo) From ( values ('&quot:','"'),('&amp:','&'),('&apos:',''''),('&lt:','<'), ('&gt:','>'),('&nbsp:',' '),('&iexcl:','¡'), ('&cent:','¢'),('&pound:','£'),('&curren:','¤'),('&yen:','¥'), ('&brvbar:','¦'),('&sect:','§'),('&uml:','¨'), ('&copy:','©'),('&ordf:','ª'),('&laquo:','«'),('&not:','¬'), ('&reg:','®'),('&macr:','¯'),('&deg:','°'), ('&plusmn:','±'),('&sup2:','²'),('&sup3:','³'),('&acute:','´'), ('&micro:','µ'),('&para:','¶'),('&middot:','·'), ('&cedil:','¸'),('&sup1:','¹'),('&ordm:','º'),('&raquo:','»'), ('&frac14:','¼'),('&frac12:','½'),('&frac34:','¾'), ('&iquest:','¿'),('&Agrave:','À'),('&Aacute:','Á'), ('&Acirc:','Â'),('&Atilde:','Ã'),('&Auml:','Ä'),('&Aring:','Å'), ('&AElig:','Æ'),('&Ccedil:','Ç'),('&Egrave:','È'), ('&Eacute:','É'),('&Ecirc:','Ê'),('&Euml:','Ë'),('&Igrave:','Ì'), ('&Iacute:','Í'),('&Icirc:','Î'),('&Iuml:','Ï'),('&ETH:','Ð'), ('&Ntilde:','Ñ'),('&Ograve:','Ò'),('&Oacute:','Ó'), ('&Ocirc:','Ô'),('&Otilde:','Õ'),('&Ouml:','Ö'),('&times:','×'), ('&Oslash:','Ø'),('&Ugrave:','Ù'),('&Uacute:','Ú'), ('&Ucirc:','Û'),('&Uuml:','Ü'),('&Yacute:','Ý'),('&THORN:','Þ'), ('&szlig:','ß'),('&agrave:','à'),('&aacute:','á'), ('&:','â'),('&atilde:','ã'),('&auml:','ä'),('&aring:','å'), ('&aelig:','æ'),('&ccedil:','ç'),('&egrave:','è'), ('&eacute:','é'),('&ecirc:','ê'),('&euml:','ë'),('&igrave:','ì'), ('&iacute:','í'),('&icirc:','î'),('&iuml:','ï'), ('&eth:','ð'),('&ntilde:','ñ'),('&ograve:','ò'),('&oacute:','ó'), ('&ocirc:','ô'),('&otilde:','õ'),('&ouml:','ö'), ('&divide:','÷'),('&oslash:','ø'),('&ugrave:','ù'), ('&uacute:','ú'),('&ucirc:','û'),('&uuml:','ü'),('&yacute:','ý'), ('&thorn:','þ'),('&yuml:','ÿ'),('&amp:','&'),('&deg:','°'), ('&infin:','∞'),('&permil:','‰'),('&sdot:','⋅'), ('&plusmn:','±'),('&dagger:','†'),('&mdash:','—'),('&not:','¬'), ('&micro:','µ'),('&euro:','€'),('&pound:','£'), ('&yen:','¥'),('&cent:','¢'),('&euro:','€'),('&pound:','£'), ('&yen:','¥'),('&cent:','¢') ) A (MapTo,MapFrom)

Select cast(cast(@S as xml) as nvarchar(max))

(15 May, 09:25) Frank Vestjens
Replies hidden

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
   declare strReplaced long nvarchar = varTest;
   for forCrs as crs cursor for
      select strSource, strTarget from LT_Replace
      set strReplaced = replace(strReplaced, strSource, strTarget);
   end for;
   return strReplaced;

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 12 May, 02:57

question was seen: 131 times

last updated: 5 hours ago