Possible Solution for Midsummer Midsomer Murder SQL Challenge

View All Public Scripts Login to Run Script

Script Name Possible Solution for Midsummer Midsomer Murder SQL Challenge
Visibility Unlisted - anyone with the share link can access
Description This is a possible solution using Object Types
Area SQL General
Contributor Philipp Salvisberg
Created Wednesday June 10, 2020

Statement 1

Creating the table to store one row with the XML data in a CLOB column.

create table midsomer_xml (   
   id        integer primary key   
 , the_xml   clob   
)

Table created.

Statement 2
Insert one row to get the LOB locator for the CLOB that will be filled in the next statement.
```
insert into midsomer_xml (id) values (1)
```
```
1 row(s) inserted.
```

Statement 3

Insert one row to get the LOB locator for the CLOB that will be filled in the next statement.

declare  
   xml_clob  clob;  
begin  
   select the_xml  
   into xml_clob  
   from midsomer_xml  
   where id = 1;  
  
   xml_clob := xml_clob || q'[  
<table>  
  
<tr>  
<td>Ep ID</td>  
<td>Title</td>  
<td>Murdered During Episode</td>  
<td>Murdered Prior to Episode</td>  
<td>Death by Suicide</td>  
<td>Accidental or Natural Death</td>  
<td>Attempted Murders or Suicides</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>0.1</td>  
<td>The Killings at Badger's Drift</td>  
<td>Emily Simpson|Dennis Rainbird|Iris Rainbird|Katherine Lacey</td>  
<td>Bella Trace</td>  
<td>Phyllis Cadell|Michael Lacey</td>  
<td>Mr. Lacey|Mrs. Lacey</td>  
<td></td>  
</tr>  
<tr>  
<td>1.1</td>  
<td>Written in Blood</td>  
<td>Gerald Hadleigh|Max Jennings</td>  
<td>Mr. Hanlon</td>  
<td></td>  
<td>Ralph Lyddiard|Honoria Lyddiard</td>  
<td>Amy Lyddiard</td>  
</tr>  
<tr>  
<td>1.2</td>  
<td>Death of a Hollow Man</td>  
<td>Agnes Gray|Esslyn Carmichael</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Doris Winstanley</td>  
</tr>  
<tr>  
<td>1.3</td>  
<td>Faithful unto Death</td>  
<td>Brenda Buckley|Alan Hollingsworth</td>  
<td></td>  
<td></td>  
<td>Simone's mother</td>  
<td></td>  
</tr>  
<tr>  
<td>1.4</td>  
<td>Death in Disguise</td>  
<td>Ian Craigie|Guy Gamelin</td>  
<td>Gaius Quintus</td>  
<td></td>  
<td>William Carter</td>  
<td>Tim Riley</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>2.1</td>  
<td>Death's Shadow</td>  
<td>Richard Bayly|David Whitely|Simon Fletcher</td>  
<td></td>  
<td>Rev. Stephen Wentworth</td>  
<td>Felix Bryce|Jennifer Bryce</td>  
<td></td>  
</tr>  
<tr>  
<td>2.2</td>  
<td>Strangler's Wood</td>  
<td>Carla Constanza|Dr. Ian Aycott|Anna Santarosa|Leonard Pike</td>  
<td>Eva Hoffman|Joan Chaplin|Judith Albiston</td>  
<td></td>  
<td></td>  
<td>David Merrill|John Merrill</td>  
</tr>  
<tr>  
<td>2.3</td>  
<td>Dead Man's Eleven</td>  
<td>Tara Cavendish|Charles Jennings</td>  
<td>Emily Beavis</td>  
<td></td>  
<td>Matthew Draper|Patricia Smith</td>  
<td>Robert Cavendish</td>  
</tr>  
<tr>  
<td>2.4</td>  
<td>Blood Will Out</td>  
<td>Hector Bridges|John Smith</td>  
<td>Catherine Hamilton</td>  
<td></td>  
<td>John Lampson</td>  
<td>Hector Bridges</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>3.1</td>  
<td>Death of a Stranger</td>  
<td>Michael Darrow|Ben Gurdie|Ron Pringle|Marcia Tranter</td>  
<td>Simon Tranter</td>  
<td>Grahame Tranter</td>  
<td></td>  
<td></td>  
</tr>  
<tr>  
<td>3.2</td>  
<td>Blue Herrings</td>  
<td>Celia Armstrong</td>  
<td></td>  
<td></td>  
<td>Mrs. Mortimer|Muriel Harrap|Madge Fielding</td>  
<td></td>  
</tr>  
<tr>  
<td>3.3</td>  
<td>Judgement Day</td>  
<td>Peter Drinkwater|Rosemary Furman|Samantha Johnstone|Bella Devere</td>  
<td>Mrs. Foster</td>  
<td></td>  
<td>Michael Weston|Ruth Weston</td>  
<td>Samantha Johnstone</td>  
</tr>  
<tr>  
<td>3.4</td>  
<td>Beyond the Grave</td>  
<td>Marcus Lowrie|James Tate</td>  
<td>Jonathan Lowrie</td>  
<td></td>  
<td>David MacKillop</td>  
<td></td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>4.1</td>  
<td>Garden of Death</td>  
<td>Felicity Inkpen-Thomas|Elspeth Inkpen-Thomas|Gerald Bennett</td>  
<td>Cynthia Bennett</td>  
<td></td>  
<td></td>  
<td>Naomi Inkpen|Augustus Deverell</td>  
</tr>  
<tr>  
<td>4.2</td>  
<td>Destroying Angel</td>  
<td>Gregory Chambers|Tristan Goodfellow|Kenneth Gooders|Suzanna Chambers</td>  
<td>Karl Wainwright</td>  
<td>Evelyn Pope</td>  
<td></td>  
<td>Julia Gooders|Clarice Opperman|Evelyn Pope</td>  
</tr>  
<tr>  
<td>4.3</td>  
<td>The Electric Vendetta</td>  
<td>Steve Ramsey|Lloyd Kirby</td>  
<td></td>  
<td></td>  
<td>Ronald Stokes|Eddie Field|Isabel Aubrey|Dave Ripert</td>  
<td>Christian Aubrey</td>  
</tr>  
<tr>  
<td>4.4</td>  
<td>Who Killed Cock Robin?</td>  
<td>Robin Wooliscroft|Mary Mohan</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Sean O'Connell</td>  
</tr>  
<tr>  
<td>4.5</td>  
<td>Dark Autumn</td>  
<td>Dave Cutler|Debbie Shortlands|Simon Reason|Owen August</td>  
<td></td>  
<td>Tammie, the mother of John Field</td>  
<td></td>  
<td>WPC Jay Nash</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>5.1</td>  
<td>Tainted Fruit</td>  
<td>Melissa Townsend|Adam Keyne|Raif Canning</td>  
<td></td>  
<td></td>  
<td>Neil Laxton</td>  
<td></td>  
</tr>  
<tr>  
<td>5.2</td>  
<td>Market for Murder</td>  
<td>Marjorie Empson|Ginny Sharp|Lady Lavinia Chetwood</td>  
<td></td>  
<td></td>  
<td>Gerald Empson</td>  
<td>Selwyn Proctor</td>  
</tr>  
<tr>  
<td>5.3</td>  
<td>A Worm in the Bud</td>  
<td>Susan Bartlett|James Harrington|Victoria Bartlett</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Jonah Bloxham</td>  
</tr>  
<tr>  
<td>5.4</td>  
<td>Ring Out Your Dead</td>  
<td>Greg Tutt|Emma Tysoe|Marcus Steadman</td>  
<td>Reverend Jonathan Ebbrell</td>  
<td></td>  
<td>Reggie Barton</td>  
<td>Peter Fogden|Liam Booker|Frances Le Bon</td>  
</tr>  
<tr>  
<td>5.5</td>  
<td>Murder on St. Malley's Day</td>  
<td>Archie Bellingham|Daniel Talbot|Dudley Carew</td>  
<td></td>  
<td></td>  
<td>Sir. Walter Talbot</td>  
<td>Sir. Anthony Talbot</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>6.1</td>  
<td>A Talent for Life</td>  
<td>Isobel Hewitt|Dr. Duncan Goff|Keith Scholey</td>  
<td></td>  
<td></td>  
<td>Kenneth Hewitt|Mrs. Scholey</td>  
<td></td>  
</tr>  
<tr>  
<td>6.2</td>  
<td>Death and Dreams</td>  
<td>Martin Wroath|Tony Parish|Gordon Leesmith</td>  
<td>Andy Moore</td>  
<td></td>  
<td></td>  
<td>DCI Tom Barnaby</td>  
</tr>  
<tr>  
<td>6.3</td>  
<td>Painted in Blood</td>  
<td>Ruth Fairfax</td>  
<td></td>  
<td></td>  
<td></td>  
<td>DS Gavin Troy|DCI Tom Barnaby|Colin Hawksley|Mrs. Partridge|Mr. Miller</td>  
</tr>  
<tr>  
<td>6.4</td>  
<td>A Tale of Two Hamlets</td>  
<td>Larry Smith|Frank Webster|Danny Pinchel</td>  
<td></td>  
<td>St John Smythe-Webster</td>  
<td>Ellis Bell|Elsie Pinchel</td>  
<td>Phil Harrison</td>  
</tr>  
<tr>  
<td>6.5</td>  
<td>Birds of Prey</td>  
<td>Julian Shepherd|Charles Edmonton|Eddie Darwin|George Hamilton</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Mallory Edmonton</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>7.1</td>  
<td>The Green Man</td>  
<td>Simon Mayfield|Steven Curtis|Lillian Webster</td>  
<td>Eric Edwards</td>  
<td></td>  
<td>Sir Jonathan Haslett|Lettie Edwards</td>  
<td></td>  
</tr>  
<tr>  
<td>7.2</td>  
<td>Bad Tidings</td>  
<td>Fiona Thompson|Dr. Charles Rust|Jacob Stokes</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Cully Barnaby</td>  
</tr>  
<tr>  
<td>7.3</td>  
<td>The Fisher King</td>  
<td>Gareth Heldman|David Heartley-Reade|Dr. James Lavery</td>  
<td>Roger Heldman</td>  
<td></td>  
<td></td>  
<td>Miriam Heartley-Reade</td>  
</tr>  
<tr>  
<td>7.4</td>  
<td>Sins of Commission</td>  
<td>Richard Rackham|Neville Williams|Jezebel Tripp</td>  
<td></td>  
<td></td>  
<td>Conor Maplin</td>  
<td>Camilla Crofton|Sam Callaghan</td>  
</tr>  
<tr>  
<td>7.5</td>  
<td>The Maid in Splendour</td>  
<td>Jamie Cruickshank|Stephen Bannerman</td>  
<td></td>  
<td></td>  
<td>Mr. Monday|Susan Bannerman|Michael Bannerman</td>  
<td>Michael Bannerman</td>  
</tr>  
<tr>  
<td>7.6</td>  
<td>The Straw Woman</td>  
<td>Alex Deakin|Jim Hale|Liz Francis</td>  
<td></td>  
<td></td>  
<td>Mrs. Cole</td>  
<td>Dr. John Cole|Alan Clifford|Agnes Waterhouse</td>  
</tr>  
<tr>  
<td>7.7</td>  
<td>Ghosts of Christmas Past</td>  
<td>Lydia Villiers|Jennifer Carter</td>  
<td></td>  
<td>Ferdy Villiers|Claire English</td>  
<td></td>  
<td>Lydia Villiers</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>8.1</td>  
<td>Things That Go Bump in the Night</td>  
<td>Patrick Pennyman|Elizabeth Key|John Whittle</td>  
<td></td>  
<td>David Key|Rosemary Key</td>  
<td></td>  
<td></td>  
</tr>  
<tr>  
<td>8.2</td>  
<td>Dead in the Water</td>  
<td>Guy Sweetman</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Sandra Tate</td>  
</tr>  
<tr>  
<td>8.3</td>  
<td>Orchis Fatalis</td>  
<td>Madeline Villiers|Henry Plummer|Munro Hilliard</td>  
<td></td>  
<td></td>  
<td></td>  
<td>DCI Tom Barnaby|DS Dan Scott|Margaret Winstanley</td>  
</tr>  
<tr>  
<td>8.4</td>  
<td>Bantling Boy</td>  
<td>Bruce Hartley|Dr. John Osgood|Trevor Machin|Joanna Craxton</td>  
<td></td>  
<td></td>  
<td>Colonel Hartley</td>  
<td>Peter Craxton</td>  
</tr>  
<tr>  
<td>8.5</td>  
<td>Second Sight</td>  
<td>John Ransom|Dr. Gregory Ransom|Max Ransom</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Little Mal Kirby</td>  
</tr>  
<tr>  
<td>8.6</td>  
<td>Hidden Depths</td>  
<td>Jack Wilmot|Otto Benham|Mike Spicer</td>  
<td></td>  
<td></td>  
<td></td>  
<td>DCI Tom Barnaby|DS Dan Scott|Antonia Wilmot</td>  
</tr>  
<tr>  
<td>8.7</td>  
<td>Sauce for the Goose</td>  
<td>Dexter Lockwood</td>  
<td></td>  
<td>Stanley Lockwood</td>  
<td>Maurice Plummer</td>  
<td></td>  
</tr>  
<tr>  
<td>8.8</td>  
<td>Midsomer Rhapsody</td>  
<td>Arthur Leggott|Owen Swinscoe|Harvey Crane</td>  
<td></td>  
<td></td>  
<td>Joan Alder</td>  
<td></td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>9.1</td>  
<td>The House in the Woods</td>  
<td>Caroline Cave|Peter Cave|Harriet Davis</td>  
<td>P.C. Colin Armstrong</td>  
<td></td>  
<td>Mr. Barrett|Mrs. Barrett</td>  
<td>Jack Magwood</td>  
</tr>  
<tr>  
<td>9.2</td>  
<td>Dead Letters</td>  
<td>Marion Slade|Mark Castle|Dr. Wellow</td>  
<td></td>  
<td></td>  
<td>Bella Slade</td>  
<td>Alistair Gooding</td>  
</tr>  
<tr>  
<td>9.3</td>  
<td>Vixen's Run</td>  
<td>Henry York|Lady Sandra Butler</td>  
<td></td>  
<td></td>  
<td>Sir Freddy Butler</td>  
<td></td>  
</tr>  
<tr>  
<td>9.4</td>  
<td>Down Among the Dead Men</td>  
<td>Martin Barrett</td>  
<td></td>  
<td></td>  
<td>Jean Waverley</td>  
<td>Sir John Waverley</td>  
</tr>  
<tr>  
<td>9.5</td>  
<td>Four Funerals and a Wedding</td>  
<td>Mildred Danvers|Rev. Anthony Gant|Cathy Hewlett</td>  
<td></td>  
<td>Vivienne Marwood</td>  
<td></td>  
<td></td>  
</tr>  
<tr>  
<td>9.6</td>  
<td>Country Matters</td>  
<td>Frank Hopkirk</td>  
<td></td>  
<td></td>  
<td>Giles Southerly</td>  
<td></td>  
</tr>  
<tr>  
<td>9.7</td>  
<td>Death in Chorus</td>  
<td>Connor Simpson|Sam Judd</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Carolyn Armitage</td>  
</tr>  
<tr>  
<td>9.8</td>  
<td>Last Year's Model</td>  
<td></td>  
<td>Frances Trevelyan</td>  
<td></td>  
<td></td>  
<td>Mrs. Beverley</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>10.1</td>  
<td>Dance with the Dead</td>  
<td>Simon Bright|Laura Sharp|Tony Kirby</td>  
<td></td>  
<td></td>  
<td>Ralph Wood</td>  
<td>Carol Prentice</td>  
</tr>  
<tr>  
<td>10.2</td>  
<td>The Animal Within</td>  
<td>Rex Masters|Miles King|Eileen Carnack</td>  
<td></td>  
<td></td>  
<td>Lucinda Thacker</td>  
<td></td>  
</tr>  
<tr>  
<td>10.3</td>  
<td>King's Crystal</td>  
<td>Alan King|Ian King</td>  
<td></td>  
<td>Peter Baxter</td>  
<td></td>  
<td>Ian King|Charles King</td>  
</tr>  
<tr>  
<td>10.4</td>  
<td>The Axeman Cometh</td>  
<td>Mimi Clifton|Nicky Harding</td>  
<td></td>  
<td>Ginger Foxton</td>  
<td></td>  
<td></td>  
</tr>  
<tr>  
<td>10.5</td>  
<td>Death and Dust</td>  
<td>Dr. Alan Delaney</td>  
<td></td>  
<td>Sarah Kirkwood</td>  
<td></td>  
<td>Dr. James Kirkwood</td>  
</tr>  
<tr>  
<td>10.6</td>  
<td>Picture of Innocence</td>  
<td>Lionel Bell|Steve Bright</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Martin Spellman</td>  
</tr>  
<tr>  
<td>10.7</td>  
<td>They Seek Him Here</td>  
<td>Nick Cheyney|Gwen Morrison</td>  
<td></td>  
<td></td>  
<td>Ted Hayward</td>  
<td>Neville Hayward</td>  
</tr>  
<tr>  
<td>10.8</td>  
<td>Death in a Chocolate Box</td>  
<td>Jack Colby|Eddie Marston</td>  
<td>Maria Godbold</td>  
<td>Gina Colby</td>  
<td></td>  
<td></td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>11.1</td>  
<td>Shot at Dawn</td>  
<td>Henry Hammond|Johnny Hammond</td>  
<td>Tommy Hicks</td>  
<td></td>  
<td>Duggie Hammond</td>  
<td>Arabella Hammond</td>  
</tr>  
<tr>  
<td>11.2</td>  
<td>Blood Wedding</td>  
<td>Marina Fellowes|Robin Lawson|Peggy Benson</td>  
<td></td>  
<td></td>  
<td>Lady Fitzroy|Lord Fitzroy</td>  
<td>DCI Tom Barnaby|Ned Fitzroy|DS Ben Jones</td>  
</tr>  
<tr>  
<td>11.3</td>  
<td>Left for Dead</td>  
<td>Ron Wilson|Jack Purdy|Louise Purdy</td>  
<td></td>  
<td>Mr. Purdy</td>  
<td>Libby Wilson|Michael Wilson</td>  
<td>Patrick Bradley|Charlotte Knight</td>  
</tr>  
<tr>  
<td>11.4</td>  
<td>Midsomer Life</td>  
<td>Frederick Tomlin|Guy Sandys|Eleanor Crouch</td>  
<td></td>  
<td></td>  
<td>Mrs. Wilson's Sister|Charlie Finleyson</td>  
<td></td>  
</tr>  
<tr>  
<td>11.5</td>  
<td>The Magician's Nephew</td>  
<td>Jean Wildacre|Hugo Cartwright|Aloysius Wilmington</td>  
<td></td>  
<td></td>  
<td>Lucy Wilmington|Tristan Balliol</td>  
<td>Ernest Balliol</td>  
</tr>  
<tr>  
<td>11.6</td>  
<td>Days of Misrule</td>  
<td>Alec Grainger|James Parkes</td>  
<td></td>  
<td>Gilly Galsworthy</td>  
<td>Colonel Halsey|Mrs. Parkes</td>  
<td></td>  
</tr>  
<tr>  
<td>11.7</td>  
<td>Talking to the Dead</td>  
<td>Terrance Lowther|Colin Thomas|Molly Thomas|Lynton Pargeter</td>  
<td></td>  
<td></td>  
<td>Peter Thomas|Cyrus LeVanu</td>  
<td>Nesta Goodfellow|Stanley Goodfellow</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>12.1</td>  
<td>The Dogleg Murders</td>  
<td>Alistair Kingslake|Ed Monkberry</td>  
<td></td>  
<td></td>  
<td>Grandfather Crisp|Mr. Crisp|Mr. Fountain</td>  
<td>Miles Tully</td>  
</tr>  
<tr>  
<td>12.2</td>  
<td>The Black Book</td>  
<td>Felicity Law|Graham Spate|Anthony Prideaux</td>  
<td></td>  
<td></td>  
<td></td>  
<td></td>  
</tr>  
<tr>  
<td>12.3</td>  
<td>Secrets and Spies</td>  
<td>Geoffrey Larkin|Nicky Frazer</td>  
<td></td>  
<td></td>  
<td>Gwendoline Frazer</td>  
<td>Malcolm Frazer</td>  
</tr>  
<tr>  
<td>12.4</td>  
<td>The Glitch</td>  
<td>Emily Harte|Daniel Snape</td>  
<td></td>  
<td></td>  
<td></td>  
<td>George Jeffers</td>  
</tr>  
<tr>  
<td>12.5</td>  
<td>Small Mercies</td>  
<td>Richard Tanner|Christa Palfrey|Harriet Compton</td>  
<td></td>  
<td></td>  
<td></td>  
<td></td>  
</tr>  
<tr>  
<td>12.6</td>  
<td>The Creeper</td>  
<td>David Roper|Dr. Sylvia Goring</td>  
<td></td>  
<td></td>  
<td>Harry Godbolt</td>  
<td>Elizabeth Chettham</td>  
</tr>  
<tr>  
<td>12.7</td>  
<td>The Great and the Good</td>  
<td>Jim Hanley|Laurence Mann</td>  
<td></td>  
<td></td>  
<td></td>  
<td></td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>13.1</td>  
<td>The Made-to-Measure Murders</td>  
<td>Sonia Woodley|Reverend Archie Moreland</td>  
<td>An unnamed gardener</td>  
<td></td>  
<td>Gerald Woodley</td>  
<td>Katie Soper</td>  
</tr>  
<tr>  
<td>13.2</td>  
<td>The Sword of Guillaume</td>  
<td>Hugh Dalgleish|Jenny Russell</td>  
<td></td>  
<td></td>  
<td>Sir Richard Guillaume</td>  
<td>Giles Shawcross|DCI Tom Barnaby</td>  
</tr>  
<tr>  
<td>13.3</td>  
<td>Blood on the Saddle</td>  
<td>Faye Lennox|Jack Fincher|Leo Fincher|Fergal Jenner</td>  
<td></td>  
<td></td>  
<td>Caleb Burbage|Noah Fincher|David Langham</td>  
<td>Susan Fincher|Jude Langham|DCI Tom Barnaby|DS Ben Jones</td>  
</tr>  
<tr>  
<td>13.4</td>  
<td>The Silent Land</td>  
<td>Gerald Ebbs|Jeff Bowmaker</td>  
<td>Landlord's wife|Landlord's daughter</td>  
<td>Caroline Maria Roberts|An unnamed landlord</td>  
<td>The patients from Saint Fidelis Hospital|Mrs. Peach</td>  
<td></td>  
</tr>  
<tr>  
<td>13.5</td>  
<td>Master Class</td>  
<td>Orlando Guest|Reverend Connor Gregory</td>  
<td></td>  
<td>Molly Fielding</td>  
<td></td>  
<td>DCI Tom Barnaby|Terry Stock|Dawn Stock</td>  
</tr>  
<tr>  
<td>13.6</td>  
<td>The Noble Art</td>  
<td>Giles Braithwaite|Frank Bishop|Iris Holman</td>  
<td></td>  
<td></td>  
<td>Sebastian's Biological Father</td>  
<td></td>  
</tr>  
<tr>  
<td>13.7</td>  
<td>Not in My Back Yard</td>  
<td>Maureen Stubbs|Clifford Bunting|Geoff Rogers</td>  
<td></td>  
<td></td>  
<td>Mr. Conway|Douglas Stubbs</td>  
<td>Norman Swanscombe|DCI Tom Barnaby</td>  
</tr>  
<tr>  
<td>13.8</td>  
<td>Fit For Murder</td>  
<td>Kitty Pottinger|Luke Archbold|The vicar at Badger's Drift</td>  
<td></td>  
<td></td>  
<td><b>Mr. Barnaby</b>|Magnus</td>  
<td>Kenny Pottinger</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>14.1</td>  
<td>Death in the Slow Lane</td>  
<td>Dave Doggy Day|Thomas Brightwell</td>  
<td>Duncan Palmer</td>  
<td></td>  
<td></td>  
<td>DCI John Barnaby</td>  
</tr>  
<tr>  
<td>14.2</td>  
<td>Dark Secrets</td>  
<td>Gerry Dawkins|Mary Bingham</td>  
<td></td>  
<td></td>  
<td>Bertie Morell|Robin Bingham|Dr. Jonathan Frost</td>  
<td>William Bingham|DCI John Barnaby|DS Ben Jones</td>  
</tr>  
<tr>  
<td>14.3</td>  
<td>Echoes of the Dead</td>  
<td>Dianne Price|Fran Carter|Len Merryman|Olive Merryman</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Jo Starling</td>  
</tr>  
<tr>  
<td>14.4</td>  
<td>The Oblong Murders</td>  
<td>Freddie Raft</td>  
<td>Jeremy Lambert|Carolyn Lambert|Max Fuller</td>  
<td></td>  
<td></td>  
<td>Claire Powell|Paddy Powell|DCI John Barnaby|DS Ben Jones</td>  
</tr>  
<tr>  
<td>14.5</td>  
<td>The Sleeper Under the Hill</td>  
<td>Alex Preston|Leticia Clifford|Sgt. Trevor Gibson</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Ezra Canning</td>  
</tr>  
<tr>  
<td>14.6</td>  
<td>The Night of the Stag</td>  
<td>Peter Slim|Conrad Walker</td>  
<td></td>  
<td></td>  
<td></td>  
<td>DCI John Barnaby|DS Ben Jones|Byron Street|Silas Trout</td>  
</tr>  
<tr>  
<td>14.7</td>  
<td>A Sacred Trust</td>  
<td>Mother Thomas|Father Behan</td>  
<td></td>  
<td></td>  
<td>Sir Anthony Vertue|Mr. Vertue</td>  
<td>Mother Julian</td>  
</tr>  
<tr>  
<td>14.8</td>  
<td>A Rare Bird</td>  
<td>Patrick Morgan|Dr. Markham</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Nina Morgan</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>15.1</td>  
<td>The Dark Rider</td>  
<td>Julian DeQuetteville|Ludo DeQuetteville</td>  
<td>Geoffrey DeQuetteville</td>  
<td></td>  
<td>Rupert DeQuetteville|Angela DeQuetteville|Bentham DeQuetteville</td>  
<td>Sasha Fleetwood</td>  
</tr>  
<tr>  
<td>15.2</td>  
<td>Murder of Innocence</td>  
<td>Giles Harrison|Caroline Garrett|Kyle Gideon|Keith Mulory</td>  
<td>Daniel Denning|Mrs. Mulory</td>  
<td></td>  
<td>Miss Felton|Ross</td>  
<td>Keith Mulory|DS Ben Jones|Ted Denning|Grady Felton|DCI John Barnaby</td>  
</tr>  
<tr>  
<td>15.3</td>  
<td>Written in the Stars</td>  
<td>Jeremy Harper|Peter Groves|George Dormer</td>  
<td></td>  
<td></td>  
<td>Tom Stanton|Mary Dutta</td>  
<td>Gagan Dutta</td>  
</tr>  
<tr>  
<td>15.4</td>  
<td>Death and the Divas</td>  
<td>Eve Lomax|Cy Davenport|Colin Yule</td>  
<td></td>  
<td></td>  
<td>George William Tilman|Felicity Mary|Michael Harris</td>  
<td>Diana Davenport</td>  
</tr>  
<tr>  
<td>15.5</td>  
<td>The Sicilian Defence</td>  
<td>Edward Stannington|David Farmer</td>  
<td></td>  
<td>Jason Winters</td>  
<td>Reverend Stannington|Vladimir Kostelov|Marion Green|Jack Barton</td>  
<td>Finn Robson</td>  
</tr>  
<tr>  
<td>15.6</td>  
<td>Schooled in Murder</td>  
<td>Debbie Moffett|Oliver Ordish|Gregory Brantner|Helen Caxton</td>  
<td></td>  
<td>Phil Caxton</td>  
<td>Lady Elizabeth Rodney|Jim Caxton</td>  
<td>Beatrix Ordish|Sylvia Mountford</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>16.1</td>  
<td>The Christmas Haunting</td>  
<td>Conor Bridgeman|Ross Clymer</td>  
<td></td>  
<td></td>  
<td>Little Rose Wilton|Mr. Fergus-Johnson</td>  
<td>Ollie Tabori</td>  
</tr>  
<tr>  
<td>16.2</td>  
<td>Let Us Prey</td>  
<td>Nancy Dewar|Frank Dewar|Philip Hamilton</td>  
<td></td>  
<td></td>  
<td>Tina Bell</td>  
<td></td>  
</tr>  
<tr>  
<td>16.3</td>  
<td>Wild Harvest</td>  
<td>Martin Strickland|Amy Strickland</td>  
<td></td>  
<td></td>  
<td>Max Thornfield</td>  
<td>Nick's Stepfather|Ruth Cameron|Johnny Linklater</td>  
</tr>  
<tr>  
<td>16.4</td>  
<td>The Flying Club</td>  
<td>Bernard King|Eddie Rayner</td>  
<td></td>  
<td>Ellie Wingate</td>  
<td>Henry Darnley|Joan Rayner</td>  
<td>Gavin Hopkirk</td>  
</tr>  
<tr>  
<td>16.5</td>  
<td>The Killings of Copenhagen</td>  
<td>Eric Calder|Julian Calder|Ernest Bradley|Atticus Bradley</td>  
<td></td>  
<td></td>  
<td>Agnes Trout</td>  
<td>Armand Stone</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>17.1</td>  
<td>The Dagger Club</td>  
<td>Suzie Colebrooke|Cecilie Petersen|Niall Colebrooke|George Summersbee</td>  
<td></td>  
<td></td>  
<td>Mark Sampson</td>  
<td></td>  
</tr>  
<tr>  
<td>17.2</td>  
<td>Murder by Magic</td>  
<td>Hannah Altman|Andrew Maplin|Annabel Latimer</td>  
<td>Sir Hugo Melmoth</td>  
<td></td>  
<td>Enrico Latimer|William Holt</td>  
<td>Luke Altman</td>  
</tr>  
<tr>  
<td>17.3</td>  
<td>The Ballad of Midsomer County</td>  
<td>Toby Winning|Brian Grey|Frank Wainwright</td>  
<td>Johnny Carver</td>  
<td></td>  
<td></td>  
<td>Claire Asher|DCI John Barnaby|DS Charlie Nelson</td>  
</tr>  
<tr>  
<td>17.4</td>  
<td>A Vintage Murder</td>  
<td>Nadia Simons|Louis Paynton</td>  
<td>Jessica Tyler</td>  
<td></td>  
<td>Mr. Stowe</td>  
<td>Tina Tyler</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>18.1</td>  
<td>Habeas Corpus</td>  
<td></td>  
<td></td>  
<td>Richard Melrose</td>  
<td>Lydia Dryffield|Gregory Lancaster</td>  
<td>Felix Lancaster</td>  
</tr>  
<tr>  
<td>18.2</td>  
<td>The Incident at Cooper Hill</td>  
<td>Felicity Ford|Yasmin Ali|Carter Faulkner</td>  
<td>Eric Tonev</td>  
<td></td>  
<td></td>  
<td></td>  
</tr>  
<tr>  
<td>18.3</td>  
<td>Breaking the Chain</td>  
<td>Greg Eddon|Aiden McCordell</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Des McCordell</td>  
</tr>  
<tr>  
<td>18.4</td>  
<td>A Dying Art</td>  
<td>Brandon Monkford|Tony Pitt|Lance Auden</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Daniel Fargo</td>  
</tr>  
<tr>  
<td>18.5</td>  
<td>Saints and Sinners</td>  
<td>Zoe Dyer|Alex Dyer|Christopher Corby</td>  
<td></td>  
<td></td>  
<td></td>  
<td></td>  
</tr>  
<tr>  
<td>18.6</td>  
<td>Harvest of Souls</td>  
<td>Harry Wyham|Clara Myerscough|Dale Nevins</td>  
<td></td>  
<td></td>  
<td></td>  
<td></td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>19.1</td>  
<td>The Village That Rose from the Dead</td>  
<td>Finn Thornberry|Roderick Craven|Milo Craven</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Julian Lennard</td>  
</tr>  
<tr>  
<td>19.2</td>  
<td>Crime and Punishment</td>  
<td>Angus Colton|Azeem Meer</td>  
<td>Frank Lockston</td>  
<td></td>  
<td></td>  
<td>Barbara Walton</td>  
</tr>  
<tr>  
<td>19.3</td>  
<td>Last Man Out</td>  
<td>Leo Henderson|Fitz Theara</td>  
<td></td>  
<td></td>  
<td></td>  
<td>DI Ben Jones</td>  
</tr>  
<tr>  
<td>19.4</td>  
<td>Red in Tooth &amp; Claw</td>  
<td>Seb Huntington|Cleo Langton</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Errol Judd</td>  
</tr>  
<tr>  
<td>19.5</td>  
<td>Death by Persuasion</td>  
<td>Samantha Berry|Ronin Chow</td>  
<td></td>  
<td></td>  
<td></td>  
<td></td>  
</tr>  
<tr>  
<td>19.6</td>  
<td>The Curse of the Ninth</td>  
<td>Jacob Wheeler|Vernon De Harthog</td>  
<td>The real Jacob Wheeler</td>  
<td></td>  
<td></td>  
<td>Natalie Wheeler</td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>20.1</td>  
<td>The Ghost of Causton Abbey</td>  
<td>Adam Osoba|Emani Taylor</td>  
<td>Brother Jozef</td>  
<td></td>  
<td>Jenny Moss</td>  
<td></td>  
</tr>  
<tr>  
<td>20.2</td>  
<td>Death of the Small Coppers</td>  
<td>Mahesh Sidana|Grady Palmerston</td>  
<td></td>  
<td></td>  
<td>Jane Scott</td>  
<td>DS Jamie Winter</td>  
</tr>  
<tr>  
<td>20.3</td>  
<td>Drawing Dead</td>  
<td>Francesca Lounds|Barrett Lounds</td>  
<td>Conrad Argo</td>  
<td></td>  
<td></td>  
<td>Stella Starling</td>  
</tr>  
<tr>  
<td>20.4</td>  
<td>The Lions of Causton</td>  
<td>Mark Adler|Dominic Braun</td>  
<td></td>  
<td>Neville Gallagher</td>  
<td></td>  
<td></td>  
</tr>  
<tr>  
<td>20.5</td>  
<td>Till Death Do Us Part</td>  
<td>Laurel Newman|Serena Madison|Aisha Khalique</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Sarah Barnaby</td>  
</tr>  
<tr>  
<td>20.6</td>  
<td>Send in the Clowns</td>  
<td>Terry Bellini|Ashley Denton</td>  
<td></td>  
<td></td>  
<td>Curtis Ferabbee</td>  
<td></td>  
</tr>  
]';  
  
   xml_clob := xml_clob || q'[  
