Rows and Columns

<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0"
	xmlns:content="http://purl.org/rss/1.0/modules/content/"
	xmlns:wfw="http://wellformedweb.org/CommentAPI/"
	xmlns:dc="http://purl.org/dc/elements/1.1/"
	>
 
<channel>
	<title>SQL Anywhere Blogs Master Feed</title>
	<link>http://www.sybase.com/sqlanyblogs</link>
	<description>A master feed for all SQL Anywhere blogs kept on iablog.sybase.com.</description>
	<pubDate>Fri, 12 Dec 2008</pubDate>
	<language>en</language>
 
<?php 	
             //Connection string altered to protect the innocent.
	$conn = sqlanywhere_connect( "uid=wpuser;pwd=wppassword;eng=wpeng" );
 
             //We make use of SQL Anywheres SQL/XML support to create the RSS feed items
	$result = sqlanywhere_query( $conn, 
                              "SELECT DISTINCT xmlelement( name item, 
                                xmlelement( 'title', post_title ), 
                                xmlelement( 'link', guid ),
                                xmlelement( 'comments', guid + '#comments' ),
                                xmlelement( 'pubDate', post_date),
                                xmlelement( 'dc:creator', post_author), 
                                xmlagg( xmlelement( 'category', post_category )),
                                xmlelement( 'guid', guid ),
                                xmlelement( 'description',  post_excerpt),
                                xmlelement( 'content:encoded', post_content ),
                                xmlelement( 'wfw:commentRss', guid + '/feed/' ) ) + '
                                ' as rssItem
                               FROM getrecentposts(20)
		     GROUP BY post_title, guid, post_date, post_content, post_excerpt, post_author" );
	if( sqlanywhere_error( $conn ) ) {
	      $error_msg = sqlanywhere_error( $conn );
	      echo "Query failed. Reason: $error_msg<br>";
	}
	while ( $row = @sqlanywhere_fetch_object($result) ) {
                 echo $row->rssItem;
	}
 
?>	
</channel>
</rss>

/*
The first thing the procedure does is define the result set and create some local 
variables.  Note that the stored procedure takes an argument which defines how 
many articles to include in the returned results, with a default of 10
*/
CREATE PROCEDURE "DBA"."getrecentposts"( IN num_posts integer DEFAULT 10 )
RESULT( post_author long varchar, posted_date varchar(32), post_title long varchar, 
            post_excerpt long varchar, guid long varchar, post_category varchar(128), 
            post_content long varchar, post_date datetime )
--Retrieve the post data from the database for the latest "num_posts" posts by date
BEGIN
    DECLARE post_query varchar(4192);
    DECLARE post_tname varchar(128);
    DECLARE user_tname varchar(128);
    DECLARE taxonomy_tname varchar(128);
    DECLARE relationship_tname varchar(128);
    DECLARE terms_tname varchar(128);
/*
Next, we open a cursor that will give us the list of tables containing the post 
data for each user.  Since I chose a naming convention for wordpress table 
names in the blogs, this statement was easy to construct.
*/
    DECLARE post_tables INSENSITIVE CURSOR FOR SELECT table_name 
        FROM SYSTABLE WHERE table_name LIKE 'wp%_posts' FOR READ ONLY;
/*
Now, we begin building the query for retrieving the post information from the 
database, using the num_posts argument to the stored procedure to limit 
the result set to the latest n results.  This query will use a derived table to
first pull the data from each bloggers posts and union them together, and then
return the top num_posts posts from that result, after ordering by date
*/
    SET post_query = 'SELECT TOP ' || num_posts || ' post_author, ' ||
        'DATEFORMAT( post_date, 'Ddd, dd Mmm yyyy hh:mm:ss +0000' ) as posted_date, ' ||
        'post_title, post_excerpt, guid, category, post_content, post_date FROM (';
 
    --Open the cursor with the list of table names which contain the post data
    OPEN post_tables;
    FETCH post_tables INTO post_tname;
 
    --We must use the supporting taxonomy tables to get category information
    -- and since the naming convention is constant, we can use search and 
    -- replace to generate the names rather using another cursor
    SET user_tname = REPLACE( post_tname, 'posts', 'users' );
    SET taxonomy_tname = REPLACE( post_tname, 'posts', 'term_taxonomy' );
    SET relationship_tname = REPLACE( post_tname, 'posts', 'term_relationships' );
    SET terms_tname = REPLACE( post_tname, 'posts', 'terms' );
 
    -- Add to the query to pull the post data for the current blogs post tables
    SET post_query = post_query || 
        'SELECT display_name as post_author, post_date, post_title, ' ||
            ' IF post_excerpt = ' THEN LEFT(post_content, 256)||'...' ' ||
            ' ELSE post_excerpt ' ||
            ' ENDIF AS post_excerpt, ' ||
            'guid, name AS category, post_content ' ||
        ' FROM ' || post_tname || ', ' || user_tname || ', ' || 
            taxonomy_tname || ', ' || relationship_tname || ', ' || terms_tname ||
        ' WHERE ' || post_tname || '.post_author = ' || user_tname || '.id ' ||
            ' AND post_status = 'publish' AND post_type = 'post' ||
            ' AND ' || post_tname || '.id = ' || relationship_tname || '.object_id ' || 
            ' AND ' || relationship_tname || '.term_taxonomy_id = ' || taxonomy_tname || '.term_taxonomy_id ' ||
            ' AND ' || taxonomy_tname || '.term_id = ' || terms_tname || '.term_id ' ||
            ' AND ' || taxonomy_tname || '.taxonomy = 'category' ';
 
    --Fetch the post table name for the next blog in the database, and add their
    -- data to the post query, loop until all bloggers have been included
    FETCH post_tables INTO post_tname;
    WHILE SQLCODE = 0 LOOP
        SET user_tname = REPLACE( post_tname, 'posts', 'users' );
        SET taxonomy_tname = REPLACE( post_tname, 'posts', 'term_taxonomy' );
        SET relationship_tname = REPLACE( post_tname, 'posts', 'term_relationships' );
        SET terms_tname = REPLACE( post_tname, 'posts', 'terms' );
        SET post_query = post_query || ' UNION ALL ' ||
            'SELECT display_name as post_author, post_date, post_title,' ||
            '  IF post_excerpt = ' ' ||
            '    THEN LEFT(post_content, 256)||'...' ' ||
            '  ELSE post_excerpt ' || 
            '  ENDIF AS post_excerpt, ' ||
            '  guid, name AS category, post_content ' ||
            'FROM ' || post_tname || ', ' || user_tname || ', ' || taxonomy_tname || 
              ', ' || relationship_tname || ', ' || terms_tname ||
            'WHERE ' || post_tname || '.post_author = ' || user_tname || '.id ' ||
            ' AND post_status = 'publish' AND post_type = 'post' ||
            ' AND ' || post_tname || '.id = ' || relationship_tname || '.object_id ' || 
            ' AND ' || relationship_tname || '.term_taxonomy_id = ' || taxonomy_tname || '.term_taxonomy_id ' ||
            ' AND ' || taxonomy_tname || '.term_id = ' || terms_tname || '.term_id AND ' || taxonomy_tname || '.taxonomy = 'category' ';
        FETCH post_tables INTO post_tname;
    END LOOP;
/*
Now we add an ordering for the result set and return it to the application, which in this case is our masterfeed php script:
*/
    SET post_query = post_query || ') as T1 ORDER BY post_date desc';
    --Enable the following line to print the query to the server message window
    --Message post_query;
    EXECUTE IMMEDIATE WITH RESULT SET ON post_query;
END