Executing Information Link based on Google BigQuery fails with error "Marshalling Error: INVALID_CHARACTER_ERR: An invalid or illegal XML character is specified."

Executing Information Link based on Google BigQuery fails with error "Marshalling Error: INVALID_CHARACTER_ERR: An invalid or illegal XML character is specified."

When executing an Information Link based on Google BigQuery using the default template, it may fail to load with the following error:

Error message: Could not open information link.
ImportException at Spotfire.Dxp.Data:
Failed to execute data source query for data source "Information link". (HRESULT: 80131500)

Stack Trace:
   at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
   at Spotfire.Dxp.Data.DataFlow.Execute()
   at Spotfire.Dxp.Data.DataFlow.DataFlowConnection.ExecuteQueryCore2()
   at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.<>c__DisplayClass72_1.<CreateView>b__0()
   at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, ProgressOperation operation)
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.CreateView(CxxSession session, DataPropertyRegistry propertyRegistry, GlobalMethodRegistry globalMethodRegistry, DataSourceConnection connection, PartialDataLoadReport& partialLoadReport)
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.GetColumnsAndProperties(DataSourceConnection connection)
   at Spotfire.Dxp.Data.Persistence.DataItem.PerformUpdate(SourceColumnProducer producer, DataSourceConnection connection)
   at Spotfire.Dxp.Data.Persistence.DataItem.Update(SourceColumnProducer producer, DataSourceConnection connection)
   at Spotfire.Dxp.Data.Persistence.DataPool.<LoadData>d__15.MoveNext()
   at Spotfire.Dxp.Data.Producers.SourceColumnProducer.OnConfigure()
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.DocumentModel.Node.<>c.<ConfigureSubTree>b__47_0(Node node)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableListAvlLeaf`1.ForEachChild(Action`1 action, Boolean includeFrozen)
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.DocumentModel.Node.<>c.<ConfigureSubTree>b__47_0(Node node)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableList`1.ForEachChild(Action`1 action, Boolean includeFrozen)
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.DocumentModel.Node.<>c.<ConfigureSubTree>b__47_0(Node node)
   at Spotfire.Dxp.Framework.DocumentModel.State.NodeState.<>c__DisplayClass91_0.<ForEachManagedChild>b__0(IDocumentNodeChild documentNodeChild)
   at Spotfire.Dxp.Framework.DocumentModel.State.NodeState.ForEachChild(IDocumentNodeChild[] children, Action`1 action)
   at Spotfire.Dxp.Framework.DocumentModel.Node.ConfigureSubTree()
   at Spotfire.Dxp.Framework.ApplicationModel.Progress.ExecuteSubtask(String title, IndeterminateProgressFormatter progressFormatter, ProgressOperation operation)
   at Spotfire.Dxp.Framework.DocumentModel.DocumentNode.ConfigureAndAttachFromNew()
   at Spotfire.Dxp.Framework.DocumentModel.DocumentNode.AttachSubTreeWhileExecuting(UndoableNodeBase newOwner)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableNode.Spotfire.Dxp.Framework.DocumentModel.IUndoableNode.AttachItemToUndoableNode(Object item)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableKeyedCollection`2.<>c__DisplayClass43_0.<Insert>b__0()
   at Spotfire.Dxp.Framework.DocumentModel.Node.InternalTransaction(Executor executor, Boolean rollbackNestedInternalTransactionAtException)
   at Spotfire.Dxp.Framework.DocumentModel.UndoableKeyedCollection`2.Insert(Int32 index, TNode item)
   at Spotfire.Dxp.Data.DataTableCollection.<>c__DisplayClass68_0.<Add>b__0()
   at Spotfire.Dxp.Framework.DocumentModel.Node.InternalTransaction(Executor executor, Boolean rollbackNestedInternalTransactionAtException)
   at Spotfire.Dxp.Data.DataTableCollection.Add(DataTable dataTable)
   at Spotfire.Dxp.Application.PartiallyOpenedDataSource.<LoadData>d__9.MoveNext()
   at Spotfire.Dxp.Application.AnalysisApplication.<OpenPartiallyOpenedDocument>d__110.MoveNext()
   at Spotfire.Dxp.Application.AnalysisApplication.<OpenDataSource>d__99.MoveNext()
   at Spotfire.Dxp.Application.AnalysisApplication.ConsumeDataLoadPromptRequests(IEnumerable`1 prompts)
   at Spotfire.Dxp.Application.AnalysisApplication.Open(DataSource source, DocumentOpenSettings settings)
   at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.OpenDataSourceWithoutPrompting(DataSource dataSource, DocumentOpenSettings documentOpenSettings, IServiceProvider serviceProvider)
   at Spotfire.Dxp.Forms.Data.Import.DataSourceFactoryService.OpenDataSource(InformationLinkDataSource dataSource, DocumentOpenSettings documentOpenSettings, IServiceProvider serviceProvider)
   at Spotfire.Dxp.Forms.Application.FormsProgressService.ProgressThread.DoOperationLoop()