<tr>  
<td>21.1</td>  
<td>The Point of Balance</td>  
<td>Rosa Corrigan|Duncan Corrigan</td>  
<td></td>  
<td></td>  
<td></td>  
<td></td>  
</tr>  
<tr>  
<td>21.2</td>  
<td>The Miniature Murders</td>  
<td>Alexander Beauvoisin|Jemima Starling</td>  
<td>Nico Dearden</td>  
<td></td>  
<td>Lara Wokoma</td>  
<td></td>  
</tr>  
<tr>  
<td>21.3</td>  
<td>The Sting of Death</td>  
<td>Dr. Serena Lowe|Cal Ingalls</td>  
<td></td>  
<td></td>  
<td></td>  
<td>Cal Ingalls|Ambrose Deddington</td>  
</tr>  
<tr>  
<td>21.4</td>  
<td>With Baited Breath</td>  
<td>Lex Bedford|Eric Skye|Cornelius Tetbury</td>  
<td></td>  
<td></td>  
<td>Lola Silvermane</td>  
<td>Griffon Twigg|Blaise McQuinn|Artie Blythe</td>  
</tr>  
  
</table>  
]';  
  
   update midsomer_xml  
   set the_xml = xml_clob  
   where id = 1;  
     
   commit;  
end;

Statement processed.

