openxml xml parser error

[former_SQLA_member1694948]

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'),'/GeocodeR... 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

[Vlad]

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?

[Breck_Carter]

Try it with one exclamation: url '!sLocation'

[former_SQLA_member1694948]

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.

[former_SQLA_member1694948]

Thanks, but unfortunately this did not help either.

[VolkerBarth]

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...