Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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:

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 '23, 02:57

Frank%20Vestjens's gravatar image

Frank Vestjens
accept rate: 21%

edited 12 May '23, 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 '23, 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 '23, 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 '23, 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?

(30 May '23, 11:42) Frank Vestjens

Sorry, I had not thought of collation issues. Note, there's the BYTE_REPLACE() function which does work on binary characters (ignoring collations) and therefore should work as expected here.

You have to rebuild a database to change its collation, so that's quite a step. If you only need different collation behaviour now and then, you can use "collation-tailoring" for comparisons, see this FAQ with a sample using the COMPARE function in the question. That's how we do comparions when the required semantics w.r.t. to case/accents/sort order differ from our database collation...

Alternatively, if your function does return XML encoded in UTF8, you could already use NCHARs within your function to prepare your strings and use the default NCHAR collation - if that has different collation settings. (IIRC, the default NCHAR collation still ignores case and accents...)

(31 May '23, 03:55) Volker Barth
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]( "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 '23, 02:57

question was seen: 553 times

last updated: 31 May '23, 04:07