Statement 4

Confirm that the XML in the CLOB can be converted to XMLTYPE and therefore is valid XML.

select   
   substr(xmlserialize(document xmltype(the_xml) indent), 1, 4000) as xml_start   
from midsomer_xml   
where id = 1


XML_START
<table>
  <tr>
    <td>Ep ID</td>
    <td>Title</td>
    <td>Murdered During Episode</td>
    <td>Murdered Prior to Episode</td>
    <td>Death by Suicide</td>
    <td>Accidental or Natural Death</td>
    <td>Attempted Murders or Suicides</td>
  </tr>
  <tr>
    <td>0.1</td>
    <td>The Killings at Badger&apos;s Drift</td>
    <td>Emily Simpson|Dennis Rainbird|Iris Rainbird|Katherine Lacey</td>
    <td>Bella Trace</td>
    <td>Phyllis Cadell|Michael Lacey</td>
    <td>Mr. Lacey|Mrs. Lacey</td>
    <td/>
  </tr>
  <tr>
    <td>1.1</td>
    <td>Written in Blood</td>
    <td>Gerald Hadleigh|Max Jennings</td>
    <td>Mr. Hanlon</td>
    <td/>
    <td>Ralph Lyddiard|Honoria Lyddiard</td>
    <td>Amy Lyddiard</td>
  </tr>
  <tr>
    <td>1.2</td>
    <td>Death of a Hollow Man</td>
    <td>Agnes Gray|Esslyn Carmichael</td>
    <td/>
    <td/>
    <td/>
    <td>Doris Winstanley</td>
  </tr>
  <tr>
    <td>1.3</td>
    <td>Faithful unto Death</td>
    <td>Brenda Buckley|Alan Hollingsworth</td>
    <td/>
    <td/>
    <td>Simone&apos;s mother</td>
    <td/>
  </tr>
  <tr>
    <td>1.4</td>
    <td>Death in Disguise</td>
    <td>Ian Craigie|Guy Gamelin</td>
    <td>Gaius Quintus</td>
    <td/>
    <td>William Carter</td>
    <td>Tim Riley</td>
  </tr>
  <tr>
    <td>2.1</td>
    <td>Death&apos;s Shadow</td>
    <td>Richard Bayly|David Whitely|Simon Fletcher</td>
    <td/>
    <td>Rev. Stephen Wentworth</td>
    <td>Felix Bryce|Jennifer Bryce</td>
    <td/>
  </tr>
  <tr>
    <td>2.2</td>
    <td>Strangler&apos;s Wood</td>
    <td>Carla Constanza|Dr. Ian Aycott|Anna Santarosa|Leonard Pike</td>
    <td>Eva Hoffman|Joan Chaplin|Judith Albiston</td>
    <td/>
    <td/>
    <td>David Merrill|John Merrill</td>
  </tr>
  <tr>
    <td>2.3</td>
    <td>Dead Man&apos;s Eleven</td>
    <td>Tara Cavendish|Charles Jennings</td>
    <td>Emily Beavis</td>
    <td/>
    <td>Matthew Draper|Patricia Smith</td>
    <td>Robert Cavendish</td>
  </tr>
  <tr>
    <td>2.4</td>
    <td>Blood Will Out</td>
    <td>Hector Bridges|John Smith</td>
    <td>Catherine Hamilton</td>
    <td/>
    <td>John Lampson</td>
    <td>Hector Bridges</td>
  </tr>
  <tr>
    <td>3.1</td>
    <td>Death of a Stranger</td>
    <td>Michael Darrow|Ben Gurdie|Ron Pringle|Marcia Tranter</td>
    <td>Simon Tranter</td>
    <td>Grahame Tranter</td>
    <td/>
    <td/>
  </tr>
  <tr>
    <td>3.2</td>
    <td>Blue Herrings</td>
    <td>Celia Armstrong</td>
    <td/>
    <td/>
    <td>Mrs. Mortimer|Muriel Harrap|Madge Fielding</td>
    <td/>
  </tr>
  <tr>
    <td>3.3</td>
    <td>Judgement Day</td>
    <td>Peter Drinkwater|Rosemary Furman|Samantha Johnstone|Bella Devere</td>
    <td>Mrs. Foster</td>
    <td/>
    <td>Michael Weston|Ruth Weston</td>
    <td>Samantha Johnstone</td>
  </tr>
  <tr>
    <td>3.4</td>
    <td>Beyond the Grave</td>
    <td>Marcus Lowrie|James Tate</td>
    <td>Jonathan Lowrie</td>
    <td/>
    <td>David MacKillop</td>
    <td/>
  </tr>
  <tr>
    <td>4.1</td>
    <td>Garden of Death</td>
    <td>Felicity Inkpen-Thomas|Elspeth Inkpen-Thomas|Gerald Bennett</td>
    <td>Cynthia Bennett</td>
    <td/>
    <td/>
    <td>Naomi Inkpen|Augustus Deverell</td>
  </tr>
  <tr>
    <td>4.2</td>
    <td>Destroying Angel</td>
    <td>Gregory Chambers|Tristan Goodfellow|Kenneth Gooders|Suzanna Chambers</td>
    <td>Karl Wainwright</td>
    <td>Evelyn Pope</td>
    <td/>
    <td>Julia Gooders|Clarice Opperman|Evelyn Pope</td>
  </tr>
  <tr>
    <td>4.3</td>
    <td>The Electric Vendetta</td>
    <td>Steve Ramsey|Lloyd Kirby</td>
    <td/>
    <td/>
    <td>Ronald Stokes|Eddie Field|Isabel Aubrey|Dave Ripert</td>
    <td>Christian Aubrey</td>
  </tr>
  <tr>
    <td>4.4</td>
    <td>Who Killed Cock Robin?</td>
    <td>Robin Wooliscroft|Mary Mohan</td>
    <td/>
    <td/>
    <td/>
    <td>Sean O&apos;Connell</td>
  </tr>
  <tr>
    <td>4.5</td>
    <td>Dark Autumn</td>
    <td>Dave Cutler|Debbie Shortlands|Simon Reason|Owen August</td>
    <td/>
    <td>Tammie, the mother of John Field<

Statement 5
Collection type for an unbound number of strings (names in this use-case)
```
CREATE OR REPLACE TYPE string_ct IS TABLE OF VARCHAR2(4000 BYTE);
```
```
Type created.
```

