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

[former_SQLA_member1694948]

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