Update of /cvsroot/monetdb/sql/src/test/xml
In directory sc8-pr-cvs16.sourceforge.net:/tmp/cvs-serv22807/src/test/xml
Added Files:
Tag: GDK-2
JackTheRipper.xml docload.sql load.sql publish.sql
serialize.sql
Log Message:
propagated changes of Saturday Aug 18 2007 - Tuesday Aug 21 2007
from the development trunk to the GDK-2 branch
--- NEW FILE: publish.sql ---
-- The simple examples of SQL/XML publishing functions
-- and an indication of the code to be derived from SQL
-- assume simple string production and bottom up construction.
select xmlelement(name "victim", V.name || ' ' || V.dob ) as result
from victim V;
-- SQL code snippet
-- _15 := sql.resultSet(1,1,_14);
-- sql.rsColumn(_15,"sys.","concat_concat_name","clob",1,0,_14);
-- sql.exportResult(_15,"");
-- SQL/XML code snippet
-- _97 := xml.xml(_14); # coercion to xml value
-- _98 := xml.element("victim",nil:bat,nil:bat,_97);
-- _99 := xml.str(_98); # coercion to string for export
-- _15 := sql.resultSet(1,1,_14);
-- sql.rsColumn(_15,"sys.","result","str",1,0,_99);
select xmlelement(name "victim",
xmlattributes(V.name, V.dob ) )
from victim V;
-- SQL code snippet
-- _12 := sql.resultSet(2,1,_9);
-- sql.rsColumn(_12,"sys.v","name","clob",0,0,_9);
-- sql.rsColumn(_12,"sys.v","dob","clob",0,0,_11);
-- SQL/XML code snippet
-- _96 := xml.attribute("name",_9);
-- _97 := xml.attribute("dob",_11);
-- _98 := xml.attributes(_96,_97);
-- _99 := xml.element("victim",nil:bat,_98,nil:bat);
-- _00 := xml.str(_99);
-- _12 := sql.resultSet(1,1,_9);
-- sql.rsColumn(_12,"sys","result","str",0,0,_00);
select xmlelement(name "victim",
xmlelement(name "name", V.name ),
xmlelement(name "birthday", V.dob ) )
from victim V;
-- _94:= xml.xml(_8);
-- _95:= xml.xml(_9);
-- _96:= xml.element("name",nil:bat,nil:bat,_94);
-- _97:= xml.element("birthday",nil:bat,nil:bat,_95);
-- _98:= xml.element("victim",nil:bat,nil:bat, _96, _97);
-- _99:= xml.str(_98);
-- sql.rsColumn(_12,"sys","result","str",0,0,_99);
select xmlelement(name "victim",
xmlelement(name "name", V.name ),
xmlelement(name "hairtype",
(select hair from victims where name=V.name)))
from victim V;
-- identical to previous one after evaluatoin of subquery
-- it may produce multiple answers though
select xmlconcat(
xmlelement(name "name", V.name ),
xmlelement(name "birthday", V.dob ) )
from victim V;
-- _94 := xml.xml(_8);
-- _95 := xml.xml(_9);
-- _96 := xml.element("name",nil:bat,nil:bat,_94);
-- _97 := xml.element("birthday",nil:bat,nil:bat,_95);
-- _98 := xml.concat(_96,97);
-- _99 := xml.str(_98);
-- sql.rsColumn(_12,"sys","result","str",0,0,_99);
select xmlelement(name "victims",
xmlforest( V.name, V.dob as "birthday")
)
from victim V;
-- _94 := xml.xml(_8);
-- _95 := xml.xml(_9);
-- _96 := xml.trunk("birthday",_95);
-- _97 := xml.forest(_95,96);
-- _98 := xml.element("victims",nil:bat,nil:bat,_97);
-- _99 := xml.str(_98);
-- sql.rsColumn(_12,"sys","result","str",0,0,_99);
-- group by hair color
select xmlelement(name "jtr",
xmlattributes(V.hair as "hair"),
xmlagg( xmlelement("victim", V.name))
)
from victim V;
select xmlelment(name "jtr",
xmlforest(
xmlelement(name="dossier",
xmlattributes(V.hair as "hair"),
xmlagg( xmlelement(name="person", V.name)),
)
xmlelement(name="total", (select count(*) from victim))
))
from victim V;
-- escape to XQuery
select xmlgen(<person id="{$name"} dob="{$dob}"</person>)
from victim;
--- NEW FILE: serialize.sql ---
select xmlserialize( document,
xmlroot(
xmlforest(
xmlelement(name="id", V.name) as string,
xmlcomment( "a real victim")
),
version "versioninfo",
standalone no value
) )
from victim V;
--- NEW FILE: JackTheRipper.xml ---
<?xml version="1.0" encoding="UTF-8"?>
<JackTheRipper>
<scene>
<victim>
<name>Mary Ann Walker</name>
<date-of-birth>1845-08-26</date-of-birth>
<length>5'12"</length>
<eyes> brown</eyes>
<hair>brown hair turning grey</hair>
<date>1888-08-31</date>
<location>Buck's Row by Charles Cross</location>
<picture>http://www.casebook.org/images/victims_nichols.jpg</picture>
<features>
five front teeth missing (Rumbelow); two
bottom-one top
front (Fido), her teeth are slightly
discoloured. She is
described as having small, delicate features
with high
cheekbones and grey eyes. She has a small scar
on her
forehead from a childhood injury. She is
described by
Emily Holland as "a very clean woman who always
seemed
[...1082 lines suppressed...]
<picture>http://www.casebook.org/images/police_war.jpg</picture>
</inspector>
<inspector>
<name>P.C. Edward Watkins</name>
<picture>http://www.casebook.org/images/police_edward_watkins.jpg</picture>
</inspector>
<inspector>
<name> Sergeant Stephen White</name>
<picture>http://www.casebook.org/images/police_steven_white.jpg</picture>
</inspector>
<inspector>
<name>Chief Constable Adolphus Frederick Williamson</name>
<picture>http://www.casebook.org/images/police_frederick_williamson.jpg</picture>
</inspector>
</JackTheRipper>
--- NEW FILE: load.sql ---
-- The Jack The Ripper case is used in different shredding formats
-- as the basis for functional testing the SQL/XML features or MonetDB
-- Here we create some relational tables as a basis for XML output rendering
create table victim(
name string,
dob string,
length string,
eyes string,
hair string,
crime string,
place string,
picture string,
features string);
insert into victim values(
'Mary Ann Walker',
'1845-08-26',
'5\'12"',
'brown',
'brown hair turning grey',
'1999-08-31',
'Buck\'s Row by Charles Cross',
'http://www.casebook.org/images/victims_nichols.jpg',
'five front teeth missing (Rumbelow); two bottom-one top '
'front (Fido), her teeth are slightly discoloured. She is '
'described as having small, delicate features with high '
'cheekbones and grey eyes. She has a small scar on her '
'forehead from a childhood injury. She is described by '
'Emily Holland as "a very clean woman who always seemed '
'to keep to herself." The doctor at the post mortem '
'remarked on the cleanliness of her thighs. She is also '
'an alcoholic.');
insert into victim values(
'Annie Chapman',
'1841-09',
'5\'',
'blue',
'dark brown, wavy',
'1888-09-08',
'29 Hanbury Street',
'http://www.casebook.org/images/victims_chapman.jpg',
'Pallid complexion, excellent teeth (possibly two '
'missing in lower jaw), strongly built (stout), thick nose');
insert into victim values(
'Elisabeth Stride',
'1843-11-27',
'5\'5"',
'light gray',
'curly dark brown',
'1888-09-30',
'Berner Street (Henriques Street today)',
'http://www.casebook.org/images/victims_stride.jpg',
'pale complexion, all the teeth in her lower left '
'jaw were missing');
insert into victim values(
'Catherine Eddowes',
'1842-04-14',
'5\'',
'hazel',
'dark auburn',
'1888-09-30',
'Mitre Square',
'http://www.casebook.org/images/eddowes1.jpg',
'She has a tattoo in blue ink on her left forearm "TC."');
insert into victim values(
'Mary Jane Kelly',
'around 1863',
'5\'7"',
'blue',
'blonde',
'1888-11-09',
'13 Miller\'s Court',
'http://www.casebook.org/images/victims_kelly.jpg',
'a fair complexion. "Said to have been possessed of considerable '
'personal attractions." (McNaughten) She was last seen wearing a '
'linsey frock and a red shawl pulled around her shoulders. She was '
'bare headed. Detective Constable Walter Dew claimed to know Kelly '
'well by sight and says that she was attractive and paraded around, '
'usually in the company of two or three friends. He says she always '
'wore a spotlessly clean white apron. '
'Maria Harvey, a friend, says that she was "much superior to that '
'of most persons in her position in life."');
insert into victim values(
'Fairy Fay',
null,
null,
null,
null,
'1887-12-26',
'the alleys of Commercial Road',
'http://www.casebook.org/images/victims_fairy.jpg',
null);
insert into victim values(
'Annie Millwood',
'1850',
null,
null,
null,
'1888-02-15',
'White\'s Row, Spitalfields',
'http://www.casebook.org/images/victims_millwood.jpg',
null);
insert into victim values(
'Ada Wilson',
null,
null,
null,
null,
'(survived the attack on 1888-28-03)',
'19 Maidman Street',
'http://www.casebook.org/images/victims_wilson.jpg',
null);
insert into victim values(
'Emma Smith',
'1843',
null,
null,
null,
'1888-03-03',
'just outside Taylor Brothers Mustard and Cocoa Mill '
'which was on the north-east corner of the Wentworth/Old '
'Montague Street crossroads',
'http://www.casebook.org/images/victims_smith.jpg',
null);
insert into victim values(
'Martha Tabram',
'1849-05-10',
null,
null,
null,
'1888-08-07',
'George Yard, a narrow north-south alley connecting Wentworth '
'Street and Whitechapel High Street',
'http://www.casebook.org/images/victims_tabram.jpg',
null);
insert into victim values(
'Whitehall Mystery',
null,
null,
null,
null,
'1888-10-03',
'a vault soon to become a section of the cellar of New Scotland Yard',
'http://www.casebook.org/images/victims_whitehall.jpg',
'the headless and limbless torso of a woman');
insert into victim values(
'Annie Farmer',
'1848',
null,
null,
null,
'(survived the attack on 1888-11-20)',
null,
'http://www.casebook.org/images/victims_farmer.jpg',
null);
insert into victim values(
'Rose Mylett',
'1862',
null,
null,
null,
'1888-12-20',
'the yard between 184 and 186 Poplar High Street, in Clarke\'s Yard',
'http://www.casebook.org/images/victims_mylett.jpg',
null);
insert into victim values(
'Elisabeth Jackson',
null,
null,
null,
null,
'1889-06',
'the Thames ',
'http://www.casebook.org/images/victims_jackson.jpg',
null);
insert into victim values(
'Alice MacKenzie',
'1849',
null,
null,
null,
'1889-07-17',
'Castle Alley',
'http://www.casebook.org/images/victims_mckenzie.jpg',
'as a freckle-faced woman with a penchant for both smoke '
'and drink. Her left thumb was also injured in what was '
'no doubt some sort of industrial accident.');
insert into victim values(
'Pinchin Street Murder, possibly Lydia Hart',
null,
null,
null,
null,
'1889-09-10',
'under a railway arch in Pinchin Street',
'http://www.casebook.org/images/victims_pinchin.jpg',
'body, missing both head and legs');
insert into victim values(
'Frances Coles',
'1865',
null,
null,
null,
'1891-02-13',
'Swallow Gardens',
'http://www.casebook.org/images/victims_coles.jpg',
'is often heralded as the prettiest of all the murder victims');
insert into victim values(
'Carrie Brown',
null,
'5\'8"',
null,
null,
'1891-04-24',
'the room of the East River Hotel on the Manhattan waterfront '
'of New York, U.S.A.',
'http://www.casebook.org/images/victims_brown.jpg',
null);
--- NEW FILE: docload.sql ---
-- The Jack The Ripper case is used in different shredding formats
-- as the basis for functional testing the SQL/XML features or MonetDB
-- check against standard!
create table archive1( doc xml );
create table archive2( doc xml document );
create table archive3( doc xml sequence );
copy into archive1 from 'JackTheRipper.xml';
insert into archive2 values(XMLparse( document 'JackTheRipper.xml'));
insert into archive values(XMLvalidate( document 'JackTheRipper.xml'));
create table chapters( scene xml); -- to contain a tuple per scene (18)
copy into archive from 'JackTheRipper.xml' delimiter 'scene';
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