XML_START
<table> <tr> <td>Ep ID</td> <td>Title</td> <td>Murdered During Episode</td> <td>Murdered Prior to Episode</td> <td>Death by Suicide</td> <td>Accidental or Natural Death</td> <td>Attempted Murders or Suicides</td> </tr> <tr> <td>0.1</td> <td>The Killings at Badger's Drift</td> <td>Emily Simpson\|Dennis Rainbird\|Iris Rainbird\|Katherine Lacey</td> <td>Bella Trace</td> <td>Phyllis Cadell\|Michael Lacey</td> <td>Mr. Lacey\|Mrs. Lacey</td> <td/> </tr> <tr> <td>1.1</td> <td>Written in Blood</td> <td>Gerald Hadleigh\|Max Jennings</td> <td>Mr. Hanlon</td> <td/> <td>Ralph Lyddiard\|Honoria Lyddiard</td> <td>Amy Lyddiard</td> </tr> <tr> <td>1.2</td> <td>Death of a Hollow Man</td> <td>Agnes Gray\|Esslyn Carmichael</td> <td/> <td/> <td/> <td>Doris Winstanley</td> </tr> <tr> <td>1.3</td> <td>Faithful unto Death</td> <td>Brenda Buckley\|Alan Hollingsworth</td> <td/> <td/> <td>Simone's mother</td> <td/> </tr> <tr> <td>1.4</td> <td>Death in Disguise</td> <td>Ian Craigie\|Guy Gamelin</td> <td>Gaius Quintus</td> <td/> <td>William Carter</td> <td>Tim Riley</td> </tr> <tr> <td>2.1</td> <td>Death's Shadow</td> <td>Richard Bayly\|David Whitely\|Simon Fletcher</td> <td/> <td>Rev. Stephen Wentworth</td> <td>Felix Bryce\|Jennifer Bryce</td> <td/> </tr> <tr> <td>2.2</td> <td>Strangler's Wood</td> <td>Carla Constanza\|Dr. Ian Aycott\|Anna Santarosa\|Leonard Pike</td> <td>Eva Hoffman\|Joan Chaplin\|Judith Albiston</td> <td/> <td/> <td>David Merrill\|John Merrill</td> </tr> <tr> <td>2.3</td> <td>Dead Man's Eleven</td> <td>Tara Cavendish\|Charles Jennings</td> <td>Emily Beavis</td> <td/> <td>Matthew Draper\|Patricia Smith</td> <td>Robert Cavendish</td> </tr> <tr> <td>2.4</td> <td>Blood Will Out</td> <td>Hector Bridges\|John Smith</td> <td>Catherine Hamilton</td> <td/> <td>John Lampson</td> <td>Hector Bridges</td> </tr> <tr> <td>3.1</td> <td>Death of a Stranger</td> <td>Michael Darrow\|Ben Gurdie\|Ron Pringle\|Marcia Tranter</td> <td>Simon Tranter</td> <td>Grahame Tranter</td> <td/> <td/> </tr> <tr> <td>3.2</td> <td>Blue Herrings</td> <td>Celia Armstrong</td> <td/> <td/> <td>Mrs. Mortimer\|Muriel Harrap\|Madge Fielding</td> <td/> </tr> <tr> <td>3.3</td> <td>Judgement Day</td> <td>Peter Drinkwater\|Rosemary Furman\|Samantha Johnstone\|Bella Devere</td> <td>Mrs. Foster</td> <td/> <td>Michael Weston\|Ruth Weston</td> <td>Samantha Johnstone</td> </tr> <tr> <td>3.4</td> <td>Beyond the Grave</td> <td>Marcus Lowrie\|James Tate</td> <td>Jonathan Lowrie</td> <td/> <td>David MacKillop</td> <td/> </tr> <tr> <td>4.1</td> <td>Garden of Death</td> <td>Felicity Inkpen-Thomas\|Elspeth Inkpen-Thomas\|Gerald Bennett</td> <td>Cynthia Bennett</td> <td/> <td/> <td>Naomi Inkpen\|Augustus Deverell</td> </tr> <tr> <td>4.2</td> <td>Destroying Angel</td> <td>Gregory Chambers\|Tristan Goodfellow\|Kenneth Gooders\|Suzanna Chambers</td> <td>Karl Wainwright</td> <td>Evelyn Pope</td> <td/> <td>Julia Gooders\|Clarice Opperman\|Evelyn Pope</td> </tr> <tr> <td>4.3</td> <td>The Electric Vendetta</td> <td>Steve Ramsey\|Lloyd Kirby</td> <td/> <td/> <td>Ronald Stokes\|Eddie Field\|Isabel Aubrey\|Dave Ripert</td> <td>Christian Aubrey</td> </tr> <tr> <td>4.4</td> <td>Who Killed Cock Robin?</td> <td>Robin Wooliscroft\|Mary Mohan</td> <td/> <td/> <td/> <td>Sean O'Connell</td> </tr> <tr> <td>4.5</td> <td>Dark Autumn</td> <td>Dave Cutler\|Debbie Shortlands\|Simon Reason\|Owen August</td> <td/> <td>Tammie, the mother of John Field<

Statement 6

