4. Coding the Export
Kris Southward from the English Heritage IT Development
Centre was tasked to investigate the means of getting the desired data into the
MIDAS XML schema format. Two options were considered: manual coding using PL/SQL
to create the files, or use of the available ORACLE XML Developers Kit.
Kris comments….
“In the end I didn't actually use any of the Oracle tools to
produce the XML output. The XML Developers Kit (XDK) provided as part of
Oracle Database 8.1.7 was too limited to be able to produce the file we
needed. From 9i Release 2 onwards the XDK has become far better and
may have enabled the file to be produced. With 8.1.7 developers kit, I
had to create several object types on the database, product and object view and
could not attribute the elements. The object types that were required to
produce the file in MIDAS format were too complex and the performance of the
object view was absolutely terrible (this was when I only had less than half
the required elements covered). The complexity of the types was also
making the code less maintainable that manual PL/SQL code, so I finally
had the write the export manually using PL/SQL. 8.1.7 XDK would have been
OK was a simple XML export for 2-3 tables say. Once our databases have
been upgraded then future XML exports could probably be achieved through Oracle
XDK or using the new XML functions that are available in PL/SQL. “.
Allowing time for several rounds of discussion and update to
the requirement, the export was completed within a couple of weeks. It has
subsequently been integrated into the forms used for management of the
database, so that exports in MIDAS XML format can be produced by users as
required.
Kris further comments (March 2005)
“Recently Phil Guest [English Heritage Database Manager] has
been testing oracle 10G database. I had a quick go at creating the export
as one giant SQL query using the new XML SQL functions and it is impressive and
relatively simple. I only wrote part of the export, but it ran extremely
efficiently unlike when using the limited functionality of 8i. It also
allows attributes and is quite simple to get the correct indentation.
People with 9i Rel 2 upwards databases should definitely use the SQL XML
features for XML extracts instead of the manual approach we took here.
The 10G database also has an XML Type, which means you can load an XML document
into a database column, and also validate it for (a) being a valid XML document
and (b) against an XML schema, which obviously means you can easily test your output.”
5. PL/SQL script used for LBS
export
Clearly the export routine for each system will be
different, reflecting the structure of each database. The routine used in this
instance is documented in detail here to illustrate the approach used.
CREATE OR REPLACE PACKAGE PK_EH_XML_EXPORTS IS
/*
Created By: Kristen Southward
Date: 25
Nov 2004
Purpose: Package to contain
procedures and functions to be used to generate XML output
*/
l_clob clob;
l_record_count NUMBER;
l_query_text VARCHAR2(4000);
FUNCTION FN_GETLASTXMLLENGTH
RETURN NUMBER;
FUNCTION
FN_GETLASTXMLSUBSTR(p_amount number, p_offset number) RETURN VARCHAR2;
FUNCTION
FN_GETLASTXMLRECORDCOUNT RETURN NUMBER;
PROCEDURE
PR_SET_QUERY_TEXT(i_query_text IN VARCHAR2);
PROCEDURE
PR_LBS_MIDAS_EXPORT(i_qry_uid IN qry_hit_list.qry_uid%TYPE);
END;
CREATE OR REPLACE PACKAGE BODY PK_EH_XML_EXPORTS IS
PROCEDURE PR_SET_QUERY_TEXT
(i_query_text IN VARCHAR2) IS
/*
Set the values of packages variable l_query_text. Required as forms cannot access this var
directly.
*/
BEGIN
l_query_text := i_query_text;
END;
PROCEDURE PR_LBS_MIDAS_EXPORT
(i_qry_uid IN qry_hit_list.qry_uid%TYPE)
IS
/*
Produces the LBS XML Export (output based on MIDAS XML schema).
Produces XML output for list entries identfied in qry_hit_list for the
passed in qry_uid.
The XML output is held in the package variable l_clob and can then be
access by calling form using then function
fn_getlastxmlsubstr (needed because 6i forms do not handle clob pl/sql
variables very well.
The output is generated manually rather tahn using dbms_xmlquery - this
is more flexible and efficient than trying to
use XML functionality of 8i XDK which is far less powerful tah 9i R2 and
above XDK.
*/
--constants to be used in the
procedure
-- File header that is
outputed at the top of the file
cn_fileheader CONSTANT
VARCHAR2(200) := '<?xml version = "1.0"
encoding="ISO-8859-1"?>';
--Tag used to enclose all
records
cn_encltag CONSTANT
VARCHAR2(200) := 'monuments';
--text to be used for
indentation
cn_indent_char CONSTANT
VARCHAR2(200) := ' ';
--Date format
to use
cn_date_format CONSTANT
VARCHAR2(30) := 'DD-MON-YYYY';
l_output_line
VARCHAR2(4000);
--variables and cursors used
for metadata
l_rec_count NUMBER;
CURSOR cu_get_rec_count IS
SELECT COUNT(*)
FROM qry_hit_list
WHERE qry_uid = i_qry_uid;
CURSOR cu_get_namedplace IS
SELECT DISTINCT p.county
FROM parishes p,
admin_locations al,
qry_hit_list q
WHERE q.qry_uid = i_qry_uid
AND al.list_entry_uid = q.list_entry_uid
AND p.loc_uid = al.loc_uid;
l_max_northing
nat_grid_refs.northing%TYPE;
l_min_northing
nat_grid_refs.northing%TYPE;
l_max_easting
nat_grid_refs.easting%TYPE;
l_min_easting
nat_grid_refs.easting%TYPE;
CURSOR
cu_get_spatial_coverage IS
SELECT MAX(easting)
max_easting,
MIN(easting)
min_easting,
MAX(northing) max_northing,
MIN(northing)
min_northing
FROM qry_hit_list q,
nat_grid_refs ngr
WHERE q.qry_uid = i_qry_uid
AND ngr.list_entry_uid = q.list_entry_uid;
--Boolean variables to use to
determine if certain tags have been outputted yet
bln_record_meta_tag_set
BOOLEAN := FALSE;
bln_appellation_tag_set
BOOLEAN := FALSE;
bln_characters_tag_set
BOOLEAN := FALSE;
bln_materials_tag_set
BOOLEAN := FALSE;
bln_type_tag_set BOOLEAN := FALSE;
bln_spatial_tag_set BOOLEAN := FALSE;
bln_place_tag_set BOOLEAN := FALSE;
bln_namedplace_tag_set BOOLEAN := FALSE;
bln_spatialappellation_tag_set BOOLEAN :=
FALSE;
bln_geometry_tag_set BOOLEAN := FALSE;
v_counter NUMBER := 0;
v_when DATE;
v_who
lbs_online_audit.whoby%TYPE;
--Main cursor used to select
all the building record to be included
--Joins to qry_hit_list as
this table contains a list of UIDs to be included (set by calling form)
CURSOR cu_get_listing IS
SELECT le.list_entry_uid,
le.grade,
le.date_listed,
lt.listing_text
FROM qry_hit_list qry,
listing_text lt,
list_entries le
WHERE qry.qry_uid = i_qry_uid
AND le.list_entry_uid = qry.list_entry_uid
AND lt.list_entry_uid (+) = le.list_entry_uid;
--Cursor to extract record
creation data
CURSOR
cu_get_record_create_data(i_list_entry_uid list_entries.list_entry_uid%TYPE) IS
SELECT
aud."WHEN",
aud.whoby
FROM lbs_online_audit aud
WHERE aud.list_entry_uid = i_list_entry_uid
AND aud.audit_type = 'A';
--Cursor to extract record
updation data
CURSOR
cu_get_record_update_data(i_list_entry_uid list_entries.list_entry_uid%TYPE) IS
SELECT
aud."WHEN",
aud.whoby
FROM lbs_online_audit aud
WHERE aud.list_entry_uid = i_list_entry_uid
AND aud.audit_type = 'M'
AND aud."WHEN" = (SELECT
MAX(aud2."WHEN")
FROM lbs_online_audit aud2
WHERE aud2.list_entry_uid =
i_list_entry_uid
AND aud2.audit_type = 'M');
--Cursor to extract building
names
CURSOR cu_get_name
(i_list_entry_uid list_entries.list_entry_uid%TYPE) IS
SELECT DISTINCT name,
DECODE(type_flag, 'A', 'Alternate', 'S', 'Current', 'F', 'Former', 'Unknown')
type, DECODE(type_flag, 'S', 'True', 'False') preferred, DECODE(type_flag, 'S',
1, 'A', 2, 'F', 3) orderby
FROM list_addresses
WHERE list_entry_uid = i_list_entry_uid
ORDER BY DECODE(type_flag,
'S', 1, 'A', 2, 'F', 3);
--Cursor to extract manual
references
CURSOR cu_get_manual_refs
(i_list_entry_uid list_entries.list_entry_uid%TYPE) IS
SELECT le.vol_number||',
'||TO_CHAR(idn.map_sheet_number)||', '||idn.item_number identifier, 'Manual'
Type, 'LB Volume, Map, Item No.' namespace
FROM id_numbers idn, list_entries le
WHERE le.list_entry_uid = i_list_entry_uid
AND idn.list_entry_uid (+) = le.list_entry_uid;
--Cursor to extract building
CDP locations
CURSOR cu_get_locations
(i_list_entry_uid list_entries.list_entry_uid%TYPE) IS
SELECT al.locality,
p.parish,
p.district,
p.county,
p.ceremonial,
p.parish_flag
FROM parishes p,
admin_locations al
WHERE al.list_entry_uid = i_list_entry_uid
AND p.loc_uid (+) = al.loc_uid;
--Cursor to extract national
park in which the building sits
CURSOR cu_get_nat_park
(i_list_entry_uid list_entries.list_entry_uid%TYPE) IS
SELECT
NVL(lu.char60_value, 'UnSpecified') nat_park
FROM look_up_table lu,
nat_grid_refs ng
WHERE ng.list_entry_uid = i_list_entry_uid
AND lu.lut_uid = ng.nat_park_uid
AND lu.class_uid = 50;
--Cursor to extract building
addresses
CURSOR cu_get_addresses
(i_list_entry_uid list_entries.list_entry_uid%TYPE) IS
SELECT la.street_number ||
' ' || la.street_name streetaddress,
la.side_of_street
directions,
null city,
null county,
null postcode,
'statutory' type
FROM list_addresses la
WHERE la.list_entry_uid = i_list_entry_uid
AND la.type_flag = 'C'
UNION ALL
SELECT pa.line_1 || ' '|| pa.line_2 || ' ' ||
pa.line_3 || ', ' || pa.line_4 streetaddress,
null directions,
pa.line_5 city,
pa.line_6 county,
pa.post_code
postcode,
'postal' type
FROM
postal_addresses pa
WHERE pa.list_entry_uid = i_list_entry_uid;
--Cursor to extract building
NGR
CURSOR cu_get_ngr
(i_list_entry_uid list_entries.list_entry_uid%TYPE) IS
SELECT ng.ngr
FROM nat_grid_refs ng
WHERE ng.list_entry_uid = i_list_entry_uid;
--Cursor to extract building
absolute reference
CURSOR cu_get_abs
(i_list_entry_uid list_entries.list_entry_uid%TYPE) IS
SELECT ng.easting,
ng.northing,
ng.ngr_comments,
lu.char60_value
capturemethod
FROM look_up_table lu,
nat_grid_refs ng
WHERE ng.list_entry_uid = i_list_entry_uid
AND lu.char3_value (+) = ng.route
AND lu.class_uid (+) = 51;
--Cursor to extract building
type data
CURSOR
cu_get_monument_types (i_list_entry_uid
list_entries.list_entry_uid%TYPE) IS
SELECT tt.term,
cl.display_date,
cl.min_date,
cl.max_date
FROM
thesaurus_terms tt,
classifications cl
WHERE cl.list_entry_uid = i_list_entry_uid
AND tt.the_te_uid = cl.term_uid;
--Cursor to extract building
material data
CURSOR cu_get_materials
(i_list_entry_uid list_entries.list_entry_uid%TYPE) IS
SELECT lu.char60_value
material,
DECODE(m.material_type, 'M', 'Main', 'C', 'Covering', 'Not Specified')
type
FROM look_up_table lu,
materials m
WHERE m.list_entry_uid = i_list_entry_uid
AND lu.lut_uid = m.material_code;
FUNCTION indent(i_level IN
NUMBER) RETURN VARCHAR2 IS
/* This function returns a
varchar2 strign that is prefixed to data outputted to give an indetation based
on the level parameter*/
BEGIN
RETURN RPAD(' ', 3*i_level,' ');
END;
PROCEDURE clobWRITE
(i_output_line VARCHAR2) IS
/* This procedure adds the
passed in text to the l_clob package variable*/
BEGIN
dbms_lob.write(l_clob,
length(i_output_line), dbms_lob.getlength(l_clob)+1,i_output_line);
END;
BEGIN
l_record_count := 0;
--initize the clob variable
DBMS_LOB.CREATETEMPORARY
(l_clob , TRUE);
--output file header type data
clobwrite(cn_fileheader||chr(10));
clobwrite('<'||cn_encltag||'
xmlns="http://www.heritage-standards.org/midas/schema/1.0" '
||chr(10));
clobwrite('xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
'|| chr(10));
clobwrite('xsi:schemaLocation="http://www.heritage-standards.org/midas/schema/1.0'||chr(10));
clobwrite('http://195.74.122.210/~fish/midas/schema/1.0/midas_monument.xsd">'||chr(10));
--Set File Meta Data
clobwrite(indent(1)||'<meta>'||chr(10));
clobwrite(indent(2)||'<title>Listed Building System data in MIDAS
XML format</title>'||chr(10));
clobwrite(indent(2)||'<subject>heritage</subject>'||chr(10));
clobwrite(indent(2)||'<keywords>listed
buildings</keywords>'||chr(10));
OPEN cu_get_rec_count;
FETCH cu_get_rec_count
INTO l_rec_count;
CLOSE cu_get_rec_count;
clobwrite(indent(2)||'<contacts>'||chr(10));
clobwrite(indent(3)||'<contact>'||chr(10));
clobwrite(indent(4)||'<name>'||chr(10));
clobwrite(indent(5)||'<organisation>English Heritage National
Monuments Record</organisation>'||chr(10));
clobwrite(indent(4)||'</name>'||chr(10));
clobwrite(indent(4)||'<address>'||chr(10));
clobwrite(indent(5)||'<streetaddress>National Monuments Record
Centre, Kemble Drive</streetaddress>'||chr(10));
clobwrite(indent(5)||'<city>Swindon</city>'||chr(10));
clobwrite(indent(5)||'<country>United Kingdom</country>'||chr(10));
clobwrite(indent(4)||'</address>'||chr(10));
clobwrite(indent(3)||'</contact>'||chr(10));
clobwrite(indent(2)||'</contacts>'||chr(10));
clobwrite(indent(2)||'<rights>'||chr(10));
clobwrite(indent(3)||'<copyright>'||chr(10));
--Next line © =
copyright symbol
clobwrite(indent(4)||'<statement>(c) Crown
Copyright</statement>'||chr(10));
clobwrite(indent(3)||'</copyright>'||chr(10));
clobwrite(indent(3)||'<accessrights>'||chr(10));
clobwrite(indent(4)||'<conditions>Use subject to Click-use
license. See
http://www.hmso.gov.uk/click-use-home.htm</conditions>'||chr(10));
clobwrite(indent(3)||'</accessrights>'||chr(10));
clobwrite(indent(2)||'</rights>'||chr(10));
clobwrite(indent(2)||'<source>'||chr(10));
clobwrite(indent(3)||'<statement>English Heritage Listed Building
System</statement>'||chr(10));
clobwrite(indent(2)||'</source>'||chr(10));
clobwrite(indent(2)||'<creation>'||chr(10));
clobwrite(indent(3)||'<createdon>'||TO_CHAR(sysdate,
cn_date_format)||'</createdon>'||chr(10));
clobwrite(indent(3)||'<query>'||NVL(l_query_text,
'Not specified')||'</query>'||chr(10));
clobwrite(indent(2)||'</creation>'||chr(10));
clobwrite(indent(2)||'<coverage>'||chr(10));
clobwrite(indent(3)||'<spatial>'||chr(10));
clobwrite(indent(4)||'<place>'||chr(10));
clobwrite(indent(5)||'<namedplace>'||chr(10));
FOR crec_places IN
cu_get_namedplace LOOP
clobwrite(indent(6)||'<location type="Administrative County"
namespace="EH_CDP98">'||crec_places.county||'</location>'||chr(10));
END LOOP;
clobwrite(indent(5)||'</namedplace>'||chr(10));
clobwrite(indent(5)||'<geopolitical type="Country">England</geopolitical>'||chr(10));
clobwrite(indent(4)||'</place>'||chr(10));
clobwrite(indent(4)||'<geometry>'||chr(10));
--get the boundingbox
OPEN
cu_get_spatial_coverage;
FETCH
cu_get_spatial_coverage
INTO l_max_easting,
l_min_easting, l_max_northing, l_min_northing;
CLOSE
cu_get_spatial_coverage;
clobwrite(indent(5)||'<boundingBox
srs="EPSG:27700" minx="'||TO_CHAR(l_min_easting)||'"
miny="'||TO_CHAR(l_min_northing)||'"
maxx="'||TO_CHAR(l_max_easting)||'"
maxy="'||TO_CHAR(l_max_northing)||'" />'||chr(10));
clobwrite(indent(4)||'</geometry>'||chr(10));
clobwrite(indent(3)||'</spatial>'||chr(10));
clobwrite(indent(2)||'</coverage>'||chr(10));
clobwrite(indent(2)||'<abstract>'||TO_CHAR(l_rec_count)||' Listed
Building System records in MIDAS XML format'||'</abstract>'||chr(10));
clobwrite(indent(1)||'</meta>'||chr(10));
v_counter := 0;
--Loop through each list entry selected and
output the XML
FOR crec_listing IN
cu_get_listing LOOP
l_record_count :=
l_record_count + 1;
--reset the boolean flags
that control output of certain header flags
bln_appellation_tag_set
:= FALSE;
bln_characters_tag_set := FALSE;
bln_spatial_tag_set
:= FALSE;
bln_place_tag_set := FALSE;
bln_materials_tag_set := FALSE;
bln_type_tag_set := FALSE;
bln_spatialappellation_tag_set :=
FALSE;
bln_geometry_tag_set := FALSE;
bln_record_meta_tag_set := FALSE;
--write top level monumet tag
clobwrite(indent(1)||'<monument>'||chr(10));
--Get record meta data if any
exist for this building
--first output creation data
OPEN
cu_get_record_create_data(crec_listing.list_entry_uid);
FETCH
cu_get_record_create_data
INTO v_when, v_who;
IF
cu_get_record_create_data%FOUND THEN
IF NOT bln_record_meta_tag_set THEN
bln_record_meta_tag_set :=TRUE;
clobwrite(indent(2)||'<recordmetadata>'||chr(10));
END IF;
clobwrite(indent(3)||'<created>'||chr(10));
IF v_when IS NOT NULL
THEN
clobwrite(indent(4)||'<createdon>'||TO_CHAR(v_when,cn_date_format)||'</createdon>'||CHR(10));
END IF;
IF v_who IS NOT NULL
THEN
clobwrite(indent(4)||'<createdby>'||CHR(10));
clobwrite(indent(5)||'<appellation>'||CHR(10));
clobwrite(indent(6)||'<name>'||v_who||'</name>'||CHR(10));
clobwrite(indent(5)||'</appellation>'||CHR(10));
clobwrite(indent(4)||'</createdby>'||CHR(10));
END IF;
clobwrite(indent(3)||'</created>'||chr(10));
END IF;
CLOSE
cu_get_record_create_data;
--now get any update data
OPEN
cu_get_record_update_data(crec_listing.list_entry_uid);
FETCH
cu_get_record_update_data
INTO v_when, v_who;
IF
cu_get_record_update_data%FOUND THEN
IF NOT
bln_record_meta_tag_set THEN
bln_record_meta_tag_set :=TRUE;
clobwrite(indent(2)||'<recordmetadata>'||chr(10));
END IF;
clobwrite(indent(3)||'<lastupdated>'||chr(10));
IF v_when IS NOT NULL
THEN
clobwrite(indent(4)||'<lastupdatedon>'||TO_CHAR(v_when,cn_date_format)||'</lastupdatedon>'||CHR(10));
END IF;
IF v_who IS NOT NULL
THEN
clobwrite(indent(4)||'<lastupdatedby>'||CHR(10));
clobwrite(indent(5)||'<appellation>'||CHR(10));
clobwrite(indent(6)||'<name>'||v_who||'</name>'||CHR(10));
clobwrite(indent(5)||'</appellation>'||CHR(10));
clobwrite(indent(4)||'</lastupdatedby>'||CHR(10));
END IF;
clobwrite(indent(3)||'</lastupdated>'||chr(10));
END IF;
CLOSE
cu_get_record_update_data;
IF bln_record_meta_tag_set
THEN
clobwrite(indent(2)||'</recordmetadata>'||chr(10));
END IF;
--start of proper record
data
--first outout the system
uid
l_output_line :=
indent(3)||'<identifier type="System UID"
namespace="LBS">'||TO_CHAR(crec_listing.list_entry_uid)||'</identifier>'||chr(10);
IF not bln_appellation_tag_set THEN
bln_appellation_tag_set
:= TRUE;
l_output_line :=
indent(2)||'<appellation>'||chr(10)||l_output_line;
END IF;
clobwrite(l_output_line);
--now output any manual
references we can find
FOR crec_manual_refs IN
cu_get_manual_refs(crec_listing.list_entry_uid) LOOP
l_output_line :=
indent(3)||'<identifier type="'||crec_manual_refs.type||'"
namespace
="'||crec_manual_refs.namespace||'">'||crec_manual_refs.identifier||'</identifier>'||chr(10);
IF not
bln_appellation_tag_set THEN
bln_appellation_tag_set := TRUE;
l_output_line :=
indent(2)||'<appellation>'||chr(10)||l_output_line;
END IF;
clobwrite(l_output_line);
END LOOP;
--now get the name data
FOR crec_get_name IN
cu_get_name(crec_listing.list_entry_uid)
LOOP
l_output_line :=
indent(3)||'<name type="'||crec_get_name.type||'"
preferred="'||crec_get_name.preferred||'">'||crec_get_name.name||'</name>'||CHR(10);
IF NOT
bln_appellation_tag_set THEN
bln_appellation_tag_set := TRUE;
l_output_line :=
indent(2)||'<appellation>'||chr(10) ||l_output_line;
END IF;
clobwrite(l_output_line);
end loop;
IF bln_appellation_tag_set
THEN
bln_appellation_tag_set
:= FALSE;
clobwrite(indent(2)||'</appellation>'||CHR(10));
end if;
--outout the listing text
wraped in a CDATA tag in case we have any ! < > ; chars
IF
crec_listing.listing_text IS NOT NULL THEN
clobwrite(indent(2)||'<description source="List Entry"
preferred="True">'||chr(10));
clobwrite(indent(3)||'<full><![CDATA[');
dbms_lob.append(l_clob,
crec_listing.listing_text);
clobwrite(']]></full>'||chr(10));
clobwrite(indent(2)||'</description>'||chr(10));
END IF;
--output designation data -
status and grade
clobwrite(indent(2)||'<designations>'||chr(10));
clobwrite(indent(3)||'<designation>'||chr(10));
clobwrite(indent(4)||'<status>listed
building</status>'||chr(10));
IF crec_listing.grade IS
NOT NULL THEN
clobwrite(indent(4)||'<grade>'||crec_listing.grade||'</grade>'||chr(10));
END IF;
IF
crec_listing.date_listed IS NOT NULL THEN
clobwrite(indent(4)||'<date>'||TO_CHAR(crec_listing.date_listed,cn_date_format)||'</date>'||chr(10));
END IF;
clobwrite(indent(3)||'</designation>'||chr(10));
clobwrite(indent(2)||'</designations>'||chr(10));
--<CHARACTERS>
-- <SPATIAL> data
FOR crec_addresses IN
cu_get_addresses (crec_listing.list_entry_uid) LOOP
IF NOT
bln_characters_tag_set THEN
bln_characters_tag_set
:= TRUE;
clobwrite(indent(2)||'<characters>'||chr(10));
clobwrite(indent(3)||'<character>'||chr(10));
END IF;
IF NOT bln_spatial_tag_set
THEN
bln_spatial_tag_set :=
TRUE;
clobwrite(indent(4)||'<spatial>'||chr(10));
END IF;
IF NOT bln_place_tag_set
THEN
bln_place_tag_set :=
TRUE;
clobwrite(indent(5)||'<place>'||chr(10));
END IF;
clobwrite(indent(6)||'<address>'||chr(10));
IF crec_addresses.streetaddress
IS NOT NULL THEN
clobwrite(indent(7)||'<streetaddress>'||crec_addresses.streetaddress||'</streetaddress>'||chr(10));
END IF;
IF crec_addresses.city IS
NOT NULL THEN
clobwrite(indent(7)||'<city>'||crec_addresses.city||'</city>'||chr(10));
END IF;
IF crec_addresses.county
IS NOT NULL THEN
clobwrite(indent(7)||'<county>'||crec_addresses.county||'</county>'||chr(10));
END IF;
IF crec_addresses.postcode
IS NOT NULL THEN
clobwrite(indent(7)||'<postcode>'||crec_addresses.postcode||'</postcode>'||chr(10));
END IF;
IF
crec_addresses.directions IS NOT NULL THEN
clobwrite(indent(7)||'<directions>'||crec_addresses.directions||'</directions>'||chr(10));
END IF;
clobwrite(indent(6)||'</address>'||chr(10));
END LOOP;
FOR crec_locations IN
cu_get_locations(crec_listing.list_entry_uid) LOOP
IF NOT
bln_characters_tag_set THEN
bln_characters_tag_set
:= TRUE;
clobwrite(indent(2)||'<characters>'||chr(10));
clobwrite(indent(3)||'<character>'||chr(10));
END IF;
IF NOT bln_spatial_tag_set
THEN
bln_spatial_tag_set :=
TRUE;
clobwrite(indent(4)||'<spatial>'||chr(10));
END IF;
IF NOT bln_place_tag_set
THEN
bln_place_tag_set :=
TRUE;
clobwrite(indent(5)||'<place>'||chr(10));
END IF;
IF NOT
bln_namedplace_tag_set THEN
bln_namedplace_tag_set
:= TRUE;
clobwrite(indent(6)||'<namedplace>'||chr(10));
END IF;
IF crec_locations.locality
IS NOT NULL THEN
clobwrite(indent(7)||'<location
type="Locality">'||crec_locations.locality||'</location>'||chr(10));
END IF;
IF crec_locations.parish IS NOT NULL THEN
IF
crec_locations.parish_flag = 'N' THEN
clobwrite(indent(7)||'<location type="Non Parish
Area">'||crec_locations.parish||'</location>'||chr(10));
ELSE
clobwrite(indent(7)||'<location
type="Civil
Parish">'||crec_locations.parish||'</location>'||chr(10));
END IF;
END IF;
IF crec_locations.district
IS NOT NULL THEN
clobwrite(indent(7)||'<location
type="District">'||crec_locations.district||'</location>'||chr(10));
END IF;
IF crec_locations.county
IS NOT NULL THEN
clobwrite(indent(7)||'<location type="Administrative County">'||crec_locations.county||'</location>'||chr(10));
END IF;
IF crec_locations.ceremonial
IS NOT NULL THEN
clobwrite(indent(7)||'<location type="Ceremonial County">'||crec_locations.ceremonial||'</location>'||chr(10));
END IF;
END LOOP;
FOR crec_nat_park IN
cu_get_nat_park (crec_listing.list_entry_uid) LOOP
IF NOT
bln_characters_tag_set THEN
bln_characters_tag_set
:= TRUE;
clobwrite(indent(2)||'<characters>'||chr(10));
clobwrite(indent(3)||'<character>'||chr(10));
END IF;
IF NOT bln_spatial_tag_set
THEN
bln_spatial_tag_set :=
TRUE;
clobwrite(indent(4)||'<spatial>'||chr(10));
END IF;
IF NOT bln_place_tag_set
THEN
bln_place_tag_set :=
TRUE;
clobwrite(indent(5)||'<place>'||chr(10));
END IF;
IF NOT
bln_namedplace_tag_set THEN
bln_namedplace_tag_set
:= TRUE;
clobwrite(indent(6)||'<namedplace>'||chr(10));
END IF;
clobwrite(indent(7)||'<location type="National
Park">'||crec_nat_park.nat_park||'</location>'||chr(10));
end loop;
IF bln_namedplace_tag_set
THEN
bln_namedplace_tag_set :=
FALSE;
clobwrite(indent(6)||'</namedplace>'||chr(10));
END IF;
FOR crec_ngr IN
cu_get_ngr(crec_listing.list_entry_uid) LOOP
IF NOT bln_characters_tag_set THEN
bln_characters_tag_set
:= FALSE;
clobwrite(indent(2)||'<characters>'||chr(10));
clobwrite(indent(3)||'<character>'||chr(10));
END IF;
IF NOT bln_spatial_tag_set
THEN
bln_spatial_tag_set := TRUE;
clobwrite(indent(4)||'<spatial>'||chr(10));
END IF;
IF NOT bln_place_tag_set
THEN
bln_place_tag_set :=
TRUE;
clobwrite(indent(5)||'<place>'||chr(10));
END IF;
IF crec_ngr.ngr IS NOT
NULL THEN
clobwrite(indent(6)||'<gridref
namespace="OSGB36">'||crec_ngr.ngr||'</gridref>'||chr(10));
END IF;
END LOOP;
IF bln_place_tag_set THEN
bln_place_tag_set :=
FALSE;
clobwrite(indent(5)||'</place>'||chr(10));
END IF;
FOR crec_abs IN
cu_get_abs(crec_listing.list_entry_uid) LOOP
IF crec_abs.easting IS NOT
NULL OR crec_abs.northing IS NOT NULL OR crec_abs.ngr_comments IS NOT NULL THEN
IF NOT bln_characters_tag_set
THEN
bln_characters_tag_set
:= FALSE;
clobwrite(indent(2)||'<characters>'||chr(10));
clobwrite(indent(3)||'<character>'||chr(10));
END IF;
IF NOT
bln_spatial_tag_set THEN
bln_spatial_tag_set :=
TRUE;
clobwrite(indent(4)||'<spatial>'||chr(10));
END IF;
IF NOT
bln_geometry_tag_set THEN
bln_geometry_tag_set
:= TRUE;
clobwrite(indent(5)||'<geometry>'||chr(10));
END IF;
IF NOT
bln_spatialappellation_tag_set THEN
bln_spatialappellation_tag_set
:= TRUE;
clobwrite(indent(6)||'<spatialappellation>'||chr(10));
END IF;
clobwrite(indent(7)||'<quickpoint>'||chr(10));
clobwrite(indent(8)||'<srs>EPSG:27700</srs>'||chr(10));
IF crec_abs.easting IS
NOT NULL THEN
clobwrite(indent(8)||'<x>'||TO_CHAR(crec_abs.easting)||'</x>'||chr(10));
END IF;
IF crec_abs.northing IS
NOT NULL THEN
clobwrite(indent(8)||'<y>'||TO_CHAR(crec_abs.northing)||'</y>'||chr(10));
END IF;
clobwrite(indent(7)||'</quickpoint>'||chr(10));
IF
crec_abs.capturemethod IS NOT NULL THEN
clobwrite(indent(8)||'<capturemethod>'||crec_abs.capturemethod||'</capturemethod>'||chr(10));
END IF;
clobwrite(indent(7)||'<entity spatialtype="Point"
uri="'||TO_CHAR(crec_listing.list_entry_uid)||'"
namespace="LBS">'||chr(10));
clobwrite(indent(8)||'<wkt
srs="EPSG:27700">POINT('||TO_CHAR(crec_abs.easting)||'
'||TO_CHAR(crec_abs.northing)||')</wkt>'||chr(10));
clobwrite(indent(8)||'<storedprecision
units="m">1</storedprecision>'||chr(10));
clobwrite(indent(7)||'</entity>');
END IF;
END LOOP;
IF
bln_spatialappellation_tag_set THEN
bln_spatialappellation_tag_set
:= FALSE;
clobwrite(indent(6)||'</spatialappellation>'||chr(10));
END IF;
IF bln_geometry_tag_set THEN
bln_geometry_tag_set :=
FALSE;
clobwrite(indent(5)||'</geometry>'||chr(10));
END IF;
IF bln_spatial_tag_set THEN
bln_spatial_tag_set
:= FALSE;
clobwrite(indent(4)||'</spatial>'||chr(10));
END IF;
-- <CHARACTERS>
--
<TYPE> data
FOR crec_monument_types IN
cu_get_monument_types(crec_listing.list_entry_uid) LOOP
IF NOT
bln_characters_tag_set THEN
bln_characters_tag_set
:= TRUE;
clobwrite(indent(2)||'<characters>'||chr(10));
clobwrite(indent(3)||'<character>'||chr(10));
END IF;
clobwrite(indent(4)||'<type>'||chr(10));
clobwrite(indent(5)||'<monumenttype
namespace="EH_TMT2">'||crec_monument_types.term||'</monumenttype>'||chr(10));
IF
crec_monument_types.display_date IS NOT NULL
OR
crec_monument_types.min_date IS NOT NULL
OR
crec_monument_types.max_date IS NOT NULL THEN
clobwrite(indent(5)||'<temporal>'||chr(10));
clobwrite(indent(6)||'<span>'||chr(10));
IF
crec_monument_types.display_date IS NOT NULL THEN
clobwrite(indent(7)||'<display>'||chr(10));
clobwrite(indent(8)||'<appellation>'||crec_monument_types.display_date||'</appellation>'||chr(10));
clobwrite(indent(7)||'</display>'||chr(10));
END IF;
IF
crec_monument_types.min_date IS NOT NULL THEN
clobwrite(indent(7)||'<start>'||chr(10));
clobwrite(indent(8)||'<appellation
type="Min
Date">'||TO_CHAR(crec_monument_types.min_date)||'</appellation>'||chr(10));
clobwrite(indent(7)||'</start>'||chr(10));
END IF;
IF
crec_monument_types.max_date IS NOT NULL THEN
clobwrite(indent(7)||'<end>'||chr(10));
clobwrite(indent(8)||'<appellation type="Max
Date">'||TO_CHAR(crec_monument_types.max_date)||'</appellation>'||chr(10));
clobwrite(indent(7)||'</end>'||chr(10));
END IF;
clobwrite(indent(6)||'</span>'||chr(10));
clobwrite(indent(5)||'</temporal>'||chr(10));
END IF;
clobwrite(indent(4)||'</type>'||chr(10));
END LOOP;
FOR crec_materials IN
cu_get_materials(crec_listing.list_entry_uid) LOOP
IF NOT
bln_characters_tag_set THEN
bln_characters_tag_set
:= TRUE;
clobwrite(indent(2)||'<characters>'||chr(10));
clobwrite(indent(3)||'<character>'||chr(10));
END IF;
IF NOT bln_type_tag_set
THEN
bln_type_tag_set :=
TRUE;
clobwrite(indent(4)||'<type>'||chr(10));
END IF;
IF NOT
bln_materials_tag_set THEN
bln_materials_tag_set
:= TRUE;
clobwrite(indent(5)||'<materials>'||chr(10));
END IF;
clobwrite(indent(6)||'<material
type="'||crec_materials.type||'">'||crec_materials.material||'</material>'||chr(10));
END LOOP;
IF bln_materials_tag_set
THEN
clobwrite(indent(5)||'</materials>'||chr(10));
END IF;
IF bln_type_tag_set THEN
clobwrite(indent(4)||'</type>'||chr(10));
END IF;
IF bln_characters_tag_set
THEN
bln_characters_tag_set :=
FALSE;
clobwrite(indent(3)||'</character>'||chr(10));
clobwrite(indent(2)||'</characters>'||chr(10));
END IF;
--end of this record so
close the monument tag
clobwrite(indent(1)||'</monument>'||chr(10));
end loop;
--end of all record so write
the enclosing tag
clobwrite('</'||cn_encltag||'>'||chr(10));
END;
FUNCTION FN_GETLASTXMLLENGTH
RETURN NUMBER IS
/* Returns the length of the last XMl generated to the package varible
l_clob*/
l_return_value NUMBER;
BEGIN
l_return_value :=
dbms_lob.GETLENGTH(l_clob);
RETURN NVL(l_return_value, 0);
END;
FUNCTION
FN_GETLASTXMLSUBSTR(p_amount number, p_offset number) RETURN VARCHAR2 IS
/* Returns the substr for the given parameter of the last XMl generated
to the package variable l_clob - return value is varchar2 so max length =
4000*/
l_return_value VARCHAR2(4000);
BEGIN
l_return_value :=
dbms_lob.SUBSTR(l_clob, p_amount, p_offset);
RETURN l_return_value;
END;
FUNCTION
FN_GETLASTXMLRECORDCOUNT RETURN NUMBER IS
BEGIN
RETURN (l_record_count);
END;
END;
6. Sample LBS data in MIDAS
XML format
The following illustrates the export output from the LBS
system. Three records are included in the export.
<?xml
version = "1.0" encoding="ISO-8859-1"?>
<monuments
xmlns="http://www.heritage-standards.org/midas/schema/1.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.heritage-standards.org/midas/schema/1.0
http://195.74.122.210/~fish/midas/schema/1.0/midas_monument.xsd">
<meta>
<title>Listed Building System data
in MIDAS XML format</title>
<subject>heritage</subject>
<keywords>listed
buildings</keywords>
<contacts>
<contact>
<name>
<organisation>English
Heritage National Monuments Record</organisation>
</name>
<address>
<streetaddress>National
Monuments Record Centre, Kemble
Drive</streetaddress>
<city>Swindon</city>
<country>United
Kingdom</country>
</address>
</contact>
</contacts>
<rights>
<copyright>
<statement>© Crown
Copyright</statement>
</copyright>
<accessrights>
<conditions>Use subject to
Click-use license. See
http://www.hmso.gov.uk/click-use-home.htm</conditions>
</accessrights>
</rights>
<source>
<statement>English Heritage
Listed Building System</statement>
</source>
<creation>
<createdon>12-JAN-2005</createdon>
<query>SELECT FROM list_entries
WHERE list_entry_uid IN (82025, 317845, 232558)</query>
</creation>
<coverage>
<spatial>
<place>
<namedplace>
<location type="Administrative
County"
namespace="EH_CDP98">DERBYSHIRE</location>
<location type="Administrative
County"
namespace="EH_CDP98">WILTSHIRE</location>
</namedplace>
<geopolitical
type="Country">England</geopolitical>
</place>
<geometry>
<boundingBox
srs="EPSG:27700" minx="405742" miny="195790"
maxx="409340" maxy="380731" />
</geometry>
</spatial>
</coverage>
<abstract>2 Listed Building System
records in MIDAS XML format</abstract>
</meta>
<monument>
<appellation>
<identifier type="System
UID" namespace="LBS">82025</identifier>
<identifier type="Manual"
namespace ="LB Volume, Map, Item No.">178, 8,
46</identifier>
<name type="Current"
preferred="True"></name>
</appellation>
<description source="List
Entry" preferred="True">
<full><![CDATA[SK 05 80 TOWN OF CHAPEL-EN-LE-FRITH MARKET PLACE
8/46
(South Side)
22.9.78 No 8
GV II
House. C17 with c19 refronting to rear and other C19
alterations. Rendered stone
to
north and west: coursed, squared,
rock-faced gritstone to south. Gritstone
dressings
and quoins. Stone slate roof. Single gable end stone stack. Two storeys
to
north, three to south. Irregular window
arrangement. North elevation -
doorcase
with large shouldered lintel to west.
Plank door. Small single light
window
to east, C19 glazed and panelled door
and casement window, beyond to west.
Above,
two C20 casements in recessed and chamfered openings. West gable wall has
2-light
recessed and chamfered mullion window with two semi-circular headed single
light
windows above. South elevation has two
C19 flush surround doorcases. To
either
side, small paned top hung casements.
Two similar above and further two to
eaves.
Listing
NGR: SK0574280731]]></full>
</description>
<designations>
<designation>
<status>listed
building</status>
<grade>II</grade>
<date>22-SEP-1978</date>
</designation>
</designations>
<characters>
<character>
<spatial>
<place>
<address>
<streetaddress>4-6 Market Place,
Chapel-en-le-Frith</streetaddress>
<city>High
Peak</city>
<county>Derbyshire</county>
<postcode>SK23
0EN</postcode>
</address>
<namedplace>
<location
type="Civil Parish">CHAPEL EN LE FRITH</location>
<location
type="District">HIGH
PEAK</location>
<location type="Administrative
County">DERBYSHIRE</location>
<location type="Ceremonial
County">DERBYSHIRE</location>
</namedplace>
<gridref
namespace="OSGB36">SK0574280731</gridref>
</place>
<geometry>
<spatialappellation>
<quickpoint>
<srs>EPSG:27700</srs>
<x>405742</x>
<y>380731</y>
</quickpoint>
<entity
spatialtype="Point" uri="82025"
namespace="LBS">
<wkt
srs="EPSG:27700">POINT(405742 380731)</wkt>
<storedprecision
units="Metres">1</storedprecision>
</entity> </spatialappellation>
</geometry>
</spatial>
<type>
<monumenttype
namespace="EH_TMT2">HOUSE</monumenttype>
<temporal>
<span>
<display>
<appellation>C17</appellation>
</display>
<start>
<appellation type="Min
Date">1600</appellation>
</start>
<end>
<appellation
type="Max Date">1699</appellation>
</end>
</span>
</temporal>
</type>
<type>
<monumenttype
namespace="EH_TMT2">HOUSE</monumenttype>
<temporal>
<span>
<display>
<appellation>C19</appellation>
</display>
<start>
<appellation type="Min
Date">1800</appellation>
</start>
<end>
<appellation
type="Max Date">1899</appellation>
</end>
</span>
</temporal>
</type>
<type>
<materials>
<material
type="Main">RENDER</material>
<material
type="Main">STONE</material>
<material
type="Main">GRITSTONE (SANDSTONE)</material>
<material
type="Covering">STONE</material>
</materials>
</type>
</character>
</characters>
</monument>
<monument>
<appellation>
<identifier type="System
UID" namespace="LBS">317845</identifier>
<identifier type="Manual"
namespace ="LB Volume, Map, Item No.">1178, 2,
195</identifier>
<name type="Current"
preferred="True">PAIR OF HABGOOD MONUMENTS IN CHURCHYARD, 8 METRES
SOUTH OF CHANCEL, CHURCH OF ST JOHN</name>
</appellation>
<description source="List Entry"
preferred="True">
<full><![CDATA[SU 09 NE LATTON CROFT LANE
2/195 Pair of
Habgood monuments in
churchyard, 8m south of
chancel, Church
of St. John
GV II
Two
chest tombs, late C18. Limestone. To north;
moulded top with
reeded
fascia and recessed corner balusters.
Cavetto base. To
WILLIAM
HABGOOD, Haberdasher of London, died 1798.
To south;
moulded
table and cavetto base. Recessed corner
balusters.
Lightly
incised inscription, and two plain ovals
on north face, to
HABGOOD,
died 1791. This pair of Habgood tombs is
within a group
of
Habgood tombs and headstones lying south of the chancel and
south
transept.
Listing
NGR: SU0934095790]]></full>
</description>
<designations>
<designation>
<status>listed
building</status>
<grade>II</grade>
<date>17-APR-1986</date>
</designation>
</designations>
<characters>
<character>
<spatial>
<place>
<address>
<streetaddress>Glebe
Cottage , Latton</streetaddress>
<city>Swindon</city>
<postcode>SN6
6DP</postcode>
</address>
<namedplace>
<location
type="Civil Parish">LATTON</location>
<location
type="District">NORTH WILTSHIRE</location>
<location type="Administrative
County">WILTSHIRE</location>
<location type="Ceremonial
County">WILTSHIRE</location>
</namedplace>
<gridref namespace="OSGB36">SU0934095790</gridref>
</place>
<geometry>
<spatialappellation>
<quickpoint>
<srs>EPSG:27700</srs>
<x>409340</x>
<y>195790</y>
</quickpoint>
<entity
spatialtype="Point" uri="317845"
namespace="LBS">
<wkt
srs="EPSG:27700">POINT(409340 195790)</wkt>
<storedprecision
units="Metres">1</storedprecision>
</entity> </spatialappellation>
</geometry>
</spatial>
<type>
<monumenttype
namespace="EH_TMT2">CHEST TOMB</monumenttype>
<temporal>
<span>
<display>
<appellation>1791</appellation>
</display>
<start>
<appellation
type="Min Date">1791</appellation>
</start>
<end>
<appellation
type="Max Date">1791</appellation>
</end>
</span>
</temporal>
</type>
<type>
<monumenttype
namespace="EH_TMT2">CHEST TOMB</monumenttype>
<temporal>
<span>
<display>
<appellation>1798</appellation>
</display>
<start>
<appellation type="Min
Date">1798</appellation>
</start>
<end>
<appellation
type="Max Date">1798</appellation>
</end>
</span>
</temporal>
</type>
<type>
<materials>
<material
type="Main">LIMESTONE</material>
</materials>
</type>
</character>
</characters>
</monument>
line 7390.
class=MsoNormal style='mso-layout-grid-align:none;text-autospace:none'></monuments>
Duncan Brown of the National Monuments Record,