midrange.com code scratchpad
Name:
Sample XMLi Template using nested SQL and parameter markers
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
01/19/2011 12:20:14 am
IP:
Logged
Description:
This template uses 2 SQL queries to build the XML, it uses the results of the outer query as parameter markers on the inner query.
Code:
  1. <xmli:template xmlns:xmli="http://www.sourceforge.net/xmli" sql-naming="SQL" format="tabbed">
  2.  
  3.     <!-- This example runs a SQL statement and builds XML from the results  -->
  4.     <!-- The first template parameter is a customer number and will be used in the SQL  -->
  5.     <!-- The xmli:with-parm element specifies the value to be used by SQL in place of the ?  -->
  6.     <!-- The order in which the xmli:with-parm elements are written should match the order of ? in the SQL statement -->
  7.     
  8.     <root>
  9.     
  10.         <!-- Run SQL to get a list of Orders by order number (sql-naming is set to SQL) -->
  11.         <xmli:run-sql name="orders" statement="select ordordno, orddat, ordtot, ordcus
  12.                                                from xmlilib.xmliorders
  13.                                                where ordcus = ?
  14.                                                order by ordordno">
  15.             <xmli:with-parm select="{$parm[1]}" />
  16.         </xmli:run-sql>
  17.     
  18.         <!-- For each row found, write the XML -->
  19.         <!-- Note: Here only orders for CUSTOMER1 (in parm[1]) will be written -->
  20.         <!-- That is, only ORDER1 and ORDER3 should be included in the XML -->
  21.         <xmli:for-each>
  22.             <order number="{$orders.1}">
  23.                 <details>
  24.                     <OrderDate><xmli:value-of select="orders.2" /></OrderDate>
  25.                     <OrderTotal>$<xmli:value-of select="orders.3" /></OrderTotal>
  26.                     <OrderCustomer><xmli:value-of select="orders.4" /></OrderCustomer>
  27.                 </details>            
  28.             
  29.             
  30.                 <!-- Run SQL to get a list of lines for each order retrieved -->
  31.                 <!-- Note: the current value of $orders.1 is used to run this SQL statement -->
  32.                 <xmli:run-sql name="lines" statement="select linlinno, linitem, linqty, lintot
  33.                                                        from xmlilib.xmlilines
  34.                                                        where linordno = ?
  35.                                                        order by linlinno">
  36.                     <xmli:with-parm select="{$orders.1}" />
  37.                 </xmli:run-sql>
  38.                 
  39.                 <xmli:for-each>
  40.                     <line number="{$lines.1}">
  41.                         <LineItem><xmli:value-of select="lines.2" /></LineItem>
  42.                         <LineQty><xmli:value-of select="lines.3" /></LineQty>
  43.                         <LineTotal>$<xmli:value-of select="lines.4" /></LineTotal>
  44.                     </line>
  45.                 </xmli:for-each>
  46.         
  47.             </order>
  48.         </xmli:for-each>
  49.     </root>
  50.     
  51.     <!-- The result is written to the IFS in the path specified (the path could be a parameter) -->
  52.     <xmli:write-to-file path="'/XMLi/Examples/Results/Example15.xml'" />
  53. </xmli:template>
  54.  
  55.  
© 2004-2019 by midrange.com generated in 0.006s valid xhtml & css