Question Re: openxml xml parser error in Technology Q&A

openxml xml parser error

former_SQLA_member1694948 — Fri, 15 Dec 2017 04:01:00 GMT

Hello!

When using "openxml" on results of the Google Geocoding API, I receive an XML parser error with the message "Invalid control character" for certain address details. Here's an example:

select * from openxml("CallGoogleapi"('https://maps.googleapis.com/maps/api/geocode/xml?address=pasica+75000+tuzla&language=en'),'/GeocodeResponse/result/geometry/location/lat') with("lat" long varchar 'text()')

with "CallGoogleapi" defined as

FUNCTION "CallGoogleapi"( in "sLocation" long varchar ) 
returns xml
url '!sLocation!'
type 'HTTP:GET'

What can I do, so that openxml does not geerate this error?

Thanks in advance!

Dirk

Re: openxml xml parser error

Former Member — Fri, 15 Dec 2017 20:25:39 GMT

I suggest to have a look at the XML document returned from the API, ideally in an XML aware editor / viewer which visualizes the document structure.

HTH
Volker
DB-TecKnowledgy

Re: openxml xml parser error

former_SQLA_member1694948 — Sat, 16 Dec 2017 05:11:44 GMT

I forgot to mention that this call normally works. For "berlin" I can calculate lat/log without oproblems:

"https://maps.googleapis.com/maps/api/geocode/xml?address=berlin&language=en"

"......<lat>52.5200066</lat><lng>13.4049540</lng>....."

In the query "pasica+75000+tuzla" sa-17 reports the parsing error at character 150, line 6, column 41:

1    < ?xml version="1.0" encoding="UTF-8"?>
2    -< GeocodeResponse>
3     < status>OK< /status>
4     < result>
5        < type>route< /type>
6        < formatted_address>Hadži Hasanage Pašića, Tuzla 75000, Bosnia and Herzegovina< /formatted_address>
7        < address_component>
8          < long_name>Hadži Hasanage Pašića< /long_name>
          .....

Saving the result to a file, I see the following result with an unicode character:

  <formatted_address>Hadži Hasanage Paši**/u001a**a, Tuzla 75000, Bosnia and Herzegovina</formatted_address>

In line 6, column 41 there is a hex "1A00" or U+001A. Apparently, openxml has a problem with this character. Would I have to do a conversion here?

Kind regards, Dirk

Re: openxml xml parser error

Former Member — Sat, 16 Dec 2017 07:30:28 GMT

I'm not sufficiently expert in XML to decide whether this is correct but my guess is that 'encoding="UTF-8"' makes it valid. Unless your database's char collation is UTF-8, it might be worth trying to use nvarchar instead of varchar in your definitions.
At least, this information could help to locate the problem.
Cheers
Volker

Re: openxml xml parser error

Vlad — Sat, 16 Dec 2017 08:23:01 GMT

There is the ENCODING option in the help article: http://dcx.sap.com/index.html#sqla170/en/html/817205cf6ce21014a949c790fd6b0608.html Can you please try to use UTF-8 in this operator?

Re: openxml xml parser error

Breck_Carter — Sun, 17 Dec 2017 08:54:31 GMT

Try it with one exclamation: url '!sLocation'

Re: openxml xml parser error

VolkerBarth — Sun, 17 Dec 2017 11:56:01 GMT

I think you should use (long) nvarchar data types for the OPEN XML output columns (i.e. in your case the "lat" column). According to the docs, the XML data will then be interpreted as NCHAR data, as well - to cite:

The xml-data is parsed directly in the NCHAR encoding if there are any NCHAR columns in the output. The xpath and namespaces arguments are also converted and parsed in the NCHAR encoding.

Re: openxml xml parser error

former_SQLA_member1694948 — Mon, 18 Dec 2017 01:54:36 GMT

Thanks for the hint. Unfortunately, I continue to receive the same error message.

select * from openxml("CallGoogleapi"('https://maps.googleapis.com/maps/api/geocode/xml?address=pasica+75000+tuzla&language=de@region=de'),'/GeocodeResponse/result/geometry/location/lat') with("lat" long nvarchar 'text()') OPTION (encoding="UTF-8")

returns still the same error.

Re: openxml xml parser error

former_SQLA_member1694948 — Mon, 18 Dec 2017 02:01:28 GMT

Thanks, but unfortunately this did not help either.

Re: openxml xml parser error

former_SQLA_member1694948 — Mon, 18 Dec 2017 03:23:42 GMT

I tried both, but I still get the XML parsing error at the same position.

Re: openxml xml parser error

former_SQLA_member1694948 — Mon, 18 Dec 2017 03:39:02 GMT

I tried this, but I still get the error message.

Re: openxml xml parser error

former_SQLA_member1694948 — Mon, 18 Dec 2017 04:38:20 GMT

I tried it again with newly created databases. When using the default collation ("COLLATION '1252LATIN1' NCHAR COLLATION 'UCA'") I still get the error.

