Re: Looping through a table to select one column from each row and then performing XML transformation ExtractValue for each value
tasks schema:
create database if not exists `sample`;
USE `sample`;
DROP TABLE IF EXISTS `items`;
CREATE TABLE `items` ( `uri` varchar(1000) NOT NULL, `content_type` varchar(256) NOT NULL, `data` longblob NOT NULL, PRIMARY KEY (`uri`(767))) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Data for the table `items` */
LOCK TABLES `items` WRITE;
UNLOCK TABLES;
/*Table structure for table `task_actions` */
DROP TABLE IF EXISTS `task_actions`;
CREATE TABLE `task_actions` (`id` int(11) NOT NULL auto_increment,`code` varchar(32) NOT NULL, `description` varchar(128) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `code` (`code`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
/*Data for the table `task_actions` */
LOCK TABLES `task_actions` WRITE;
insert into `task_actions`(`id`,`code`,`description`) values (1,'CREATED',''),(2,'MODIFIED',''),(3,'CLAIMED',''),(4,'REVOKED',''),(5,'REASSIGNED',''),(6,'ESCALATED',''),(7,'COMPLETED',''),(8,'FAILED',''),(9,'DELETED','');
UNLOCK TABLES;
/*Table structure for table `task_attachments` */
DROP TABLE IF EXISTS `task_attachments`;
CREATE TABLE `task_attachments` (`task_id` int(11) NOT NULL, `payload_url` varchar(513) NOT NULL, `file_name` varchar(513) NOT NULL, `mime_type` varchar(128) NOT NULL, `widget` varchar(32) default NULL, `creation_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `title` varchar(513) default NULL, `description` varchar(513) default NULL, PRIMARY KEY (`task_id`,`payload_url`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Data for the table `task_attachments` */
LOCK TABLES `task_attachments` WRITE;
UNLOCK TABLES;
/*Table structure for table `task_role_actions` */
DROP TABLE IF EXISTS `task_role_actions`;
CREATE TABLE `task_role_actions` (`task_id` int(11) NOT NULL, `action_id` varchar(32) NOT NULL, `role_id` varchar(32) NOT NULL, PRIMARY KEY (`task_id`,`action_id`,`role_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Data for the table `task_role_actions` */
LOCK TABLES `task_role_actions` WRITE;
UNLOCK TABLES;
/*Table structure for table `task_role_owners` */
DROP TABLE IF EXISTS `task_role_owners`;
CREATE TABLE `task_role_owners` (`task_id` int(11) NOT NULL, `role_id` varchar(32) NOT NULL, PRIMARY KEY (`task_id`,`role_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Data for the table `task_role_owners` */
LOCK TABLES `task_role_owners` WRITE;
UNLOCK TABLES;
/*Table structure for table `task_states` */
DROP TABLE IF EXISTS `task_states`;
CREATE TABLE `task_states` ( `id` int(11) NOT NULL auto_increment, `code` varchar(32) NOT NULL, `description` varchar(128) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `code` (`code`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*Data for the table `task_states` */
LOCK TABLES `task_states` WRITE;
insert into `task_states`(`id`,`code`,`description`) values (1,'ready',''),(2,'completed',''),(3,'failed',''),(4,'claimed','');
UNLOCK TABLES;
/*Table structure for table `task_types` */
DROP TABLE IF EXISTS `task_types`;
CREATE TABLE `task_types` ( `id` int(11) NOT NULL auto_increment, `code` varchar(32) NOT NULL, `description` varchar(128) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `code` (`code`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
/*Data for the table `task_types` */
LOCK TABLES `task_types` WRITE;
insert into `task_types`(`id`,`code`,`description`) values (1,'init',''),(2,'activity',''),(3,'notification','');
UNLOCK TABLES;
/*Table structure for table `task_user_actions` */
DROP TABLE IF EXISTS `task_user_actions`;
CREATE TABLE `task_user_actions` ( `task_id` int(11) NOT NULL, `action_id` varchar(32) NOT NULL, `user_id` varchar(32) NOT NULL, PRIMARY KEY (`task_id`,`action_id`,`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Data for the table `task_user_actions` */
LOCK TABLES `task_user_actions` WRITE;
UNLOCK TABLES;
/*Table structure for table `task_user_owners` */
DROP TABLE IF EXISTS `task_user_owners`;
CREATE TABLE `task_user_owners` ( `task_id` int(11) NOT NULL, `user_id` varchar(32) NOT NULL, PRIMARY KEY (`task_id`,`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*Data for the table `task_user_owners` */
LOCK TABLES `task_user_owners` WRITE;
UNLOCK TABLES;
/*Table structure for table `tasks` */
DROP TABLE IF EXISTS `tasks`;
CREATE TABLE `tasks` ( `id` int(11) NOT NULL auto_increment, `task_id` varchar(128) NOT NULL, `process_id` varchar(128) default NULL, `type_id` int(11) NOT NULL, `state_id` int(11) NOT NULL, `description` varchar(512) default NULL, `creation_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `form_url` varchar(2048) NOT NULL, `failure_code` varchar(128) default NULL, `failure_reason` varchar(512) default NULL, `input_xml` text, `output_xml` text, `endpoint` varchar(256) default NULL, `namespace` varchar(512) default NULL, `init_soap_action` varchar(512) default NULL, `complete_soap_action` varchar(512) default NULL, `is_chained_before` char(1) default NULL, `previous_task_id` varchar(128) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `task_id` (`task_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES `tasks` WRITE;
UNLOCK TABLES;