Consistent Id of XML output of SQL executed on Data Access Point

Question

I run an application on Exact Oline for eco taxes, using XML output with XSL transformation to generate the HTML forms.

A query like:

select * from me

generates:

<?xml version="1.0" encoding="utf-16"?>
<InvantiveDAPOutput xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <!--ecotaksen.be Invantive Data Access Point (Unofficial)-->
  <!--License 'L740757780' registered to Control INFO B.V..-->
  <ResultSets>
    <ResultSet Id="0">
      <Fields>
        <Field>
          <Id>0</Id>
          <Name>CurrentDivision</Name>
          <DatabaseDataType>int32</DatabaseDataTyp...

In my XSL I then use for instance:

<xsl:value-of select="//InvantiveDAPOutput/ResultSets/ResultSet[@Id='4']/Rows/Row/entity_name_singular" />

But when I insert a query somewhere in the process, the Id all change. 1 remains 1 for instance, but the numbers after the new inserted query increase by 1.

Is there a smarter way to make the XSL independent of the number of queries and their order?

Answer 1

Just add the desired name of the result set to your query as a hint:

select /*+ result_set_name('myname') */ * from me

The XML will become like:

<ResultSet Id="0" Name="myname">

You can find the full syntax for execution hints in the documentation. This holds for Exact Online as well as other platforms such as Teamleader or salesforce.