When using "COLLATION 'UCA' NCHAR COLLATION 'UCA'" everything works fine!

I changed everything to "NVARCHAR" and tried it again with default collation: I still get the error.

select * from openxml("CallGoogleapi"('https://maps.googleapis.com/maps/api/geocode/xml?address=pasica+75000+tuzla&language=de@region=de'),'/GeocodeResponse/result/geometry/location/lat') with("lat" long nvarchar 'text()') OPTION (ENCODING='UTF-8')

FUNCTION "DBA"."CallGoogleapi"( in "sLocation" long nvarchar ) 
returns long nvarchar
url '!sLocation'
type 'HTTP:GET'

Shouldn't the call also be possible with the default collation?

Re: openxml xml parser error

VolkerBarth — Mon, 18 Dec 2017 04:43:28 GMT

Does it work when you replace the mask "/u" with "\\u", as that is the expected encoding when using UNISTR to generate NCHAR data based on their code point?

Re: openxml xml parser error

VolkerBarth — Mon, 18 Dec 2017 04:44:05 GMT

What encoding does the file use?

Re: openxml xml parser error

former_SQLA_member1694948 — Mon, 18 Dec 2017 04:53:05 GMT

I called "SELECT "CallMapsGoogleapi"('https://maps.googleapis.com/maps/api/geocode/xml?address=pasica+75000+tuzla&language=de@region=de')" and saved the rsult directly in Interactive SQL to a file?

Interactive SQL used UTF-8.

Re: openxml xml parser error

former_SQLA_member1694948 — Mon, 18 Dec 2017 04:57:30 GMT

There is no /u or \\u in the output. I can't copy the character to the editor. It is the 20th character in this string: "Hadži Hasanage Paši\x1aa" (between i and a at the end).

As soon as I save the comment, the UTF-8 character disappears.

Re: openxml xml parser error

VolkerBarth — Mon, 18 Dec 2017 07:40:36 GMT

I think the problem is not the OPENXML call but the web client function. It seems to convert the returned value to the CHAR datatype (so it works when you use UTF-8 as CHAR encoding). That restriction is documented here under "data-type", if my assumption is correct:

Only SOAP requests support the transmission of typed data such as FLOAT, INT, and so on. HTTP requests support the transmission of strings only, so you are limited to CHAR types.

So you might have to use a web client function of type SOAP...

Re: openxml xml parser error

Vlad — Tue, 19 Dec 2017 09:13:05 GMT

I was finally able to solve this... I don't even know the correct word... 'thing'. I don't know where to start, because the error is unclear, and the documentation looks incomplete for Unicode-related questions.

There are important links that explain that SqlAnywhere (when it cannot convert a string from one encoding to another) replaces the multi-byte character with '0x1a'. There is an option (on_charset_conversion_failure) that you can try to switch to Error to see if SA faces some difficulties with an implicit conversion. And I forgot to mention that you cannot use this option, because the default collation is LATIN1251 (at least on my machine) - 'single byte'.

If you execute the SELECT statement in SCJVIEW, you will see that SA returns a different string than you submitted:

SELECT  'Pašića Hadži' AS chr1,   'Жпа' AS chr2,
       N'Pašića Hadži' AS nchr1, N'Жпа' AS nchr2;

Here is what I see on my screen:

But after I have created the DB file with the following command line options:

dbinit -dba dba,sql -mpl 3 -ze utf8 -zn UTF8BIN -z UTF8BIN anotherDB

I was able to get what I really wanted:

Even you code works on a fresh DB file:

CREATE OR REPLACE FUNCTION "dba"."CallGoogleApi"( IN "sLocation" LONG NVARCHAR)
RETURNS LONG BINARY
URL '!sLocation'
TYPE 'HTTP:GET'

SELECT * FROM OPENXML("CallGoogleapi"('https://maps.googleapis.com/maps/api/geocode/xml?address=pasica+75000+tuzla&language=de@region=de'), '/GeocodeResponse/result/geometry/location/lat')
              WITH("lat" LONG NVARCHAR'text()')

p.s. don't repeat my faults... don't work with Unicode in SQL. This is painful.

Re: openxml xml parser error

MarkCulp — Tue, 19 Dec 2017 09:27:17 GMT

Note that all SQL statements are converted and transmitted to the server in Database CHAR charset and characters that cannot be mapped to the Database CHAR charset get replaced by the '0x1a' character. This is why you are getting the results that you are seeing.

In a similar way, HTTP results are expected to be in Database CHAR charset as Volker pointed out in his response above.

Re: openxml xml parser error

VolkerBarth — Tue, 19 Dec 2017 10:06:56 GMT

So my conclusion is correct that a SOAP type request is required for NCHAR data when the database does use a single-byte CHAR collation?

I agree with Vlad that Unicode handling with web client procedures could be explained better in the docs... 🙂