Re: xql help!
Arnold Daniels
You're forgiven :p. I spend a weekend doing other stuff than hanging
behind the computer for a change, so therefor the reply is somewhat
late. Anyway, best this is to ask this on the on our mailing list
mysql-udf-...@googlegroups.com.
The sourceforge project is closed and the lib has moved to the
repository for mysql udfs. If you visit
'http://libmyxql.sourceforge.net', you're redirected (twice) to the
correct site. This means that the correct syntax is on the mysqludf.com
site.
I'll take take you through the 3 most important functions:
xql_element(name, content, attribute, attribute, ...), writes one
complete XML node, which has a (tag)name, content and multiple
attributes. The name is required and should be a string containing only
word chars, dashes and optionally a ':' (for namespace). The content may
be NULL, which will make a node like <name att="value" />, normal
content which is automatically escaped or raw XML which is not escaped.
Output from another xql function is automatically detected an seen a raw
XML. However it you want to add XML from a table or from a subquery, you
need use the special alias `xql:raw`, like xql_element("name", myxml AS
`xql:raw`).
xql_forest(...), writes multiple XML nodes, without any attributes.
The field name or alias will be the tag name and the value will be the
content (and is automatically escaped). xql_forest(first_name,
last_name, IF(gender=1, 'male', 'female') AS gender), would give you
(for 1 row),
"<first_name>Jan</first_name><last_name>Klaassen</last_name><gender>male</gender>".
Note that this is not valid XML, since there is no document root, the
lib doesn't force valid XML. You might want to put the whole thing in a
node though, you could use xql_element for that, however (also for
performance) you can also use the special alias `xql:root` for this.
Example: xql_forest("person" AS `xql:root`, first_name, last_name,
IF(gender=1, 'male', 'female') AS gender), would give you (for 1 row),
"<person><first_name>Jan</first_name><last_name>Klaassen</last_name><gender>male</gender></person>".
xql_agg(...), is almost like xql_forest, except that is aggregates the
data, which means it sticks data from different rows together, like
GROUP_CONCAT. The special alias `xql:root` will put a starting and
closing tag around all of the aggregated data, where `xql:child` puts a
starting and closing tag around each row before it is aggregated.
I hope it is now clear to you.
Good luck,
Arnold
Nat Woodard wrote:
> Please forgive me if you have already received this, I was having
> difficulty establishing a current email address for you. -nat
>
> Hi Arnold,
>
> I am in need of some help and hoping you don't mind me asking you for
> some clarification. I am working on some projects where I have
> recently decided to convert to an xml/xslt approach. In researching
> the best/most efficient way to get xml out of mysql is to use your
> libmyxql library. I had my server admin install the library and have
> been successful in getting a single root node with null child nodes
> (containing attributes). I cannot seem to figure out how to get child
> elements nested in the first element.
>
> When I initially came across your library I ended up with 2 different
> bookmarks to API references. They differ slightly. Can you confirm for
> me which API reference I should be using/learning? I have included
> links to the 2 API refs as well as the two differing examples of a
> result tree. (Please note that I am a designer who has learned more
> code than the average — but I still an idiot when it comes to
> understanding/recognizing syntax etc).
>
>
> Thanks for your time,
>
> -nat
>
> http://www.mysqludf.org/lib_mysqludf_xql/index.php
>
> Simple tree result
> SELECT xql_agg('classes' AS `xql:root`, `schoolclasses` AS `xql:raw`)
> FROM
> (SELECT xql_element('class', xql_agg('pupil' AS `xql:child`,
> firstname, lastname)), concat(grade, suffix) AS `name`, teacher) AS
> `schoolclasses` FROM schoolclass LEFT JOIN pupil ON schoolclass.id =
> pupil.schoolclass_id WHERE grade = 3 GROUP BY schoolclass.id) AS
> schoolclass
>
> http://libmyxql.sourceforge.net/examples.php
>
> SELECT xql_agg_root('classes', class_node) FROM
> (SELECT xql_root('class', xql_agg(xql_forest_el('pupil', 'firstname',
> firstname, 'lastname', lastname)), 'name', concat(grade, suffix),
> 'teacher', teacher) AS class_node FROM schoolclass LEFT JOIN pupil ON
> schoolclass.id = pupil.schoolclass_id WHERE grade = 3 GROUP BY
> schoolclass.id) AS x
nat
confusion being a newbie I am hoping to get advise/direction on.
1. Best method/How to create a complete xml doc? I am trying to
understand if there is a method of producing a complete xml file with
xql? I have used the text declaration successfully, but not sure how
to get the (php file) to be seen as an xml doc without using
"header('Content-Type: application/xml');" in the beginning of the
file. Is there a correct method for using xql to produce a complete
xml doc?
2. API version/syntax - I am having trouble getting the current
examples to work (http://www.mysqludf.org/lib_mysqludf_xql/index.php).
I tried running the lib version and get "lib_mysqludf_xql 0.9.6" which
I believe is current? Below is a code example of the only thing I have
been able to get work, it seems like it follows the older API found at
(http://libmyxql.sourceforge.net/docs.php) but I can't be sure. Is
there a way I can determine that it is just me and my learning curve -
not my serve admin' install job?
$result = $db->query("SELECT xql_textdecl('1.0', 'UTF-8')");
list($xml) = $result->fetch_row();
print_r($xml);
$result = $db->query("SELECT xql_agg_root('feed',
xql_root('entry', xql_forest('published',news_date,'updated',
modified,'entry-title', news_title,'entry-content', news_details,
'event-id', show_link),'id', id)) FROM `_cms_news`");
list($xml) = $result->fetch_row();
print_r($xml);
For anyone that can shed some light for me: I am a designer with xhtml/
css skills but I am not a programmer -- I learn eventually but I am
slow in the beggining. I have recently taken a huge long overdue
plunge into XML/XSLT and have decided to rebuild a system I have been
developing for music venues at the 11th hour. My goal is to minimize
PHP's role by using the xql lib to get xml as needed directly from the
DB.
thanks,
-nat
Arnold Daniels
Thanks for responding on this list. I'm not trying to be ass, but everything on this list is logged and I don't like to explain the same stuff each time.
The website at sourceforge is officially off line. I see you've managed to dig up some pages, by using direct links. However I made a redirect on the index page for a reason: don't expect anything from the sourceforge site to still be correct. Please use the API documentation on http://mysqludf.com/lib_mysqludf_xql.
The xql_agg_root function no longer exist. Instead you can use the alias `xql:root` to specify that the first argument of the xql_agg function should be used as tag name for the root node of the aggregated XML.
Remember that MySQL usually return rows and columns, not a single string of XML. It is up to you to use these functions to get a single column and a single row. With the xql_element, xql_forest and xql_concat function, you can stick values and XML of 1 row together to get to a single column. With xql_agg, you stick the data of rows together. The aggregated function should be used for instance, to stick all products of per invoice together and again to stick all invoices together to come to a single string. Since MySQL can only do one aggregate, you will need to use subqueries for anything beyond to most simple XML schemes.
This isn't useful code:
$result = $db->query("SELECT xql_textdecl('1.0', 'UTF-8')");
list($xml) = $result->fetch_row();
SELECT xql_concat(
xql_textdecl('1.0', 'UTF-8'),
xql_agg('feed' as `xql:root`,
xql_element('entry', xql_forest(news_date AS `published`, modified AS `updated`, news_title AS `entry-title`, news_details AS `entry-content`, show_link AS `event-id`), id AS `id`))
)
) FROM `_cms_news`
This should give you enough to get started. Make sure you read the (correct) documentation.
Best regards,
Arnold