Print Complete XML Data Element Values in RTF for troubleshooting

Use the below code inside tag, this is used to print all element values in xml,

<xsl:value-of select=".">


to print xml, you need to place below tag at first line in rtf

<?xdo-debug-level:'STATEMENT'?>

Oracle Fusion Tables

Inventory:

Items: EGP_SYSTEM_ITEMS_B

ITEM_CODE, ITEM_NAME, INVENTORY_ITEM_ID 

ITEM CATEGORY: EGP_ITEM_CATEGORIES

UNIT_OF_MEASURE: INV_UNITS_OF_MEASURE_B

xsl, xslt : Using in BI Publisher

Sample Data:

<?xml version = '1.0' encoding = 'utf-8'?>
<G_MAIN>
<PAYMENT_ID>45003</PAYMENT_ID><PAYMENT_AMOUNT>2221000</PAYMENT_AMOUNT><PAYMENT_CURRENCY_CODE>USD</PAYMENT_CURRENCY_CODE>
<G_INV>
<CHECK_NUMBER>1</CHECK_NUMBER><INVOICE_ID>17007</INVOICE_ID><INVOICE_NUM>8888</INVOICE_NUM>
</G_INV>
<G_INV>
<CHECK_NUMBER>1</CHECK_NUMBER><INVOICE_ID>17008</INVOICE_ID><INVOICE_NUM>9999</INVOICE_NUM>
</G_INV>
<G_INV>
<CHECK_NUMBER>1</CHECK_NUMBER><INVOICE_ID>17009</INVOICE_ID><INVOICE_NUM>1111</INVOICE_NUM>
</G_INV>
</G_MAIN>
</DATA_DS>


Always use xsl syntax in rtf tags, writing directly in rtf will not work.

xsl:value-of

<xsl:value-of select="//G_MAIN/G_INV/INVOICE_NUM"/>

It will search for node //G_MAIN/G_INV/ in xml, and will print all occurrences of INVOICE_NUM

<xsl: if>

XSL_IF

