mip’s blog

Ok, last time i’ve shown the first really basic functions of Xtirer reading data from MYSQL and generating an XML output. So this post is going more into detail showing more possibilities.

First of all here the definition of the xml-object / xml-attribute. Both objects are defined equally, besides the output being different.

<xml_object|xml_attribute
 name="outputname"
 [[distinct="true|false"
 source="Xtirer_table_reference"
 value="STATIC_VALUE"
 valueof="XTIRER_COLUMN_REFERENCE"
 limit="MAX_OF_ROWS"
 offset="ROW_OFFSET"
 order="ASC|DESC|ASCENDING|DESCENDING"]]>

You see every attribute except the name attribute is optional. Now let’s explain each attribute and its effect in terms of SQL-Syntax and XML Output. The relevant part is highlighted in red.

• name: defines the name of the output object – tag or attribute name
  Xtirer: <xml_object name="myxml"/>
SQL: - (nothing)
Output: <myxml/>
• value: defines a static value to be the content of the xml-object or value of the xml-attribute
  Xtirer: <xml_object name="myxml" value="somevalue"/>
        <xml_attribute name="myvalue" value="someother"/>
SQL: - (nothing)
Output: <myxml><![CDATA[somevalue]]></myxml>
        <... myvalue="someother" .. 
• valueof: (* assuming some parent defined the TABLE being “animals”)
  defines the database table column from where the content | value of the object | attribute shall come.
  Xtirer: <xml_object name="myxml" valueof="COLUMN.owner"/>
        <xml_attribute name="myattr" valueof="COLUMN.owner"/>
SQL: SELECT owner FROM animals;
Output: <myxml><![CDATA[database value of column owner]]></myxml>
        <... myattr="database value" ..
• source: defines the database table where the data shall come from. Objects inside inherit the source, so pls. only define once.
  Xtirer: <xml_object name="myxml" source="TABLE.animals" valueof="COLUMN.birth"/>
SQL: SELECT birth FROM animals;
Output: <myxml><![CDATA[database value]]></myxml>
• distinct: if only distinct values of one column are required set distinct true.
  Xtirer: <xml_object name="myxml" valueof="COLUMN.owner" distinct="true"/>
SQL: SELECT distinct owner FROM animals;
Output: <myxml><![CDATA[database value]]></myxml>
• order: if results shall be ordered ascending or descending indicate the ordering column with “order” and the direction. Possible values are ASC,DESC,ASCENDING,DESCENDING in upper and lower cases.
  Xtirer: <xml_object name="myxml" valueof="COLUMN.owner" order="ASC"/>
SQL: SELECT owner FROM animals ORDER BY owner ASC;
Output: <myxml><![CDATA[database value]]></myxml>
• limit: if you like to limit the number of result objects use the limit attribute
  Xtirer: <xml_object name="myxml" valueof="COLUMN.owner" LIMIT="5"/>
SQL: SELECT owner FROM animals LIMIT 5;
Output: <myxml><![CDATA[database value]]></myxml>
• offset: if you would like to skip some of the results, e.g. get the 11-20 position of the charts use the offset value. If no limit is defined the offset is ignored.
  Xtirer: <xml_object name="musictitle" value="COLUMN.musictitle" LIMIT="10" OFFSET="10"/>
SQL: SELECT musictitle FROM charts LIMIT 10,10;
Output: <musictitle><![CDATA[database value]]></musictitle> 

lol, getting a bit cody here, but don’t be scared away. All these possibilities are really handy and the usage is very straight forward.

Ok, the final thing to understand the Xtirer xml_object | xml_attribute tags is “source aka sql nesting”. Let’s have a look at the next Xtirer Prototype file:

 1: <animallist>
 2: <xml_object name="pat" source="TABLE.animals">
 3:   <xml_object name="name" valueof="COLUMN.name"/>
 4:   <xml_attribute name="species" valueof="COLUMN.species"/>
 5:
 6:   <xml_object name="funky" source="TABLE.funkystuff">
 7:     <xml_attribute name="number" valueof="COLUMN.number"/>
 8:   </xml_object>
 9:
10:   <xml_attribute name="birth" valueof="COLUMN.birth"/>
11:  </xml_object>
12: </animallist>

This is a reduced version of the animals example in the introduction to Xtirer database reading. I’ve just added an xml_object named funky and its attribute.
What will happen?

Xtirer will scan through the XML-Prototype file and do the following:
1. Make a basic xml tag named “animallist”
2. Make an SQL Query “SELECT name,species,birth FROM animals”
3. Replicate the xml_object as many times as there are resulting rows in the DB.
4. For every result row execute the SQL Query “SELECT number FROM funkystuff”
5. Replicate the xml_object funky for every query and every result row

So the output will be:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<animallist>
<pat species="dog" birth="2008-07-11">
<name><![CDATA[chaco]]></name>
<funky number="1"/>
<funky number="2"/>
<funky number="34"/>
<funky number="500"/>
</pat>
<pat species="cat" birth="2007-07-20">
<name><![CDATA[lurch]]></name>
<funky number="1"/>
<funky number="2"/>
<funky number="34"/>
<funky number="500"/>
</pat>
<pat species="ape" birth="1997-07-04">
<name><![CDATA[chimpike]]></name>
<funky number="1"/>
<funky number="2"/>
<funky number="34"/>
<funky number="500"/>
</pat>
</animallist>

Things to note:
1. all objects in line 3,4 and 10 inherit the source defined in line 2.
2. xml-attribute in line 7 takes source from line 6
3. Query defined with tags of line 6+7 are executed as often as results are found for the other SQL query

Ok, i stop here for now. Next time i’ll introduce the selector tag to select specific rows in a table.

Feel free to send me your feedback on Xtirer and check back soon!