qbo3 Search Panel override with XmlData

Question

The QBO Deal modules includes a DealLoan panel that includes rendering user-defined fields. I wish to export the DealLoan data to Excel using a custom Excel template, but I don't know how to map the user-definded fields in the DealLoan.XmlData using QBO's Excel overrides. Is this even possible?

Answer 1

You can use wildcard field mapping in Excel to render XmlData.

There key configuration components to enable this are:

• The Excel template must include a range with a column like XmlData_Root_*
• Ensure XmlStreamReader:PrefixNestedNames = true is in configuration
• The DealLoan panel must use either SearchXml or SmartSearch to render data (this is the default configuration)

Excel Template

If you include a column name with asterisk(s), the qbo4.Document.Aspose.Bridge.CellsHandler will insert a new column for each field that matches the expression. For example, given the following data:

<DealLoanCollection>
  <DealLoanItem>
    <DealLoanID>12345</DealLoanID>
    <DealLoan>1674063630</DealLoan>
    <XmlData>
      <Root>
        <Foo>Bar</Foo>
        <Animal>Road Runner</Animal>
      </Root>
    </XmlData>
  </DealLoanItem>
</DealLoanCollection>

{
    "XmlStreamReader": {
        "PrefixNestedNames": true
    }
}

The following Excel range:

DealLoanID	XmlData_Root_*	DealLoan

would render the following:

DealLoanID	Foo	Animal	DealLoan
12345	Bar	Road Runner	9999063630

Note that the "prefix" of the wildcard is, by default, removed from the resulting column name. For example:

Wildcard	Resulting Columns	Comments
Xml*	_Data_Foo, _Data_Animal	Trailing * will omit everything before the `*.
Xml_*	Data_Foo, Data_Animal
*Loan	Deal	Leading * will omit everything after the *.
Deal*	LoanID, Loan

Caution: wildcard columns will trigger a column insert, which applies to an entire worksheet. If you have data above or below your target range, the column insert may cause unwanted side effects.

XmlStreamReaderOptions

QBO uses an IDataReaderService paired with an XmlStreamReader for this use case. The XmlStreamReader has options to control how nested XML fragments are rendered in a data reader, including:

Property	Default	Description
PrefixNestedNames	false	If true, each ancestor node name will be included in the IDataReader field name.
PrefixDelimiter	_	Determines what ancestor nodes are concatenated with.

These configuration changes can be set via any IConfiguration source your QBO environment is configured to read, including ConfigurationEntry or AWS Secrets.

Warning

Modifying the XmlSreamReader will impact the default behavior of all XmlStreamReaders created by the IDataReaderService, including imports. If you wish your imports to behave differently than the default behavior, you must extend your ImportFileTemplate.Parameters to include PrefixNestedNames=true or PrefixNestedNames=false. If the source data is not an XmlStreamReader, this setting will have no impact. If the source data is an XmlStreamReader, this setting will dictate the desired behavior.