XSL Tags	Output
<xsl:if test="count(//G_MAIN/G_INV)>1"> true statement </xsl:if> <xsl:if test="count(//G_MAIN/G_INV) > 10"> <?count(//G_MAIN/G_INV)?> </xsl:if>	true statement 20

<xsl:choose>

XSL_CHOOSE

XSL Tags	Output
<xsl:choose> <xsl:when test="count(//G_MAIN/G_INV)>10"> COUNT IS MORE THAN 10 </xsl:when> <xsl:otherwise> COUNT IS LESS THAN 10 </xsl:otherwise> </xsl:choose	COUNT IS MORE THAN 10

 <xsl:text> 
<xsl:text>text to be displayed</xsl:text>


Define Variables

<xsl:variable name="x"/> --BLANK VAR

<xsl:variable name="favouriteColour" select="'blue'"/>  --STATIC VAL VARIABLE

<xsl:variable name="favouriteColour" select="blue"/>  -- FETCH <blue> element into var

Define a Variable

<xsl:variable name="no_of_lines_per_page" select="number(15)"/>

<?$ no_of_lines_per_page?>

Define a variable to contain Data Nodes

Define a group on the fly. With context

<xsl:variable xdofo:ctx="incontext" name="INV_GRP" select=".//G_INV"/>

<?for-each:$INV_GRP?>

<?position()?> <?INVOICE_NUM?>

<?end for-each?>

Define and Calling a Template

Define Template	<xsl:template name="calcuateArea"> <xsl:param name="width"/> <xsl:param name="height"/> <xsl:value-of select="$width * $height"/> </xsl:template>
Call Template	<xsl:call-template name="calcuateArea"> <xsl:with-param name="width" select="10"/> <xsl:with-param name="height" select="50"/> </xsl:call-template>

Learn XSLT

https://docstore.mik.ua/orelly/xml/xslt/index.htm

Harward Playlist:

https://www.youtube.com/playlist?list=PLPdA9zLPgLvGVDB1OntCCVjnNltud4XUg

Report with Variable Columns Based on XML Data

Constructing Dynamic Data Columns

Renders a table when the number of columns that is required by the data is variable. 

 <TestScoreTable>
  <TestScores>
    <TestCategory>Mathematics</TestCategory>
    <TestScore width ="15">
    <TestScoreRange>0-20</TestScoreRange>
    <NumofStudents>30</NumofStudents>

• Use the following tags to accommodate the dynamic formatting required to render the data correctly:
• Dynamic Column Header
  <?split-column-header:group element name?>  TestScores
  Use this tag to define which group to split for the column headers of a table.
• Dynamic Column <?split-column-data:group element name?>
  Use this tag to define which group to split for the column data of a table.
• Dynamic Column Width
  <?split-column-width:name?> or
  <?split-column-width:@width?>
  Use one of these tags to define the width of the column when the width is described in the XML data. The width can be described in two ways:
  • An XML element stores the value of the width. In this case, use the syntax <?split-column-width:name?>, where name is the XML element tag name that contains the value for the width.
  • If the element defined in the split-column-header tag, contains a width attribute, use the syntax <?split-column-width:@width?> to use the value of that attribute.
• Dynamic Column Width's unit value (in points) <?split-column-width-unit:value?>
  Use this tag to define a multiplier for the column width. If the column widths are defined in character cells, then you must use the appropriate multiplier value to render the columns to the correct width in points. For example, if you are using 10 point courier font in the table, you would use a multiplier of 6, which is the approximate width of a character displayed in 10 point courier font. If the multiplier is not defined, then the widths of the columns are calculated as a percentage of the total width of the table. The column width calculations are illustrated in the following table:

  Width Definition	Column 1 (Width = 10)	Column 2 (Width = 12)	Column 3 (Width = 14)
  Multiplier not present -% width	10/10+12+14*100 28%	%Width = 33%	%Width =39%
  Multiplier = 6 - width	60 pts	72 pts	84 pts

Defining Columns to Repeat Across Pages

If the table columns expand horizontally across more than one page, you can define how many row heading columns you want to repeat on every page.

Use the following syntax to specify the number of columns to repeat:

<?horizontal-break-table:number?>

where number is the number of columns (starting from the left) to repeat.

Note:

This functionality is supported for PDF output only.

Example of Dynamic Data Columns

A template is required to display test score ranges for school exams. Logically, you want the report to be arranged as shown in the table here.

Test Score	Test Score Range 1	Test Score Range 2	Test Score Range 3	...Test Score Range n
Test Category	# students in Range 1	# students in Range 2	# students in Range 3	# of students in Range n

However, you do not know how many Test Score Ranges are reported. The number of Test Score Range columns is dynamic, depending on the data.

The following XML data describes these test scores. The number of occurrences of the element <TestScoreRange> determine how many columns are required. In this case there are five columns: 0-20, 21-40, 41-60, 61-80, and 81-100. For each column there is an amount element (<NumOfStudents>) and a column width attribute (<TestScore width="15">).

<?xml version="1.0" encoding="utf-8"?>
 <TestScoreTable>
  <TestScores>
    <TestCategory>Mathematics</TestCategory>
    <TestScore width ="15">
    <TestScoreRange>0-20</TestScoreRange>
    <NumofStudents>30</NumofStudents>
 </TestScore>
    <TestScore width ="20">
    <TestScoreRange>21-40</TestScoreRange>
    <NumofStudents>45</NumofStudents>
 </TestScore>
   <TestScore width ="15">
    <TestScoreRange>41-60</TestScoreRange>
    <NumofStudents>50</NumofStudents>
 </TestScore>
    <TestScore width ="20">
    <TestScoreRange>61-80</TestScoreRange>
    <NumofStudents>102</NumofStudents>
 </TestScore> 
    <TestScore width ="15"> 
    <TestScoreRange>81-100</TestScoreRange>
   <NumofStudents>22</NumofStudents>
 </TestScore>
 </TestScores> 
  <TestScoreTable> 

Using the dynamic column tags in form fields, set up the table in two columns as shown in the following figure. The first column, Test Score is static. The second column, Column Header and Splittingis the dynamic column. At runtime this column is split according to the data, and the header for each column is appropriately populated. The Default Text entry and Form Field Help entry for each field are listed in the following table. (See Using the Form Field Method for more information on using form fields).

Description of the illustration GUID-7A10AA37-5BEA-4E85-9627-1610344F5A05-default.gif

Default Text Entry	Form Field Help Text Entry
Group:TestScores	<?for-each:TestScores?>
Test Category	<?TestCategory?>
Column Header and Splitting	<?split-column-header:TestScore?> <?split-column-width:@width?> <?TestScoreRange?>%
Content and Splitting	<?split-column-data:TestScore?> <?NumofStudents?>
end:TestScores	<?end for-each?>

• Test Score is the boilerplate column heading.
• Test Category is the placeholder for the <TestCategory> data element, that is, Mathematics, which is also the row heading.
• The second column is the one to be split dynamically. The width you specify is divided by the number of columns of data. In this case, there are 5 data columns.
• The second column contains the dynamic range data. The width of the column is divided according to the split column width. Because this example does not contain the unit value tag (<?split-column-width-unit:value?>), the column is split on a percentage basis. Wrapping of the data occurs if required.
  If the tag (<?split-column-width-unit:value?>) were present, then the columns have a specific width in points. If the total column widths were wider than the allotted space on the page, then the table breaks onto another page.
  The horizontal-break-table tag could then be used to specify how many columns to repeat on the subsequent page. For example, a value of 1 would repeat the column Test Score on the subsequent page, with the continuation of the columns that did not fit on the first page.

The template renders the output that is shown in the following figure:

Using XPATH in RTF: count, starts-with, name functions

https://docs.oracle.com/cd/E80149_01/bip/BIPRD/GUID-4D9FC5FC-5A72-4CE6-919C-A3E0D6E4900C.htm#BIPRD2597

 XPath contains a number of functions on node sets, numbers, and strings; here are a few of them:

  count(elem) counts the number of selected elements

  Example: //section[count(student)=1] selects section with exactly two students children

  name() returns the name of the element

  Example: //*[name()='student'] is the same as //student

  starts-with(arg1, arg2) tests if arg1 starts with arg2

  Example: //*[starts-with(name(), 'stu']

  contains(arg1, arg2) tests if arg1 contains arg2

  Example: //*[contains(name(), 'tud']

Type		Output
Count elements (Students) in XML		<?count(STUDENT)?>
Name of the Element if exists		<?//*[name()=’ROLL_NO’]?>
Starts-with		<?//*[starts-with(name(), 'ROLL_NO')]?>

OUTPUT IS RETURNED AS BELOW:

Type		Output
Count elements (Students) in XML		3
Name of the Element if exists		1201
Starts-with		1201

check earlier post for xml, save above in rtf file, and output will be shown as shown above

Using XPATH in RTF Templates

Example: [http://www.adivaconsulting.com/adiva-blog/item/38-working-with-xpath-in-rtf-template.html]

Here is the example of an institute which has one department and a few students in it.

<INSTITUTE>

<DEPARTMENT>

     <DEPARTMENT_NAME>COMPUTER SCIENCE ENGINEERING</DEPARTMENT_NAME>

          <SECTION>

                <STUDENT>

                     <ROLL_NO>1201</ROLL_NO>

                      <MARKS>

                              <SUBJECT1>45</SUBJECT1>

                              <SUBJECT2>55</SUBJECT2>

                              <SUBJECT3>65</SUBJECT3>

                      </MARKS>

                </STUDENT>

                 <STUDENT>

                     <ROLL_NO>1205</ROLL_NO>

                      <MARKS>

                              <SUBJECT1>56</SUBJECT1>

                              <SUBJECT2>48</SUBJECT2>

                              <SUBJECT3>58</SUBJECT3>

                      </MARKS>

                </STUDENT>

                <STUDENT>

                     <ROLL_NO>1257</ROLL_NO>

                      <MARKS>

                              <SUBJECT1>51</SUBJECT1>

                              <SUBJECT2>49</SUBJECT2>

                              <SUBJECT3>53</SUBJECT3>

                      </MARKS>

                </STUDENT>

          </SECTION>

     </DEPARTMENT>

</INSTITUTE>

Now let’s display all the student details using a table with department name, roll number and marks.

Output:

Why is the department name missing?

When we say <?for-each:STUDENT?> the control is at <STUDENT> , (as you can see in the below diagram) but department name is not the child of student so we have to move the cursor to appropriate tag using xpath.

Output:

Here we used <?../../DEPARTMENT_NAME?> instead of <?DEPARTMENT_NAME?> so that the control jumps to two levels up in hierarchy and displays the DEPARTMENT NAME.

Now, let’s calculate totals marks secured by each student. To do so, add a column for Total and use <?sum(./MARKS/*)?> syntax to calculate TOTAL.

<?sum(./MARKS/*)?> syntax implies that we are using sum function and calculating all the children in marks

Output: