Structure2xml

Introduction

structure2xml is a plugin for simply getting content out of a Database without having to write complicated SQL queries. The only thing you have to do is editing an XML file.

structure2xml uses the db2xml class (aka sql2xml) and is quite an old project (it was also used in the old Bitflux CMS)

Collection Config

To use the structure2xml plugin, you have to put it into the CollectionConfig in .configxml (or .configxml.children) in the appropriate collection in the data/ directory. For example:

<plugins>
        <extension type="html"/>
        <parameter type="pipeline" name="xslt" value="news.xsl"/>
        <plugin type="structure2xml">
            <parameter name="src" value="structure/news.xml"/>
            <parameter name="structure2xml[lang]" value="bx://lang"/>
        </plugin>
    </plugins>

Parameters:

src: The path to the structure XML file. It's relative to BX_PROJECT_DIR and we usually put it into the structure/ directory, but you can put it where ever you want.

structure2xml[value]: You can pass variables to the structure file. See below for more about that.

Hint:

tableprefix: If you want to access a table that has another tableprefix add this parameter:
<parameter name="tableprefix" value="foo_"/>

Structure XML

For a simple one-table query, the structure XML file is quite short:

<bxst:structure xmlns:bxst="http://bitflux.org/structure/1.0">
  <bxst:section name="herecomethenews" >
    <bxst:table name="news" fields="id , title , link ,  content_encoded ,  description"/>
  </bxst:section>
</bxst:structure>

This would return all news rows from the table news with the fields defined in fields.

<bx>

  <plugin name="structure2xml">
    <herecomethenews>
      <news id="news4">
      <id>4</id>
      <title>Parsing non-well-formed XML documents in PHP 5.1</title>
      <link>parsing_non_well_formed_xml</link>
   ...

If you leave out the fields attribute, all fields are returned.

Definining fields

To avoid any problems, put the unique fields at the beginning of your fields definitons.

It is also important to note, that fieldnames have to be seperated by " , " eg. "[space][comma][space]" (you can have as many spaces as you want, but at least one).
The engine seperates them this way... The reason for this is, that you also can use sql-functions here, so for example

<bxst:table name="news"
fields="id , date_format(dc_date,'%d/%m/%y') as dc_date" />

would work and there's a comma, too. Of course, you shouldn't seperate arguments-seperating commas with "[space][comma][space]", but only with "[comma]" (or ",[space]", resp. "[space],")

The id field

You need an field called id, otherwise it won't return anything. Theoretically it's possible to define another field as id-field, but that's not exposed to the outside currently. Could be added later, but one little trick to have a field called "id" is to rename an existing field to id within the sql query:

<bxst:table name="mytable" fields="myidfield as id , and , more , fields"/>

bxst:section

You can also have more than one section for making more than one query and the name of the section can be chosen freely. The name of the section is the "root"-element for that resultset.

Attributes for bxst:section

To allow adding where et al. conditions to the generated SQL query, you can use the following, hopefully selfexplanatory attribute to <bxst:section>

where
orderby
groupby
limit

Example:

<bxst:section name="herecomethenews" where="id > 20" orderby="dc_date" limit="10">

Variable `where` data

As mentioned above, it's possible to pass variables to structure2xml with the structure2xml[variable] parameter in .configxml.

This variable then can be used in the where attribute in bxst:section. Assuming we have the following entry in .configxml

<plugin type="structure2xml">

  <parameter name="src" value="structure/news.xml"/>
  <parameter name="structure2xml[id]" value="phpglobals://_GET[id]"/>

</plugin>

and bxst:section in structure/news.xml

<bxst:section name="herecomethenews" where="id = $id">

would replace $id by the GET Parameter "id".

You can use any scheme, which you can use in popoon for the attribute value in .configxml, for some special schemes, see also CollectionConfig#Special_Schemes

Conditional variable `where` data

On top of the above explained possibility to use variable data, you can also make them conditional, depending on if they really exist or not. Example

<bxst:section name="herecomethenews" where="[[id = $id]]">

This would remove the whole id = $id part, if no GET[id] was provided. This way, you can use the same structure XML for showing all data (when no GET[id]) or specific ones (with a provided GET[id]).
You can also have more than conditional where entries. From the demo:

<bxst:section name="news" where="[[lang = '$lang']] [[and link = '$link']] " >

depending on if $lang and/or $link are filled with data, they are removed or not.

Joining more than one table

The main reason, why this whole structure2xml stuff was written, was to provide an easy way to join different tables without having to write page-long sql queries...

A simple example:

<bxst:section name="projekte" >

  <bxst:table name="projekte" fields="id ,  text , uri , titel">
    <bxst:table name="projekte_images" thisfield="uri" thatfield="uri" />
  </bxst:table>

</bxst:section>

In this query, we join the tables projekte and projekte_images with the fields projekte.uri and projekte_images.uri.

thisfield is the joining field of projekte_images, thatfield the joining field of projekte, if you don't provide them, it takes id by default.

The output of the XML is nicely nested, so you can easily query them with XSLT.

You can also join more than 2 tables. Just follow the pattern described above.

Editing database data

See OldSchoolForms for that.

You can also use DBForms2 for editing database contents. In many ways DBForms2 ist actually more powerful.

Output for getChildren

for example for the navitree plugin...

<bxst:section name="projekte" inChildren=" concat(uri,'.','$lang') as uri, text_$lang as title"
orderby="text_de">

will return all projects with uri.$lang as uri and text_$lang as title. Display-Order is set according to orderby. $lang is substituted like other variables with for example:

in .configxml

If you don't need the output of this in the regular plugin output, you can add noOutput="true" to the bxst:section element.

sql2xml Plugin

Besides the structure2xml plugin, there's also a sql2xml plugin. It just takes one sql query as parameter, executes it and returns the result as XML with the help of the db2xml class. It's useful for complicated SQL queries which you can't map to the structure2xml format.