MySQL Forums
Forum List  »  Microsoft SQL Server

Re: Looping through a table to select one column from each row and then performing XML transformation ExtractValue for each value
Posted by: Rahul Kumar
Date: July 06, 2010 08:17AM

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;

Options: ReplyQuote


Subject
Written By
Posted
Re: Looping through a table to select one column from each row and then performing XML transformation ExtractValue for each value
July 06, 2010 08:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.