InformationModelException at Spotfire.Dxp.Data:
Marshalling Error: INVALID_CHARACTER_ERR: An invalid or illegal XML character is specified.  (HRESULT: 80131500)

Stack Trace:
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.GetNextBlock()
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at Spotfire.Dxp.Internal.Utilities.SeekableStream.Read(Byte[] buffer, Int32 offset, Int32 count)
   at System.IO.BinaryReader.FillBuffer(Int32 numBytes)
   at System.IO.BinaryReader.ReadUInt16()
   at Spotfire.Dxp.Data.Formats.Sbdf.SbdfSectionHeader.ReadMagicNumber(BinaryReader reader)
   at Spotfire.Dxp.Data.Formats.Sbdf.SbdfSectionHeader.ReadSectionType(BinaryReader reader, SbdfSectionTypeId expectedType)
   at Spotfire.Dxp.Data.Import.SbdfDataRowReader.DelayedInit()
   at Spotfire.Dxp.Data.Import.InformationLinkDataSourceConnection.ExecuteQueryCore2()
   at Spotfire.Dxp.Data.DataSourceConnection.ExecuteQuery2()

InformationModelServiceException at Spotfire.Dxp.Services:
Marshalling Error: INVALID_CHARACTER_ERR: An invalid or illegal XML character is specified.  (HRESULT: 80131509)

Stack Trace:
   at Spotfire.Dxp.Services.WebServiceBase`1.InvokeService[T](ServiceMethod`1 serviceMethod, ExceptionFactoryMethod exceptionFactoryMethod)
   at Spotfire.Dxp.Data.InformationModel.InternalInformationModelManager.DataStream.GetNextBlock()

SoapHeaderException at System.Web.Services:
Marshalling Error: INVALID_CHARACTER_ERR: An invalid or illegal XML character is specified.  (HRESULT: 80131501)

Stack Trace:
   at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at Spotfire.Dxp.WebServices.QueryManagerService.getDataBlock(String jobID, Int32 timeout)
   at Spotfire.Dxp.Services.WebServiceBase`1.InvokeService[T](ServiceMethod`1 serviceMethod, ExceptionFactoryMethod exceptionFactoryMethod)

Quotes (like " ") are appended to the schema, table and column names by default in the Information link query. In Google BigQuery, having quotes '' " in the schema, table and column names in the SQL query is not supported. To resolve, please follow either of the options:

Option 1) Edit the default SQL of the Information Link by removing the quotes to the schema, table and column names. See Editing the SQL of an Information Link for more details. For example, if the default Information Link SQL is:

SELECT
  B1."ASSISTS" AS "ASSISTS",
  B1."NAME" AS "NAME",
  B1."POSITION" AS "POSITION"
FROM
  "spotfire_demodata"."dbo"."BASEBALL" B1
WHERE
  <conditions>

It should be modified to:

SELECT
  B1.ASSISTS AS ASSISTS,
  B1.NAME AS NAME,
  B1.POSITION AS POSITION
FROM
   spotfire_demodata.dbo.BASEBALL B1
WHERE
  <conditions>

Option 2) Add the following lines before </jdbc-type-settings> in the default template available for Google BigQuery from the TIBCO Spotfire® JDBC Data Access Connectivity Details wiki page:

<column-name-pattern>$$name$$</column-name-pattern>
<table-name-pattern>$$name$$</table-name-pattern>
<schema-name-pattern>$$name$$</schema-name-pattern>
<catalog-name-pattern>$$name$$</catalog-name-pattern>
<procedure-name-pattern>$$name$$</procedure-name-pattern>
<column-alias-pattern>$$name$$</column-alias-pattern>

Wiki: TIBCO Spotfire® JDBC Data Access Connectivity Details > Google BigQuery template: 

• https://community.tibco.com/wiki/tibco-spotfire-jdbc-data-access-connectivity-details

Doc: Editing the SQL of an Information Link

• https://docs.tibco.com/pub/sfire-analyst/10.3.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/id/id_editing_the_sql_of_an_information_link.htm