Object type as container for the string list (names). It adds methods for this collection and provides the user-defined aggregate functions interface (see https://docs.oracle.com/en/database/oracle/oracle-database/19/addci/user-defined-aggregate-functions-interface.html)

CREATE OR REPLACE TYPE string_container_ot AS OBJECT (   
   strlist string_ct,    
   CONSTRUCTOR FUNCTION string_container_ot (   
      in_str IN VARCHAR2   
   ) RETURN SELF AS RESULT,   
   MEMBER FUNCTION countall(   
      SELF IN string_container_ot   
   ) RETURN INTEGER,   
   MEMBER FUNCTION countdistinct(   
      SELF IN string_container_ot   
   ) RETURN INTEGER,   
   MEMBER FUNCTION csv(   
      SELF IN string_container_ot   
   ) RETURN CLOB,   
   STATIC FUNCTION ODCIAggregateInitialize(   
      io_ctx   IN OUT string_container_ot   
   ) RETURN NUMBER,   
   MEMBER FUNCTION ODCIAggregateIterate(   
      SELF     IN OUT string_container_ot,   
      in_value IN     string_container_ot    
   ) RETURN NUMBER,   
   MEMBER FUNCTION ODCIAggregateTerminate(   
      SELF             IN  string_container_ot,   
      out_return_value OUT string_container_ot,   
      in_flags         IN  NUMBER   
   ) RETURN NUMBER,   
   MEMBER FUNCTION ODCIAggregateMerge(   
      SELF   IN OUT string_container_ot,   
      in_ctx IN     string_container_ot   
   ) RETURN NUMBER   
);

Type created.

Statement 7

Implementation using mainly PL/SQL

CREATE OR REPLACE TYPE BODY string_container_ot IS  
   CONSTRUCTOR FUNCTION string_container_ot (  
      in_str IN VARCHAR2  
   ) RETURN SELF AS RESULT IS  
        co_pattern CONSTANT VARCHAR2(10) := '[^|]+';  
   BEGIN  
      strlist := string_ct();  
      IF in_str IS NOT NULL THEN  
         FOR i IN 1 .. regexp_count(in_str, co_pattern) LOOP  
            strlist.extend();  
            strlist(strlist.count) := regexp_substr(in_str, co_pattern, 1, i);  
         END LOOP;  
      END IF;  
      RETURN;  
   END;  
     
   MEMBER FUNCTION countall(  
      SELF IN string_container_ot  
   ) RETURN INTEGER IS  
   BEGIN  
      RETURN strlist.COUNT;  
   END;  
     
   MEMBER FUNCTION countdistinct(  
      SELF IN string_container_ot  
   ) RETURN INTEGER IS  
      l_count INTEGER;  
   BEGIN  
      SELECT COUNT(DISTINCT column_value)  
        INTO l_count  
        FROM TABLE(strlist);   
      RETURN l_count;  
   END;  
  
   MEMBER FUNCTION csv(  
      SELF IN string_container_ot  
   ) RETURN CLOB IS  
      l_csv CLOB;  
   BEGIN  
      dbms_lob.createtemporary(l_csv, TRUE);  
      FOR r IN (  
         SELECT DISTINCT column_value AS str  
           FROM TABLE(strlist)  
          ORDER BY column_value  
      ) LOOP  
         IF dbms_lob.getlength(l_csv) > 0 THEN  
            dbms_lob.append(l_csv, ', ');  
         END IF;  
         dbms_lob.append(l_csv, r.str);             
      END LOOP;  
      RETURN l_csv;  
   END;  
  
   STATIC FUNCTION ODCIAggregateInitialize(  
      io_ctx IN OUT string_container_ot  
   ) RETURN NUMBER IS  
   BEGIN  
     io_ctx := string_container_ot(string_ct());  
     RETURN ODCIConst.Success;  
   END;  
  
   MEMBER FUNCTION ODCIAggregateIterate(  
      SELF     IN OUT string_container_ot,  
      in_value IN     string_container_ot  
   ) RETURN NUMBER IS  
   BEGIN  
      FOR i IN 1 .. in_value.strlist.COUNT LOOP  
         SELF.strlist.EXTEND;  
         SELF.strlist(SELF.strlist.COUNT) := in_value.strlist(i);  
      END LOOP;  
      RETURN ODCIConst.Success;  
   END ODCIAggregateIterate;  
  
   MEMBER FUNCTION ODCIAggregateTerminate(  
      SELF             IN  string_container_ot,  
      out_return_value OUT string_container_ot,  
      in_flags         IN  NUMBER  
   ) RETURN NUMBER IS  
  BEGIN  
    out_return_value := self;  
    RETURN ODCIConst.Success;  
  END ODCIAggregateTerminate;  
  
  MEMBER FUNCTION ODCIAggregateMerge(  
     SELF   IN OUT string_container_ot,  
     in_ctx IN     string_container_ot  
  ) RETURN NUMBER IS  
  BEGIN  
    FOR i IN 1 .. in_ctx.strlist.COUNT  
    LOOP  
       SELF.strlist.EXTEND;  
       SELF.strlist(SELF.strlist.COUNT) := in_ctx.strlist(i);  
    END LOOP;  
    RETURN ODCIConst.Success;  
  END ODCIAggregateMerge;  
END;

Type created.

Statement 8

User-defined aggregate function for string lists (string_container_ot)

CREATE OR REPLACE FUNCTION string_agg (  
   in_obj IN string_container_ot  
) RETURN string_container_ot PARALLEL_ENABLE AGGREGATE USING string_container_ot;

Function created.

Statement 9

Relational view to transform the XML (single row) into an object-relational format (row per episode)

CREATE OR REPLACE VIEW midsomer_obj_rel AS  
WITH  
   base AS (  
      SELECT x.*   
        FROM midsomer_xml t  
       CROSS JOIN XMLTABLE(  
                     '/table/tr[position() > 1]'  
                     PASSING XMLTYPE(t.the_xml)  
                     COLUMNS ep_id                           VARCHAR2(5 BYTE)    PATH '/tr/td[1]',  
                             title                           VARCHAR2(100 CHAR)  PATH '/tr/td[2]',  
                             murdered_during_episode         VARCHAR2(4000 BYTE) PATH '/tr/td[3]',  
                             murdered_prior_to_episode       VARCHAR2(4000 BYTE) PATH '/tr/td[4]',  
                             death_by_suicide                VARCHAR2(4000 BYTE) PATH '/tr/td[5]',  
                             accidental_or_natural_death     VARCHAR2(4000 BYTE) PATH '/tr/td[6]',  
                             attempted_murderers_or_suicides VARCHAR2(4000 BYTE) PATH '/tr/td[7]'  
                             
                  ) x  
   )  
SELECT ep_id,  
       to_number(substr(ep_id, 1, instr(ep_id, '.') - 1))   AS season,  
       to_number(substr(ep_id, instr(ep_id, '.') + 1))      AS episode,  
       title,   
       string_container_ot(murdered_during_episode)         AS murdered_during_episode,  
       string_container_ot(murdered_prior_to_episode)       AS murdered_prior_to_episode,  
       string_container_ot(death_by_suicide)                AS death_by_suicide,  
       string_container_ot(accidental_or_natural_death)     AS accidental_or_natural_death,  
       string_container_ot(attempted_murderers_or_suicides) AS attempted_murderers_or_suicides  
  FROM base

View created.

Statement 10

Result statement 1 via relational view, aggregation with rollup, user-defined aggregate function and UDT methods.

SELECT season,  
       COUNT(DISTINCT ep_id)                                         AS epsiodes,  
       string_agg(t.murdered_during_episode).countall()              AS acts_murder,  
       string_agg(t.murdered_during_episode).countdistinct()         AS persons_murder,  
       string_agg(t.murdered_prior_to_episode).countall()            AS acts_historical,  
       string_agg(t.murdered_prior_to_episode).countdistinct()       AS persons_historical,  
       string_agg(t.death_by_suicide).countall()                     AS acts_suicide,  
       string_agg(t.death_by_suicide).countdistinct()                AS persons_suicide,  
       string_agg(t.accidental_or_natural_death).countall()          AS acts_accident,  
       string_agg(t.accidental_or_natural_death).countdistinct()     AS persons_accident,  
       string_agg(t.attempted_murderers_or_suicides).countall()      AS acts_attempt,  
       string_agg(t.attempted_murderers_or_suicides).countdistinct() AS persons_attempt  
  FROM midsomer_obj_rel t  
 GROUP BY ROLLUP(season)  
 ORDER BY season


SEASON EPSIODES ACTS_MURDER PERSONS_MURDER ACTS_HISTORICAL PERSONS_HISTORICAL ACTS_SUICIDE PERSONS_SUICIDE ACTS_ACCIDENT PERSONS_ACCIDENT ACTS_ATTEMPT PERSONS_ATTEMPT
0 1 4 4 1 1 2 2 2 2 0 0
1 4 8 8 2 2 0 0 4 4 3 3
2 4 11 11 5 5 1 1 5 5 4 4
3 4 11 11 3 3 1 1 6 6 1 1
4 5 15 15 2 2 2 2 4 4 8 8
5 5 15 15 1 1 0 0 4 4 6 6
6 5 14 14 1 1 1 1 4 4 8 7
7 7 19 19 2 2 2 2 7 7 9 9
8 8 21 21 0 0 3 3 3 3 9 7
9 8 15 15 2 2 1 1 6 6 5 5
10 8 17 17 1 1 4 4 3 3 6 6
11 7 20 20 1 1 2 2 13 13 9 9
12 7 16 16 0 0 0 0 5 5 4 4
13 8 21 21 3 3 3 3 12 12 13 10
14 8 18 18 4 4 0 0 5 5 16 11
15 6 18 18 3 3 2 2 16 16 11 11
16 5 13 13 0 0 1 1 7 7 6 6
17 4 12 12 3 3 0 0 4 4 5 5
18 6 14 14 1 1 1 1 2 2 3 3
19 6 13 13 2 2 0 0 0 0 5 5
20 6 13 13 2 2 1 1 3 3 3 3
21 4 9 9 1 1 0 0 2 2 5 5
 - 126 317 317 40 40 27 27 117 117 139 120

23 rows selected.

SEASON	EPSIODES	ACTS_MURDER	PERSONS_MURDER	ACTS_HISTORICAL	PERSONS_HISTORICAL	ACTS_SUICIDE	PERSONS_SUICIDE	ACTS_ACCIDENT	PERSONS_ACCIDENT	ACTS_ATTEMPT	PERSONS_ATTEMPT
0	1	4	4	1	1	2	2	2	2	0	0
1	4	8	8	2	2	0	0	4	4	3	3
2	4	11	11	5	5	1	1	5	5	4	4
3	4	11	11	3	3	1	1	6	6	1	1
4	5	15	15	2	2	2	2	4	4	8	8
5	5	15	15	1	1	0	0	4	4	6	6
6	5	14	14	1	1	1	1	4	4	8	7
7	7	19	19	2	2	2	2	7	7	9	9
8	8	21	21	0	0	3	3	3	3	9	7
9	8	15	15	2	2	1	1	6	6	5	5
10	8	17	17	1	1	4	4	3	3	6	6
11	7	20	20	1	1	2	2	13	13	9	9
12	7	16	16	0	0	0	0	5	5	4	4
13	8	21	21	3	3	3	3	12	12	13	10
14	8	18	18	4	4	0	0	5	5	16	11
15	6	18	18	3	3	2	2	16	16	11	11
16	5	13	13	0	0	1	1	7	7	6	6
17	4	12	12	3	3	0	0	4	4	5	5
18	6	14	14	1	1	1	1	2	2	3	3
19	6	13	13	2	2	0	0	0	0	5	5
20	6	13	13	2	2	1	1	3	3	3	3
21	4	9	9	1	1	0	0	2	2	5	5
-	126	317	317	40	40	27	27	117	117	139	120

Statement 11

Result statement 2, comma separated lists as CLOB, hence no max_string_size limitation for the resulting aggregates.

SELECT season,   
       COUNT(DISTINCT ep_id)                               AS epsiodes,   
       string_agg(t.murdered_during_episode).csv()         AS persons_murder,   
       string_agg(t.murdered_prior_to_episode).csv()       AS persons_historical,   
       string_agg(t.death_by_suicide).csv()                AS persons_suicide,   
       string_agg(t.accidental_or_natural_death).csv()     AS persons_accident,   
       string_agg(t.attempted_murderers_or_suicides).csv() AS persons_attempt   
  FROM midsomer_obj_rel t   
 GROUP BY ROLLUP(season)   
 ORDER BY season


SEASON EPSIODES PERSONS_MURDER PERSONS_HISTORICAL PERSONS_SUICIDE PERSONS_ACCIDENT PERSONS_ATTEMPT
0 1 Dennis Rainbird, Emily Simpson, Iris Rainbird, Katherine Lacey Bella Trace Michael Lacey, Phyllis Cadell Mr. Lacey, Mrs. Lacey  - 
1 4 Agnes Gray, Alan Hollingsworth, Brenda Buckley, Esslyn Carmichael, Gerald Hadleigh, Guy Gamelin, Ian Craigie, Max Jennings Gaius Quintus, Mr. Hanlon  - Honoria Lyddiard, Ralph Lyddiard, Simone's mother, William Carter Amy Lyddiard, Doris Winstanley, Tim Riley
2 4 Anna Santarosa, Carla Constanza, Charles Jennings, David Whitely, Dr. Ian Aycott, Hector Bridges, John Smith, Leonard Pike, Richard Bayly, Simon Fletcher, Tara Cavendish Catherine Hamilton, Emily Beavis, Eva Hoffman, Joan Chaplin, Judith Albiston Rev. Stephen Wentworth Felix Bryce, Jennifer Bryce, John Lampson, Matthew Draper, Patricia Smith David Merrill, Hector Bridges, John Merrill, Robert Cavendish
3 4 Bella Devere, Ben Gurdie, Celia Armstrong, James Tate, Marcia Tranter, Marcus Lowrie, Michael Darrow, Peter Drinkwater, Ron Pringle, Rosemary Furman, Samantha Johnstone Jonathan Lowrie, Mrs. Foster, Simon Tranter Grahame Tranter David MacKillop, Madge Fielding, Michael Weston, Mrs. Mortimer, Muriel Harrap, Ruth Weston Samantha Johnstone
4 5 Dave Cutler, Debbie Shortlands, Elspeth Inkpen-Thomas, Felicity Inkpen-Thomas, Gerald Bennett, Gregory Chambers, Kenneth Gooders, Lloyd Kirby, Mary Mohan, Owen August, Robin Wooliscroft, Simon Reason, Steve Ramsey, Suzanna Chambers, Tristan Goodfellow Cynthia Bennett, Karl Wainwright Evelyn Pope, Tammie, the mother of John Field Dave Ripert, Eddie Field, Isabel Aubrey, Ronald Stokes Augustus Deverell, Christian Aubrey, Clarice Opperman, Evelyn Pope, Julia Gooders, Naomi Inkpen, Sean O'Connell, WPC Jay Nash
5 5 Adam Keyne, Archie Bellingham, Daniel Talbot, Dudley Carew, Emma Tysoe, Ginny Sharp, Greg Tutt, James Harrington, Lady Lavinia Chetwood, Marcus Steadman, Marjorie Empson, Melissa Townsend, Raif Canning, Susan Bartlett, Victoria Bartlett Reverend Jonathan Ebbrell  - Gerald Empson, Neil Laxton, Reggie Barton, Sir. Walter Talbot Frances Le Bon, Jonah Bloxham, Liam Booker, Peter Fogden, Selwyn Proctor, Sir. Anthony Talbot
6 5 Charles Edmonton, Danny Pinchel, Dr. Duncan Goff, Eddie Darwin, Frank Webster, George Hamilton, Gordon Leesmith, Isobel Hewitt, Julian Shepherd, Keith Scholey, Larry Smith, Martin Wroath, Ruth Fairfax, Tony Parish Andy Moore St John Smythe-Webster Ellis Bell, Elsie Pinchel, Kenneth Hewitt, Mrs. Scholey Colin Hawksley, DCI Tom Barnaby, DS Gavin Troy, Mallory Edmonton, Mr. Miller, Mrs. Partridge, Phil Harrison
7 7 Alex Deakin, David Heartley-Reade, Dr. Charles Rust, Dr. James Lavery, Fiona Thompson, Gareth Heldman, Jacob Stokes, Jamie Cruickshank, Jennifer Carter, Jezebel Tripp, Jim Hale, Lillian Webster, Liz Francis, Lydia Villiers, Neville Williams, Richard Rackham, Simon Mayfield, Stephen Bannerman, Steven Curtis Eric Edwards, Roger Heldman Claire English, Ferdy Villiers Conor Maplin, Lettie Edwards, Michael Bannerman, Mr. Monday, Mrs. Cole, Sir Jonathan Haslett, Susan Bannerman Agnes Waterhouse, Alan Clifford, Camilla Crofton, Cully Barnaby, Dr. John Cole, Lydia Villiers, Michael Bannerman, Miriam Heartley-Reade, Sam Callaghan
8 8 Arthur Leggott, Bruce Hartley, Dexter Lockwood, Dr. Gregory Ransom, Dr. John Osgood, Elizabeth Key, Guy Sweetman, Harvey Crane, Henry Plummer, Jack Wilmot, Joanna Craxton, John Ransom, John Whittle, Madeline Villiers, Max Ransom, Mike Spicer, Munro Hilliard, Otto Benham, Owen Swinscoe, Patrick Pennyman, Trevor Machin  - David Key, Rosemary Key, Stanley Lockwood Colonel Hartley, Joan Alder, Maurice Plummer Antonia Wilmot, DCI Tom Barnaby, DS Dan Scott, Little Mal Kirby, Margaret Winstanley, Peter Craxton, Sandra Tate
9 8 Caroline Cave, Cathy Hewlett, Connor Simpson, Dr. Wellow, Frank Hopkirk, Harriet Davis, Henry York, Lady Sandra Butler, Marion Slade, Mark Castle, Martin Barrett, Mildred Danvers, Peter Cave, Rev. Anthony Gant, Sam Judd Frances Trevelyan, P.C. Colin Armstrong Vivienne Marwood Bella Slade, Giles Southerly, Jean Waverley, Mr. Barrett, Mrs. Barrett, Sir Freddy Butler Alistair Gooding, Carolyn Armitage, Jack Magwood, Mrs. Beverley, Sir John Waverley
10 8 Alan King, Dr. Alan Delaney, Eddie Marston, Eileen Carnack, Gwen Morrison, Ian King, Jack Colby, Laura Sharp, Lionel Bell, Miles King, Mimi Clifton, Nick Cheyney, Nicky Harding, Rex Masters, Simon Bright, Steve Bright, Tony Kirby Maria Godbold Gina Colby, Ginger Foxton, Peter Baxter, Sarah Kirkwood Lucinda Thacker, Ralph Wood, Ted Hayward Carol Prentice, Charles King, Dr. James Kirkwood, Ian King, Martin Spellman, Neville Hayward
11 7 Alec Grainger, Aloysius Wilmington, Colin Thomas, Eleanor Crouch, Frederick Tomlin, Guy Sandys, Henry Hammond, Hugo Cartwright, Jack Purdy, James Parkes, Jean Wildacre, Johnny Hammond, Louise Purdy, Lynton Pargeter, Marina Fellowes, Molly Thomas, Peggy Benson, Robin Lawson, Ron Wilson, Terrance Lowther Tommy Hicks Gilly Galsworthy, Mr. Purdy Charlie Finleyson, Colonel Halsey, Cyrus LeVanu, Duggie Hammond, Lady Fitzroy, Libby Wilson, Lord Fitzroy, Lucy Wilmington, Michael Wilson, Mrs. Parkes, Mrs. Wilson's Sister, Peter Thomas, Tristan Balliol Arabella Hammond, Charlotte Knight, DCI Tom Barnaby, DS Ben Jones, Ernest Balliol, Ned Fitzroy, Nesta Goodfellow, Patrick Bradley, Stanley Goodfellow
12 7 Alistair Kingslake, Anthony Prideaux, Christa Palfrey, Daniel Snape, David Roper, Dr. Sylvia Goring, Ed Monkberry, Emily Harte, Felicity Law, Geoffrey Larkin, Graham Spate, Harriet Compton, Jim Hanley, Laurence Mann, Nicky Frazer, Richard Tanner  -  - Grandfather Crisp, Gwendoline Frazer, Harry Godbolt, Mr. Crisp, Mr. Fountain Elizabeth Chettham, George Jeffers, Malcolm Frazer, Miles Tully
13 8 Clifford Bunting, Faye Lennox, Fergal Jenner, Frank Bishop, Geoff Rogers, Gerald Ebbs, Giles Braithwaite, Hugh Dalgleish, Iris Holman, Jack Fincher, Jeff Bowmaker, Jenny Russell, Kitty Pottinger, Leo Fincher, Luke Archbold, Maureen Stubbs, Orlando Guest, Reverend Archie Moreland, Reverend Connor Gregory, Sonia Woodley, The vicar at Badger's Drift An unnamed gardener, Landlord's daughter, Landlord's wife An unnamed landlord, Caroline Maria Roberts, Molly Fielding Caleb Burbage, David Langham, Douglas Stubbs, Gerald Woodley, Magnus, Mr. Barnaby, Mr. Conway, Mrs. Peach, Noah Fincher, Sebastian's Biological Father, Sir Richard Guillaume, The patients from Saint Fidelis Hospital DCI Tom Barnaby, DS Ben Jones, Dawn Stock, Giles Shawcross, Jude Langham, Katie Soper, Kenny Pottinger, Norman Swanscombe, Susan Fincher, Terry Stock
14 8 Alex Preston, Conrad Walker, Dave Doggy Day, Dianne Price, Dr. Markham, Father Behan, Fran Carter, Freddie Raft, Gerry Dawkins, Len Merryman, Leticia Clifford, Mary Bingham, Mother Thomas, Olive Merryman, Patrick Morgan, Peter Slim, Sgt. Trevor Gibson, Thomas Brightwell Carolyn Lambert, Duncan Palmer, Jeremy Lambert, Max Fuller  - Bertie Morell, Dr. Jonathan Frost, Mr. Vertue, Robin Bingham, Sir Anthony Vertue Byron Street, Claire Powell, DCI John Barnaby, DS Ben Jones, Ezra Canning, Jo Starling, Mother Julian, Nina Morgan, Paddy Powell, Silas Trout, William Bingham
15 6 Caroline Garrett, Colin Yule, Cy Davenport, David Farmer, Debbie Moffett, Edward Stannington, Eve Lomax, George Dormer, Giles Harrison, Gregory Brantner, Helen Caxton, Jeremy Harper, Julian DeQuetteville, Keith Mulory, Kyle Gideon, Ludo DeQuetteville, Oliver Ordish, Peter Groves Daniel Denning, Geoffrey DeQuetteville, Mrs. Mulory Jason Winters, Phil Caxton Angela DeQuetteville, Bentham DeQuetteville, Felicity Mary, George William Tilman, Jack Barton, Jim Caxton, Lady Elizabeth Rodney, Marion Green, Mary Dutta, Michael Harris, Miss Felton, Reverend Stannington, Ross, Rupert DeQuetteville, Tom Stanton, Vladimir Kostelov Beatrix Ordish, DCI John Barnaby, DS Ben Jones, Diana Davenport, Finn Robson, Gagan Dutta, Grady Felton, Keith Mulory, Sasha Fleetwood, Sylvia Mountford, Ted Denning
16 5 Amy Strickland, Atticus Bradley, Bernard King, Conor Bridgeman, Eddie Rayner, Eric Calder, Ernest Bradley, Frank Dewar, Julian Calder, Martin Strickland, Nancy Dewar, Philip Hamilton, Ross Clymer  - Ellie Wingate Agnes Trout, Henry Darnley, Joan Rayner, Little Rose Wilton, Max Thornfield, Mr. Fergus-Johnson, Tina Bell Armand Stone, Gavin Hopkirk, Johnny Linklater, Nick's Stepfather, Ollie Tabori, Ruth Cameron
17 4 Andrew Maplin, Annabel Latimer, Brian Grey, Cecilie Petersen, Frank Wainwright, George Summersbee, Hannah Altman, Louis Paynton, Nadia Simons, Niall Colebrooke, Suzie Colebrooke, Toby Winning Jessica Tyler, Johnny Carver, Sir Hugo Melmoth  - Enrico Latimer, Mark Sampson, Mr. Stowe, William Holt Claire Asher, DCI John Barnaby, DS Charlie Nelson, Luke Altman, Tina Tyler
18 6 Aiden McCordell, Alex Dyer, Brandon Monkford, Carter Faulkner, Christopher Corby, Clara Myerscough, Dale Nevins, Felicity Ford, Greg Eddon, Harry Wyham, Lance Auden, Tony Pitt, Yasmin Ali, Zoe Dyer Eric Tonev Richard Melrose Gregory Lancaster, Lydia Dryffield Daniel Fargo, Des McCordell, Felix Lancaster
19 6 Angus Colton, Azeem Meer, Cleo Langton, Finn Thornberry, Fitz Theara, Jacob Wheeler, Leo Henderson, Milo Craven, Roderick Craven, Ronin Chow, Samantha Berry, Seb Huntington, Vernon De Harthog Frank Lockston, The real Jacob Wheeler  -  - Barbara Walton, DI Ben Jones, Errol Judd, Julian Lennard, Natalie Wheeler
20 6 Adam Osoba, Aisha Khalique, Ashley Denton, Barrett Lounds, Dominic Braun, Emani Taylor, Francesca Lounds, Grady Palmerston, Laurel Newman, Mahesh Sidana, Mark Adler, Serena Madison, Terry Bellini Brother Jozef, Conrad Argo Neville Gallagher Curtis Ferabbee, Jane Scott, Jenny Moss DS Jamie Winter, Sarah Barnaby, Stella Starling
21 4 Alexander Beauvoisin, Cal Ingalls, Cornelius Tetbury, Dr. Serena Lowe, Duncan Corrigan, Eric Skye, Jemima Starling, Lex Bedford, Rosa Corrigan Nico Dearden  - Lara Wokoma, Lola Silvermane Ambrose Deddington, Artie Blythe, Blaise McQuinn, Cal Ingalls, Griffon Twigg
 - 126 Adam Keyne, Adam Osoba, Agnes Gray, Aiden McCordell, Aisha Khalique, Alan Hollingsworth, Alan King, Alec Grainger, Alex Deakin, Alex Dyer, Alex Preston, Alexander Beauvoisin, Alistair Kingslake, Aloysius Wilmington, Amy Strickland, Andrew Maplin, Angus Colton, Anna Santarosa, Annabel Latimer, Anthony Prideaux, Archie Bellingham, Arthur Leggott, Ashley Denton, Atticus Bradley, Azeem Meer, Barrett Lounds, Bella Devere, Ben Gurdie, Bernard King, Brandon Monkford, Brenda Buckley, Brian Grey, Bruce Hartley, Cal Ingalls, Carla Constanza, Caroline Cave, Caroline Garrett, Carter Faulkner, Cathy Hewlett, Cecilie Petersen, Celia Armstrong, Charles Edmonton, Charles Jennings, Christa Palfrey, Christopher Corby, Clara Myerscough, Cleo Langton, Clifford Bunting, Colin Thomas, Colin Yule, Connor Simpson, Conor Bridgeman, Conrad Walker, Cornelius Tetbury, Cy Davenport, Dale Nevins, Daniel Snape, Daniel Talbot, Danny Pinchel, Dave Cutler, Dave Doggy Day, David Farmer, David Heartley-Reade, David Roper, David Whitely, Debbie Moffett, Debbie Shortlands, Dennis Rainbird, Dexter Lockwood, Dianne Price, Dominic Braun, Dr. Alan Delaney, Dr. Charles Rust, Dr. Duncan Goff, Dr. Gregory Ransom, Dr. Ian Aycott, Dr. James Lavery, Dr. John Osgood, Dr. Markham, Dr. Serena Lowe, Dr. Sylvia Goring, Dr. Wellow, Dudley Carew, Duncan Corrigan, Ed Monkberry, Eddie Darwin, Eddie Marston, Eddie Rayner, Edward Stannington, Eileen Carnack, Eleanor Crouch, Elizabeth Key, Elspeth Inkpen-Thomas, Emani Taylor, Emily Harte, Emily Simpson, Emma Tysoe, Eric Calder, Eric Skye, Ernest Bradley, Esslyn Carmichael, Eve Lomax, Father Behan, Faye Lennox, Felicity Ford, Felicity Inkpen-Thomas, Felicity Law, Fergal Jenner, Finn Thornberry, Fiona Thompson, Fitz Theara, Fran Carter, Francesca Lounds, Frank Bishop, Frank Dewar, Frank Hopkirk, Frank Wainwright, Frank Webster, Freddie Raft, Frederick Tomlin, Gareth Heldman, Geoff Rogers, Geoffrey Larkin, George Dormer, George Hamilton, George Summersbee, Gerald Bennett, Gerald Ebbs, Gerald Hadleigh, Gerry Dawkins, Giles Braithwaite, Giles Harrison, Ginny Sharp, Gordon Leesmith, Grady Palmerston, Graham Spate, Greg Eddon, Greg Tutt, Gregory Brantner, Gregory Chambers, Guy Gamelin, Guy Sandys, Guy Sweetman, Gwen Morrison, Hannah Altman, Harriet Compton, Harriet Davis, Harry Wyham, Harvey Crane, Hector Bridges, Helen Caxton, Henry Hammond, Henry Plummer, Henry York, Hugh Dalgleish, Hugo Cartwright, Ian Craigie, Ian King, Iris Holman, Iris Rainbird, Isobel Hewitt, Jack Colby, Jack Fincher, Jack Purdy, Jack Wilmot, Jacob Stokes, Jacob Wheeler, James Harrington, James Parkes, James Tate, Jamie Cruickshank, Jean Wildacre, Jeff Bowmaker, Jemima Starling, Jennifer Carter, Jenny Russell, Jeremy Harper, Jezebel Tripp, Jim Hale, Jim Hanley, Joanna Craxton, John Ransom, John Smith, John Whittle, Johnny Hammond, Julian Calder, Julian DeQuetteville, Julian Shepherd, Katherine Lacey, Keith Mulory, Keith Scholey, Kenneth Gooders, Kitty Pottinger, Kyle Gideon, Lady Lavinia Chetwood, Lady Sandra Butler, Lance Auden, Larry Smith, Laura Sharp, Laurel Newman, Laurence Mann, Len Merryman, Leo Fincher, Leo Henderson, Leonard Pike, Leticia Clifford, Lex Bedford, Lillian Webster, Lionel Bell, Liz Francis, Lloyd Kirby, Louis Paynton, Louise Purdy, Ludo DeQuetteville, Luke Archbold, Lydia Villiers, Lynton Pargeter, Madeline Villiers, Mahesh Sidana, Marcia Tranter, Marcus Lowrie, Marcus Steadman, Marina Fellowes, Marion Slade, Marjorie Empson, Mark Adler, Mark Castle, Martin Barrett, Martin Strickland, Martin Wroath, Mary Bingham, Mary Mohan, Maureen Stubbs, Max Jennings, Max Ransom, Melissa Townsend, Michael Darrow, Mike Spicer, Mildred Danvers, Miles King, Milo Craven, Mimi Clifton, Molly Thomas, Mother Thomas, Munro Hilliard, Nadia Simons, Nancy Dewar, Neville Williams, Niall Colebrooke, Nick Cheyney, Nicky Frazer, Nicky Harding, Olive Merryman, Oliver Ordish, Orlando Guest, Otto Benham, Owen August, Owen Swinscoe, Patrick Morgan, Patrick Pennyman, Peggy Benson, Peter Cave, Peter Drinkwater, Peter Groves, Peter Slim, Philip Hamilton, Raif Canning, Rev. Anthony Gant, Reverend Archie Moreland, Reverend Connor Gregory, Rex Masters, Richard Bayly, Richard Rackham, Richard Tanner, Robin Lawson, Robin Wooliscroft, Roderick Craven, Ron Pringle, Ron Wilson, Ronin Chow, Rosa Corrigan, Rosemary Furman, Ross Clymer, Ruth Fairfax, Sam Judd, Samantha Berry, Samantha Johnstone, Seb Huntington, Serena Madison, Sgt. Trevor Gibson, Simon Bright, Simon Fletcher, Simon Mayfield, Simon Reason, Sonia Woodley, Stephen Bannerman, Steve Bright, Steve Ramsey, Steven Curtis, Susan Bartlett, Suzanna Chambers, Suzie Colebrooke, Tara Cavendish, Terrance Lowther, Terry Bellini, The vicar at Badger's Drift, Thomas Brightwell, Toby Winning, Tony Kirby, Tony Parish, Tony Pitt, Trevor Machin, Tristan Goodfellow, Vernon De Harthog, Victoria Bartlett, Yasmin Ali, Zoe Dyer An unnamed gardener, Andy Moore, Bella Trace, Brother Jozef, Carolyn Lambert, Catherine Hamilton, Conrad Argo, Cynthia Bennett, Daniel Denning, Duncan Palmer, Emily Beavis, Eric Edwards, Eric Tonev, Eva Hoffman, Frances Trevelyan, Frank Lockston, Gaius Quintus, Geoffrey DeQuetteville, Jeremy Lambert, Jessica Tyler, Joan Chaplin, Johnny Carver, Jonathan Lowrie, Judith Albiston, Karl Wainwright, Landlord's daughter, Landlord's wife, Maria Godbold, Max Fuller, Mr. Hanlon, Mrs. Foster, Mrs. Mulory, Nico Dearden, P.C. Colin Armstrong, Reverend Jonathan Ebbrell, Roger Heldman, Simon Tranter, Sir Hugo Melmoth, The real Jacob Wheeler, Tommy Hicks An unnamed landlord, Caroline Maria Roberts, Claire English, David Key, Ellie Wingate, Evelyn Pope, Ferdy Villiers, Gilly Galsworthy, Gina Colby, Ginger Foxton, Grahame Tranter, Jason Winters, Michael Lacey, Molly Fielding, Mr. Purdy, Neville Gallagher, Peter Baxter, Phil Caxton, Phyllis Cadell, Rev. Stephen Wentworth, Richard Melrose, Rosemary Key, Sarah Kirkwood, St John Smythe-Webster, Stanley Lockwood, Tammie, the mother of John Field, Vivienne Marwood Agnes Trout, Angela DeQuetteville, Bella Slade, Bentham DeQuetteville, Bertie Morell, Caleb Burbage, Charlie Finleyson, Colonel Halsey, Colonel Hartley, Conor Maplin, Curtis Ferabbee, Cyrus LeVanu, Dave Ripert, David Langham, David MacKillop, Douglas Stubbs, Dr. Jonathan Frost, Duggie Hammond, Eddie Field, Ellis Bell, Elsie Pinchel, Enrico Latimer, Felicity Mary, Felix Bryce, George William Tilman, Gerald Empson, Gerald Woodley, Giles Southerly, Grandfather Crisp, Gregory Lancaster, Gwendoline Frazer, Harry Godbolt, Henry Darnley, Honoria Lyddiard, Isabel Aubrey, Jack Barton, Jane Scott, Jean Waverley, Jennifer Bryce, Jenny Moss, Jim Caxton, Joan Alder, Joan Rayner, John Lampson, Kenneth Hewitt, Lady Elizabeth Rodney, Lady Fitzroy, Lara Wokoma, Lettie Edwards, Libby Wilson, Little Rose Wilton, Lola Silvermane, Lord Fitzroy, Lucinda Thacker, Lucy Wilmington, Lydia Dryffield, Madge Fielding, Magnus, Marion Green, Mark Sampson, Mary Dutta, Matthew Draper, Maurice Plummer, Max Thornfield, Michael Bannerman, Michael Harris, Michael Weston, Michael Wilson, Miss Felton, Mr. Barnaby, Mr. Barrett, Mr. Conway, Mr. Crisp, Mr. Fergus-Johnson, Mr. Fountain, Mr. Lacey, Mr. Monday, Mr. Stowe, Mr. Vertue, Mrs. Barrett, Mrs. Cole, Mrs. Lacey, Mrs. Mortimer, Mrs. Parkes, Mrs. Peach, Mrs. Scholey, Mrs. Wilson's Sister, Muriel Harrap, Neil Laxton, Noah Fincher, Patricia Smith, Peter Thomas, Ralph Lyddiard, Ralph Wood, Reggie Barton, Reverend Stannington, Robin Bingham, Ronald Stokes, Ross, Rupert DeQuetteville, Ruth Weston, Sebastian's Biological Father, Simone's mother, Sir Anthony Vertue, Sir Freddy Butler, Sir Jonathan Haslett, Sir Richard Guillaume, Sir. Walter Talbot, Susan Bannerman, Ted Hayward, The patients from Saint Fidelis Hospital, Tina Bell, Tom Stanton, Tristan Balliol, Vladimir Kostelov, William Carter, William Holt Agnes Waterhouse, Alan Clifford, Alistair Gooding, Ambrose Deddington, Amy Lyddiard, Antonia Wilmot, Arabella Hammond, Armand Stone, Artie Blythe, Augustus Deverell, Barbara Walton, Beatrix Ordish, Blaise McQuinn, Byron Street, Cal Ingalls, Camilla Crofton, Carol Prentice, Carolyn Armitage, Charles King, Charlotte Knight, Christian Aubrey, Claire Asher, Claire Powell, Clarice Opperman, Colin Hawksley, Cully Barnaby, DCI John Barnaby, DCI Tom Barnaby, DI Ben Jones, DS Ben Jones, DS Charlie Nelson, DS Dan Scott, DS Gavin Troy, DS Jamie Winter, Daniel Fargo, David Merrill, Dawn Stock, Des McCordell, Diana Davenport, Doris Winstanley, Dr. James Kirkwood, Dr. John Cole, Elizabeth Chettham, Ernest Balliol, Errol Judd, Evelyn Pope, Ezra Canning, Felix Lancaster, Finn Robson, Frances Le Bon, Gagan Dutta, Gavin Hopkirk, George Jeffers, Giles Shawcross, Grady Felton, Griffon Twigg, Hector Bridges, Ian King, Jack Magwood, Jo Starling, John Merrill, Johnny Linklater, Jonah Bloxham, Jude Langham, Julia Gooders, Julian Lennard, Katie Soper, Keith Mulory, Kenny Pottinger, Liam Booker, Little Mal Kirby, Luke Altman, Lydia Villiers, Malcolm Frazer, Mallory Edmonton, Margaret Winstanley, Martin Spellman, Michael Bannerman, Miles Tully, Miriam Heartley-Reade, Mother Julian, Mr. Miller, Mrs. Beverley, Mrs. Partridge, Naomi Inkpen, Natalie Wheeler, Ned Fitzroy, Nesta Goodfellow, Neville Hayward, Nick's Stepfather, Nina Morgan, Norman Swanscombe, Ollie Tabori, Paddy Powell, Patrick Bradley, Peter Craxton, Peter Fogden, Phil Harrison, Robert Cavendish, Ruth Cameron, Sam Callaghan, Samantha Johnstone, Sandra Tate, Sarah Barnaby, Sasha Fleetwood, Sean O'Connell, Selwyn Proctor, Silas Trout, Sir John Waverley, Sir. Anthony Talbot, Stanley Goodfellow, Stella Starling, Susan Fincher, Sylvia Mountford, Ted Denning, Terry Stock, Tim Riley, Tina Tyler, WPC Jay Nash, William Bingham

23 rows selected.

SEASON	EPSIODES	PERSONS_MURDER	PERSONS_HISTORICAL	PERSONS_SUICIDE	PERSONS_ACCIDENT	PERSONS_ATTEMPT
0	1	Dennis Rainbird, Emily Simpson, Iris Rainbird, Katherine Lacey	Bella Trace	Michael Lacey, Phyllis Cadell	Mr. Lacey, Mrs. Lacey	-
1	4	Agnes Gray, Alan Hollingsworth, Brenda Buckley, Esslyn Carmichael, Gerald Hadleigh, Guy Gamelin, Ian Craigie, Max Jennings	Gaius Quintus, Mr. Hanlon	-	Honoria Lyddiard, Ralph Lyddiard, Simone's mother, William Carter	Amy Lyddiard, Doris Winstanley, Tim Riley
2	4	Anna Santarosa, Carla Constanza, Charles Jennings, David Whitely, Dr. Ian Aycott, Hector Bridges, John Smith, Leonard Pike, Richard Bayly, Simon Fletcher, Tara Cavendish	Catherine Hamilton, Emily Beavis, Eva Hoffman, Joan Chaplin, Judith Albiston	Rev. Stephen Wentworth	Felix Bryce, Jennifer Bryce, John Lampson, Matthew Draper, Patricia Smith	David Merrill, Hector Bridges, John Merrill, Robert Cavendish
3	4	Bella Devere, Ben Gurdie, Celia Armstrong, James Tate, Marcia Tranter, Marcus Lowrie, Michael Darrow, Peter Drinkwater, Ron Pringle, Rosemary Furman, Samantha Johnstone	Jonathan Lowrie, Mrs. Foster, Simon Tranter	Grahame Tranter	David MacKillop, Madge Fielding, Michael Weston, Mrs. Mortimer, Muriel Harrap, Ruth Weston	Samantha Johnstone
4	5	Dave Cutler, Debbie Shortlands, Elspeth Inkpen-Thomas, Felicity Inkpen-Thomas, Gerald Bennett, Gregory Chambers, Kenneth Gooders, Lloyd Kirby, Mary Mohan, Owen August, Robin Wooliscroft, Simon Reason, Steve Ramsey, Suzanna Chambers, Tristan Goodfellow	Cynthia Bennett, Karl Wainwright	Evelyn Pope, Tammie, the mother of John Field	Dave Ripert, Eddie Field, Isabel Aubrey, Ronald Stokes	Augustus Deverell, Christian Aubrey, Clarice Opperman, Evelyn Pope, Julia Gooders, Naomi Inkpen, Sean O'Connell, WPC Jay Nash
5	5	Adam Keyne, Archie Bellingham, Daniel Talbot, Dudley Carew, Emma Tysoe, Ginny Sharp, Greg Tutt, James Harrington, Lady Lavinia Chetwood, Marcus Steadman, Marjorie Empson, Melissa Townsend, Raif Canning, Susan Bartlett, Victoria Bartlett	Reverend Jonathan Ebbrell	-	Gerald Empson, Neil Laxton, Reggie Barton, Sir. Walter Talbot	Frances Le Bon, Jonah Bloxham, Liam Booker, Peter Fogden, Selwyn Proctor, Sir. Anthony Talbot
6	5	Charles Edmonton, Danny Pinchel, Dr. Duncan Goff, Eddie Darwin, Frank Webster, George Hamilton, Gordon Leesmith, Isobel Hewitt, Julian Shepherd, Keith Scholey, Larry Smith, Martin Wroath, Ruth Fairfax, Tony Parish	Andy Moore	St John Smythe-Webster	Ellis Bell, Elsie Pinchel, Kenneth Hewitt, Mrs. Scholey	Colin Hawksley, DCI Tom Barnaby, DS Gavin Troy, Mallory Edmonton, Mr. Miller, Mrs. Partridge, Phil Harrison
7	7	Alex Deakin, David Heartley-Reade, Dr. Charles Rust, Dr. James Lavery, Fiona Thompson, Gareth Heldman, Jacob Stokes, Jamie Cruickshank, Jennifer Carter, Jezebel Tripp, Jim Hale, Lillian Webster, Liz Francis, Lydia Villiers, Neville Williams, Richard Rackham, Simon Mayfield, Stephen Bannerman, Steven Curtis	Eric Edwards, Roger Heldman	Claire English, Ferdy Villiers	Conor Maplin, Lettie Edwards, Michael Bannerman, Mr. Monday, Mrs. Cole, Sir Jonathan Haslett, Susan Bannerman	Agnes Waterhouse, Alan Clifford, Camilla Crofton, Cully Barnaby, Dr. John Cole, Lydia Villiers, Michael Bannerman, Miriam Heartley-Reade, Sam Callaghan
8	8	Arthur Leggott, Bruce Hartley, Dexter Lockwood, Dr. Gregory Ransom, Dr. John Osgood, Elizabeth Key, Guy Sweetman, Harvey Crane, Henry Plummer, Jack Wilmot, Joanna Craxton, John Ransom, John Whittle, Madeline Villiers, Max Ransom, Mike Spicer, Munro Hilliard, Otto Benham, Owen Swinscoe, Patrick Pennyman, Trevor Machin	-	David Key, Rosemary Key, Stanley Lockwood	Colonel Hartley, Joan Alder, Maurice Plummer	Antonia Wilmot, DCI Tom Barnaby, DS Dan Scott, Little Mal Kirby, Margaret Winstanley, Peter Craxton, Sandra Tate
9	8	Caroline Cave, Cathy Hewlett, Connor Simpson, Dr. Wellow, Frank Hopkirk, Harriet Davis, Henry York, Lady Sandra Butler, Marion Slade, Mark Castle, Martin Barrett, Mildred Danvers, Peter Cave, Rev. Anthony Gant, Sam Judd	Frances Trevelyan, P.C. Colin Armstrong	Vivienne Marwood	Bella Slade, Giles Southerly, Jean Waverley, Mr. Barrett, Mrs. Barrett, Sir Freddy Butler	Alistair Gooding, Carolyn Armitage, Jack Magwood, Mrs. Beverley, Sir John Waverley
10	8	Alan King, Dr. Alan Delaney, Eddie Marston, Eileen Carnack, Gwen Morrison, Ian King, Jack Colby, Laura Sharp, Lionel Bell, Miles King, Mimi Clifton, Nick Cheyney, Nicky Harding, Rex Masters, Simon Bright, Steve Bright, Tony Kirby	Maria Godbold	Gina Colby, Ginger Foxton, Peter Baxter, Sarah Kirkwood	Lucinda Thacker, Ralph Wood, Ted Hayward	Carol Prentice, Charles King, Dr. James Kirkwood, Ian King, Martin Spellman, Neville Hayward
11	7	Alec Grainger, Aloysius Wilmington, Colin Thomas, Eleanor Crouch, Frederick Tomlin, Guy Sandys, Henry Hammond, Hugo Cartwright, Jack Purdy, James Parkes, Jean Wildacre, Johnny Hammond, Louise Purdy, Lynton Pargeter, Marina Fellowes, Molly Thomas, Peggy Benson, Robin Lawson, Ron Wilson, Terrance Lowther	Tommy Hicks	Gilly Galsworthy, Mr. Purdy	Charlie Finleyson, Colonel Halsey, Cyrus LeVanu, Duggie Hammond, Lady Fitzroy, Libby Wilson, Lord Fitzroy, Lucy Wilmington, Michael Wilson, Mrs. Parkes, Mrs. Wilson's Sister, Peter Thomas, Tristan Balliol	Arabella Hammond, Charlotte Knight, DCI Tom Barnaby, DS Ben Jones, Ernest Balliol, Ned Fitzroy, Nesta Goodfellow, Patrick Bradley, Stanley Goodfellow
12	7	Alistair Kingslake, Anthony Prideaux, Christa Palfrey, Daniel Snape, David Roper, Dr. Sylvia Goring, Ed Monkberry, Emily Harte, Felicity Law, Geoffrey Larkin, Graham Spate, Harriet Compton, Jim Hanley, Laurence Mann, Nicky Frazer, Richard Tanner	-	-	Grandfather Crisp, Gwendoline Frazer, Harry Godbolt, Mr. Crisp, Mr. Fountain	Elizabeth Chettham, George Jeffers, Malcolm Frazer, Miles Tully
13	8	Clifford Bunting, Faye Lennox, Fergal Jenner, Frank Bishop, Geoff Rogers, Gerald Ebbs, Giles Braithwaite, Hugh Dalgleish, Iris Holman, Jack Fincher, Jeff Bowmaker, Jenny Russell, Kitty Pottinger, Leo Fincher, Luke Archbold, Maureen Stubbs, Orlando Guest, Reverend Archie Moreland, Reverend Connor Gregory, Sonia Woodley, The vicar at Badger's Drift	An unnamed gardener, Landlord's daughter, Landlord's wife	An unnamed landlord, Caroline Maria Roberts, Molly Fielding	Caleb Burbage, David Langham, Douglas Stubbs, Gerald Woodley, Magnus, Mr. Barnaby, Mr. Conway, Mrs. Peach, Noah Fincher, Sebastian's Biological Father, Sir Richard Guillaume, The patients from Saint Fidelis Hospital	DCI Tom Barnaby, DS Ben Jones, Dawn Stock, Giles Shawcross, Jude Langham, Katie Soper, Kenny Pottinger, Norman Swanscombe, Susan Fincher, Terry Stock
14	8	Alex Preston, Conrad Walker, Dave Doggy Day, Dianne Price, Dr. Markham, Father Behan, Fran Carter, Freddie Raft, Gerry Dawkins, Len Merryman, Leticia Clifford, Mary Bingham, Mother Thomas, Olive Merryman, Patrick Morgan, Peter Slim, Sgt. Trevor Gibson, Thomas Brightwell	Carolyn Lambert, Duncan Palmer, Jeremy Lambert, Max Fuller	-	Bertie Morell, Dr. Jonathan Frost, Mr. Vertue, Robin Bingham, Sir Anthony Vertue	Byron Street, Claire Powell, DCI John Barnaby, DS Ben Jones, Ezra Canning, Jo Starling, Mother Julian, Nina Morgan, Paddy Powell, Silas Trout, William Bingham
15	6	Caroline Garrett, Colin Yule, Cy Davenport, David Farmer, Debbie Moffett, Edward Stannington, Eve Lomax, George Dormer, Giles Harrison, Gregory Brantner, Helen Caxton, Jeremy Harper, Julian DeQuetteville, Keith Mulory, Kyle Gideon, Ludo DeQuetteville, Oliver Ordish, Peter Groves	Daniel Denning, Geoffrey DeQuetteville, Mrs. Mulory	Jason Winters, Phil Caxton	Angela DeQuetteville, Bentham DeQuetteville, Felicity Mary, George William Tilman, Jack Barton, Jim Caxton, Lady Elizabeth Rodney, Marion Green, Mary Dutta, Michael Harris, Miss Felton, Reverend Stannington, Ross, Rupert DeQuetteville, Tom Stanton, Vladimir Kostelov	Beatrix Ordish, DCI John Barnaby, DS Ben Jones, Diana Davenport, Finn Robson, Gagan Dutta, Grady Felton, Keith Mulory, Sasha Fleetwood, Sylvia Mountford, Ted Denning
16	5	Amy Strickland, Atticus Bradley, Bernard King, Conor Bridgeman, Eddie Rayner, Eric Calder, Ernest Bradley, Frank Dewar, Julian Calder, Martin Strickland, Nancy Dewar, Philip Hamilton, Ross Clymer	-	Ellie Wingate	Agnes Trout, Henry Darnley, Joan Rayner, Little Rose Wilton, Max Thornfield, Mr. Fergus-Johnson, Tina Bell	Armand Stone, Gavin Hopkirk, Johnny Linklater, Nick's Stepfather, Ollie Tabori, Ruth Cameron
17	4	Andrew Maplin, Annabel Latimer, Brian Grey, Cecilie Petersen, Frank Wainwright, George Summersbee, Hannah Altman, Louis Paynton, Nadia Simons, Niall Colebrooke, Suzie Colebrooke, Toby Winning	Jessica Tyler, Johnny Carver, Sir Hugo Melmoth	-	Enrico Latimer, Mark Sampson, Mr. Stowe, William Holt	Claire Asher, DCI John Barnaby, DS Charlie Nelson, Luke Altman, Tina Tyler
18	6	Aiden McCordell, Alex Dyer, Brandon Monkford, Carter Faulkner, Christopher Corby, Clara Myerscough, Dale Nevins, Felicity Ford, Greg Eddon, Harry Wyham, Lance Auden, Tony Pitt, Yasmin Ali, Zoe Dyer	Eric Tonev	Richard Melrose	Gregory Lancaster, Lydia Dryffield	Daniel Fargo, Des McCordell, Felix Lancaster
19	6	Angus Colton, Azeem Meer, Cleo Langton, Finn Thornberry, Fitz Theara, Jacob Wheeler, Leo Henderson, Milo Craven, Roderick Craven, Ronin Chow, Samantha Berry, Seb Huntington, Vernon De Harthog	Frank Lockston, The real Jacob Wheeler	-	-	Barbara Walton, DI Ben Jones, Errol Judd, Julian Lennard, Natalie Wheeler
20	6	Adam Osoba, Aisha Khalique, Ashley Denton, Barrett Lounds, Dominic Braun, Emani Taylor, Francesca Lounds, Grady Palmerston, Laurel Newman, Mahesh Sidana, Mark Adler, Serena Madison, Terry Bellini	Brother Jozef, Conrad Argo	Neville Gallagher	Curtis Ferabbee, Jane Scott, Jenny Moss	DS Jamie Winter, Sarah Barnaby, Stella Starling
21	4	Alexander Beauvoisin, Cal Ingalls, Cornelius Tetbury, Dr. Serena Lowe, Duncan Corrigan, Eric Skye, Jemima Starling, Lex Bedford, Rosa Corrigan	Nico Dearden	-	Lara Wokoma, Lola Silvermane	Ambrose Deddington, Artie Blythe, Blaise McQuinn, Cal Ingalls, Griffon Twigg
-	126	Adam Keyne, Adam Osoba, Agnes Gray, Aiden McCordell, Aisha Khalique, Alan Hollingsworth, Alan King, Alec Grainger, Alex Deakin, Alex Dyer, Alex Preston, Alexander Beauvoisin, Alistair Kingslake, Aloysius Wilmington, Amy Strickland, Andrew Maplin, Angus Colton, Anna Santarosa, Annabel Latimer, Anthony Prideaux, Archie Bellingham, Arthur Leggott, Ashley Denton, Atticus Bradley, Azeem Meer, Barrett Lounds, Bella Devere, Ben Gurdie, Bernard King, Brandon Monkford, Brenda Buckley, Brian Grey, Bruce Hartley, Cal Ingalls, Carla Constanza, Caroline Cave, Caroline Garrett, Carter Faulkner, Cathy Hewlett, Cecilie Petersen, Celia Armstrong, Charles Edmonton, Charles Jennings, Christa Palfrey, Christopher Corby, Clara Myerscough, Cleo Langton, Clifford Bunting, Colin Thomas, Colin Yule, Connor Simpson, Conor Bridgeman, Conrad Walker, Cornelius Tetbury, Cy Davenport, Dale Nevins, Daniel Snape, Daniel Talbot, Danny Pinchel, Dave Cutler, Dave Doggy Day, David Farmer, David Heartley-Reade, David Roper, David Whitely, Debbie Moffett, Debbie Shortlands, Dennis Rainbird, Dexter Lockwood, Dianne Price, Dominic Braun, Dr. Alan Delaney, Dr. Charles Rust, Dr. Duncan Goff, Dr. Gregory Ransom, Dr. Ian Aycott, Dr. James Lavery, Dr. John Osgood, Dr. Markham, Dr. Serena Lowe, Dr. Sylvia Goring, Dr. Wellow, Dudley Carew, Duncan Corrigan, Ed Monkberry, Eddie Darwin, Eddie Marston, Eddie Rayner, Edward Stannington, Eileen Carnack, Eleanor Crouch, Elizabeth Key, Elspeth Inkpen-Thomas, Emani Taylor, Emily Harte, Emily Simpson, Emma Tysoe, Eric Calder, Eric Skye, Ernest Bradley, Esslyn Carmichael, Eve Lomax, Father Behan, Faye Lennox, Felicity Ford, Felicity Inkpen-Thomas, Felicity Law, Fergal Jenner, Finn Thornberry, Fiona Thompson, Fitz Theara, Fran Carter, Francesca Lounds, Frank Bishop, Frank Dewar, Frank Hopkirk, Frank Wainwright, Frank Webster, Freddie Raft, Frederick Tomlin, Gareth Heldman, Geoff Rogers, Geoffrey Larkin, George Dormer, George Hamilton, George Summersbee, Gerald Bennett, Gerald Ebbs, Gerald Hadleigh, Gerry Dawkins, Giles Braithwaite, Giles Harrison, Ginny Sharp, Gordon Leesmith, Grady Palmerston, Graham Spate, Greg Eddon, Greg Tutt, Gregory Brantner, Gregory Chambers, Guy Gamelin, Guy Sandys, Guy Sweetman, Gwen Morrison, Hannah Altman, Harriet Compton, Harriet Davis, Harry Wyham, Harvey Crane, Hector Bridges, Helen Caxton, Henry Hammond, Henry Plummer, Henry York, Hugh Dalgleish, Hugo Cartwright, Ian Craigie, Ian King, Iris Holman, Iris Rainbird, Isobel Hewitt, Jack Colby, Jack Fincher, Jack Purdy, Jack Wilmot, Jacob Stokes, Jacob Wheeler, James Harrington, James Parkes, James Tate, Jamie Cruickshank, Jean Wildacre, Jeff Bowmaker, Jemima Starling, Jennifer Carter, Jenny Russell, Jeremy Harper, Jezebel Tripp, Jim Hale, Jim Hanley, Joanna Craxton, John Ransom, John Smith, John Whittle, Johnny Hammond, Julian Calder, Julian DeQuetteville, Julian Shepherd, Katherine Lacey, Keith Mulory, Keith Scholey, Kenneth Gooders, Kitty Pottinger, Kyle Gideon, Lady Lavinia Chetwood, Lady Sandra Butler, Lance Auden, Larry Smith, Laura Sharp, Laurel Newman, Laurence Mann, Len Merryman, Leo Fincher, Leo Henderson, Leonard Pike, Leticia Clifford, Lex Bedford, Lillian Webster, Lionel Bell, Liz Francis, Lloyd Kirby, Louis Paynton, Louise Purdy, Ludo DeQuetteville, Luke Archbold, Lydia Villiers, Lynton Pargeter, Madeline Villiers, Mahesh Sidana, Marcia Tranter, Marcus Lowrie, Marcus Steadman, Marina Fellowes, Marion Slade, Marjorie Empson, Mark Adler, Mark Castle, Martin Barrett, Martin Strickland, Martin Wroath, Mary Bingham, Mary Mohan, Maureen Stubbs, Max Jennings, Max Ransom, Melissa Townsend, Michael Darrow, Mike Spicer, Mildred Danvers, Miles King, Milo Craven, Mimi Clifton, Molly Thomas, Mother Thomas, Munro Hilliard, Nadia Simons, Nancy Dewar, Neville Williams, Niall Colebrooke, Nick Cheyney, Nicky Frazer, Nicky Harding, Olive Merryman, Oliver Ordish, Orlando Guest, Otto Benham, Owen August, Owen Swinscoe, Patrick Morgan, Patrick Pennyman, Peggy Benson, Peter Cave, Peter Drinkwater, Peter Groves, Peter Slim, Philip Hamilton, Raif Canning, Rev. Anthony Gant, Reverend Archie Moreland, Reverend Connor Gregory, Rex Masters, Richard Bayly, Richard Rackham, Richard Tanner, Robin Lawson, Robin Wooliscroft, Roderick Craven, Ron Pringle, Ron Wilson, Ronin Chow, Rosa Corrigan, Rosemary Furman, Ross Clymer, Ruth Fairfax, Sam Judd, Samantha Berry, Samantha Johnstone, Seb Huntington, Serena Madison, Sgt. Trevor Gibson, Simon Bright, Simon Fletcher, Simon Mayfield, Simon Reason, Sonia Woodley, Stephen Bannerman, Steve Bright, Steve Ramsey, Steven Curtis, Susan Bartlett, Suzanna Chambers, Suzie Colebrooke, Tara Cavendish, Terrance Lowther, Terry Bellini, The vicar at Badger's Drift, Thomas Brightwell, Toby Winning, Tony Kirby, Tony Parish, Tony Pitt, Trevor Machin, Tristan Goodfellow, Vernon De Harthog, Victoria Bartlett, Yasmin Ali, Zoe Dyer	An unnamed gardener, Andy Moore, Bella Trace, Brother Jozef, Carolyn Lambert, Catherine Hamilton, Conrad Argo, Cynthia Bennett, Daniel Denning, Duncan Palmer, Emily Beavis, Eric Edwards, Eric Tonev, Eva Hoffman, Frances Trevelyan, Frank Lockston, Gaius Quintus, Geoffrey DeQuetteville, Jeremy Lambert, Jessica Tyler, Joan Chaplin, Johnny Carver, Jonathan Lowrie, Judith Albiston, Karl Wainwright, Landlord's daughter, Landlord's wife, Maria Godbold, Max Fuller, Mr. Hanlon, Mrs. Foster, Mrs. Mulory, Nico Dearden, P.C. Colin Armstrong, Reverend Jonathan Ebbrell, Roger Heldman, Simon Tranter, Sir Hugo Melmoth, The real Jacob Wheeler, Tommy Hicks	An unnamed landlord, Caroline Maria Roberts, Claire English, David Key, Ellie Wingate, Evelyn Pope, Ferdy Villiers, Gilly Galsworthy, Gina Colby, Ginger Foxton, Grahame Tranter, Jason Winters, Michael Lacey, Molly Fielding, Mr. Purdy, Neville Gallagher, Peter Baxter, Phil Caxton, Phyllis Cadell, Rev. Stephen Wentworth, Richard Melrose, Rosemary Key, Sarah Kirkwood, St John Smythe-Webster, Stanley Lockwood, Tammie, the mother of John Field, Vivienne Marwood	Agnes Trout, Angela DeQuetteville, Bella Slade, Bentham DeQuetteville, Bertie Morell, Caleb Burbage, Charlie Finleyson, Colonel Halsey, Colonel Hartley, Conor Maplin, Curtis Ferabbee, Cyrus LeVanu, Dave Ripert, David Langham, David MacKillop, Douglas Stubbs, Dr. Jonathan Frost, Duggie Hammond, Eddie Field, Ellis Bell, Elsie Pinchel, Enrico Latimer, Felicity Mary, Felix Bryce, George William Tilman, Gerald Empson, Gerald Woodley, Giles Southerly, Grandfather Crisp, Gregory Lancaster, Gwendoline Frazer, Harry Godbolt, Henry Darnley, Honoria Lyddiard, Isabel Aubrey, Jack Barton, Jane Scott, Jean Waverley, Jennifer Bryce, Jenny Moss, Jim Caxton, Joan Alder, Joan Rayner, John Lampson, Kenneth Hewitt, Lady Elizabeth Rodney, Lady Fitzroy, Lara Wokoma, Lettie Edwards, Libby Wilson, Little Rose Wilton, Lola Silvermane, Lord Fitzroy, Lucinda Thacker, Lucy Wilmington, Lydia Dryffield, Madge Fielding, Magnus, Marion Green, Mark Sampson, Mary Dutta, Matthew Draper, Maurice Plummer, Max Thornfield, Michael Bannerman, Michael Harris, Michael Weston, Michael Wilson, Miss Felton, Mr. Barnaby, Mr. Barrett, Mr. Conway, Mr. Crisp, Mr. Fergus-Johnson, Mr. Fountain, Mr. Lacey, Mr. Monday, Mr. Stowe, Mr. Vertue, Mrs. Barrett, Mrs. Cole, Mrs. Lacey, Mrs. Mortimer, Mrs. Parkes, Mrs. Peach, Mrs. Scholey, Mrs. Wilson's Sister, Muriel Harrap, Neil Laxton, Noah Fincher, Patricia Smith, Peter Thomas, Ralph Lyddiard, Ralph Wood, Reggie Barton, Reverend Stannington, Robin Bingham, Ronald Stokes, Ross, Rupert DeQuetteville, Ruth Weston, Sebastian's Biological Father, Simone's mother, Sir Anthony Vertue, Sir Freddy Butler, Sir Jonathan Haslett, Sir Richard Guillaume, Sir. Walter Talbot, Susan Bannerman, Ted Hayward, The patients from Saint Fidelis Hospital, Tina Bell, Tom Stanton, Tristan Balliol, Vladimir Kostelov, William Carter, William Holt	Agnes Waterhouse, Alan Clifford, Alistair Gooding, Ambrose Deddington, Amy Lyddiard, Antonia Wilmot, Arabella Hammond, Armand Stone, Artie Blythe, Augustus Deverell, Barbara Walton, Beatrix Ordish, Blaise McQuinn, Byron Street, Cal Ingalls, Camilla Crofton, Carol Prentice, Carolyn Armitage, Charles King, Charlotte Knight, Christian Aubrey, Claire Asher, Claire Powell, Clarice Opperman, Colin Hawksley, Cully Barnaby, DCI John Barnaby, DCI Tom Barnaby, DI Ben Jones, DS Ben Jones, DS Charlie Nelson, DS Dan Scott, DS Gavin Troy, DS Jamie Winter, Daniel Fargo, David Merrill, Dawn Stock, Des McCordell, Diana Davenport, Doris Winstanley, Dr. James Kirkwood, Dr. John Cole, Elizabeth Chettham, Ernest Balliol, Errol Judd, Evelyn Pope, Ezra Canning, Felix Lancaster, Finn Robson, Frances Le Bon, Gagan Dutta, Gavin Hopkirk, George Jeffers, Giles Shawcross, Grady Felton, Griffon Twigg, Hector Bridges, Ian King, Jack Magwood, Jo Starling, John Merrill, Johnny Linklater, Jonah Bloxham, Jude Langham, Julia Gooders, Julian Lennard, Katie Soper, Keith Mulory, Kenny Pottinger, Liam Booker, Little Mal Kirby, Luke Altman, Lydia Villiers, Malcolm Frazer, Mallory Edmonton, Margaret Winstanley, Martin Spellman, Michael Bannerman, Miles Tully, Miriam Heartley-Reade, Mother Julian, Mr. Miller, Mrs. Beverley, Mrs. Partridge, Naomi Inkpen, Natalie Wheeler, Ned Fitzroy, Nesta Goodfellow, Neville Hayward, Nick's Stepfather, Nina Morgan, Norman Swanscombe, Ollie Tabori, Paddy Powell, Patrick Bradley, Peter Craxton, Peter Fogden, Phil Harrison, Robert Cavendish, Ruth Cameron, Sam Callaghan, Samantha Johnstone, Sandra Tate, Sarah Barnaby, Sasha Fleetwood, Sean O'Connell, Selwyn Proctor, Silas Trout, Sir John Waverley, Sir. Anthony Talbot, Stanley Goodfellow, Stella Starling, Susan Fincher, Sylvia Mountford, Ted Denning, Terry Stock, Tim Riley, Tina Tyler, WPC Jay Nash, William Bingham

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

SEASON	EPSIODES	ACTS_MURDER	PERSONS_MURDER	ACTS_HISTORICAL	PERSONS_HISTORICAL	ACTS_SUICIDE	PERSONS_SUICIDE	ACTS_ACCIDENT	PERSONS_ACCIDENT	ACTS_ATTEMPT	PERSONS_ATTEMPT
0	1	4	4	1	1	2	2	2	2	0	0
1	4	8	8	2	2	0	0	4	4	3	3
2	4	11	11	5	5	1	1	5	5	4	4
3	4	11	11	3	3	1	1	6	6	1	1
4	5	15	15	2	2	2	2	4	4	8	8
5	5	15	15	1	1	0	0	4	4	6	6
6	5	14	14	1	1	1	1	4	4	8	7
7	7	19	19	2	2	2	2	7	7	9	9
8	8	21	21	0	0	3	3	3	3	9	7
9	8	15	15	2	2	1	1	6	6	5	5
10	8	17	17	1	1	4	4	3	3	6	6
11	7	20	20	1	1	2	2	13	13	9	9
12	7	16	16	0	0	0	0	5	5	4	4
13	8	21	21	3	3	3	3	12	12	13	10
14	8	18	18	4	4	0	0	5	5	16	11
15	6	18	18	3	3	2	2	16	16	11	11
16	5	13	13	0	0	1	1	7	7	6	6
17	4	12	12	3	3	0	0	4	4	5	5
18	6	14	14	1	1	1	1	2	2	3	3
19	6	13	13	2	2	0	0	0	0	5	5
20	6	13	13	2	2	1	1	3	3	3	3
21	4	9	9	1	1	0	0	2	2	5	5
-	126	317	317	40	40	27	27	117	117	139	120

SEASON	EPSIODES	ACTS_MURDER	PERSONS_MURDER	ACTS_HISTORICAL	PERSONS_HISTORICAL	ACTS_SUICIDE	PERSONS_SUICIDE	ACTS_ACCIDENT	PERSONS_ACCIDENT	ACTS_ATTEMPT	PERSONS_ATTEMPT
0	1	4	4	1	1	2	2	2	2	0	0
1	4	8	8	2	2	0	0	4	4	3	3
2	4	11	11	5	5	1	1	5	5	4	4
3	4	11	11	3	3	1	1	6	6	1	1
4	5	15	15	2	2	2	2	4	4	8	8
5	5	15	15	1	1	0	0	4	4	6	6
6	5	14	14	1	1	1	1	4	4	8	7
7	7	19	19	2	2	2	2	7	7	9	9
8	8	21	21	0	0	3	3	3	3	9	7
9	8	15	15	2	2	1	1	6	6	5	5
10	8	17	17	1	1	4	4	3	3	6	6
11	7	20	20	1	1	2	2	13	13	9	9
12	7	16	16	0	0	0	0	5	5	4	4
13	8	21	21	3	3	3	3	12	12	13	10
14	8	18	18	4	4	0	0	5	5	16	11
15	6	18	18	3	3	2	2	16	16	11	11
16	5	13	13	0	0	1	1	7	7	6	6
17	4	12	12	3	3	0	0	4	4	5	5
18	6	14	14	1	1	1	1	2	2	3	3
19	6	13	13	2	2	0	0	0	0	5	5
20	6	13	13	2	2	1	1	3	3	3	3
21	4	9	9	1	1	0	0	2	2	5	5
-	126	317	317	40	40	27	27	117	117	139	120

SEASON	EPSIODES	ACTS_MURDER	PERSONS_MURDER	ACTS_HISTORICAL	PERSONS_HISTORICAL	ACTS_SUICIDE	PERSONS_SUICIDE	ACTS_ACCIDENT	PERSONS_ACCIDENT	ACTS_ATTEMPT	PERSONS_ATTEMPT
0	1	4	4	1	1	2	2	2	2	0	0
1	4	8	8	2	2	0	0	4	4	3	3
2	4	11	11	5	5	1	1	5	5	4	4
3	4	11	11	3	3	1	1	6	6	1	1
4	5	15	15	2	2	2	2	4	4	8	8
5	5	15	15	1	1	0	0	4	4	6	6
6	5	14	14	1	1	1	1	4	4	8	7
7	7	19	19	2	2	2	2	7	7	9	9
8	8	21	21	0	0	3	3	3	3	9	7
9	8	15	15	2	2	1	1	6	6	5	5
10	8	17	17	1	1	4	4	3	3	6	6
11	7	20	20	1	1	2	2	13	13	9	9
12	7	16	16	0	0	0	0	5	5	4	4
13	8	21	21	3	3	3	3	12	12	13	10
14	8	18	18	4	4	0	0	5	5	16	11
15	6	18	18	3	3	2	2	16	16	11	11
16	5	13	13	0	0	1	1	7	7	6	6
17	4	12	12	3	3	0	0	4	4	5	5
18	6	14	14	1	1	1	1	2	2	3	3
19	6	13	13	2	2	0	0	0	0	5	5
20	6	13	13	2	2	1	1	3	3	3	3
21	4	9	9	1	1	0	0	2	2	5	5
-	126	317	317	40	40	27	27	117	117	139	120