List of articles Terminology Choose language

Smoothness between rational and xml

Foreign key between rational and xml, between xml and xml

It's comfortable to describe foreign keys of such kind by using XPath, having appended it by expressions like '@field/element. For example, rational field can refer to xml-attribute, containing in textual field of other table.

create table a(
  a1 num,
  a2 string
);
create table b(
  b1 num,
  b2 num references a(@a2/k/m/n/@n1)
);

Vise verse, xml-attribute, containing in textual field, can refer to rational field of other table. In this case we cann't specify foreign key directly in expression 'create table', and are forced to use separate expression 'alter table'

create table a (
  a1 num,
  a2 num
);
create table b (
  b1 num,
  b2 string
);
alter table b add foreign key (@b2/k/m/n/@n1) references a(a2);

Foreign key from xml-attribute of textual string of one table to xml-attribute of textual string of other table is combination of two mentioned above cases.

create table a (
  a1 num,
  a2 string
);
create table b (
  b1 num,
  b2 string
);
alter table b add foreign key (@b2/k/m/n/@n1) references a(@a2/p/q/r/@r1);

Inserting of rational into xml

Not only xml-text can be inserted into textual field

insert into tab (fld) values ('<tag>...</tag>');

but also tree, created on base of foreign keys between tables. It's unimportant, following section of tree is parental table or branch table - it's necessary only relationship of two tables. I propose to name further constructions like 'a.b.c' as XTree. Reference fields, used during building of rational tree, are not displayed in xml-presentations (because lack of need).

insert into tab (fld) select * from a.b.c;

First is parental table, then branch table. Let preceding section of tree is parental table (table "a"), and next section is branch table (table "b"), refering to primary key of parental table. Let there are several records in branch table, all records refer to one parental record - this means, that next level of tree contains several elements. We shall name section of tree, consisting of several records of branch table, as set. We list names of tables to extract tree - parental table, then branch table.

at condition of following DDL

request on DML

request is executed so

create table a (
  id   num      primary key,
  data float
);
create table b (
  id   num      primary key,
  ref  num      references a(id),
  data float
);
create table c (
  id   num      primary key,
  link num      references b(id),
  data float
);
insert into a values (1,     12.3);
insert into b values (10, 1, 23.4);
insert into b values (20, 1, 34.5);
insert into b values (30, 1, 45.6);
insert into c values (100,10,56.7);
insert into c values (101,10,67.8);
insert into c values (200,20,78.9);
insert into c values (201,20,89.1);
insert into c values (300,30,91.2);

insert into tab (fld)
  select * from a.b.c;

insert into tab (fld) values ('
  <a     id=1   data=12.3>
    <b   id=10  data=23.4>
      <c id=100 data=56.7/>
      <c id=101 data=67.8/>
    </b>
    <b   id=20  data=34.5>
      <c id=200 data=78.9/>
      <c id=201 data=89.1/>
    </b>
    <b   id=30  data=45.6>
      <c id=200 data=91.2/>
    </b>
  </a>
');

If branch (enclosed) table refers by two own fields to two different records of parental (embracing) table, then it' necessary to specify name of corresponding field of parent table in request after name of branch, enclosed table through sign "#". We shall name such specification of field in section as determination of section. If determination is not specified, then all enclosed records will be extracted.

at condition of following DDL

request on DML

request is executed so

create table a (
  id   num      primary key,
  data float
);
create table b (
  id   num      primary key,
  ref1 num      references a(id),
  ref2 num      references a(id),
  data float
);
create table c (
  id   num      primary key,
  lnk1 num      references b(id),
  lnk2 num      references b(id),
  data float
);
insert into a values (1,        12.3);
insert into a values (2,        23.4);
insert into b values (10, 1, 2, 34.5);
insert into b values (20, 1, 2, 45.6);
insert into b values (30, 1, 2, 56.7);
insert into b values (40, 1, 2, 67.8);
insert into c values (100,10,20,78.9);
insert into c values (101,10,20,89.1);
insert into c values (200,30,40,91.2);
insert into c values (201,30,40,88.8);

insert into tab (fld)
  select * from a.b#ref1.c#lnk1;

insert into tab (fld) values ('
  <a     id=1           data=12.3>
    <b   id=10  ref2=2  data=34.5>
      <c id=100 lnk2=20 data=78.9/>
      <c id=101 lnk2=20 data=89.1/>
    </b>
    <b   id=30  ref2=2  data=56.7>
      <c id=200 lnk2=40 data=91.2/>
      <c id=201 lnk2=40 data=88.8/>
    </b>
  </a>
');

First is branch table, then parental table. Let preceding section of tree is branch table (table "a"), and next section is parental table (table "b"). We shall name section of tree, consisting of record of parental table, as relay-race. For extraction tree we enumerate the name of the tables - first affiliated, then parental. We list names of tables to extract tree - branch table, then parental table.

at condition of following DDL

request on DML

request is executed so

create table a (
  id   num      primary key,
  ref  num      references b(id),
  data float
);
create table b (
  id   num      primary key,
  link num      references c(id),
  data float
);
create table c (
  id   num      primary key,
  data float
);
insert into с values (100,    34.5);
insert into b values (10, 100,23.4);
insert into a values (1,  10, 12.3);

insert into tab (fld)
  select * from a.b.c;

insert into tab (fld) values ('
  <a     id=1   data=12.3>
    <b   id=10  data=23.4>
      <c id=100 data=34.5/>
    </b>
  </a>
');

If branch (embracing) table refers by two own fields to two different records of parental (enclosed) table, then it' necessary to specify name of corresponding field of parent table in request after name of branch, embracing table through sign "#". Thus table and its refering field are specified in one section, instead of in different sections. We shall name such specification of field in section also as determination of section. If determination is not specified, then all embracing records will be extracted.

at condition of following DDL

request on DML

request is executed so

create table a (
  id   num      primary key,
  ref1 num      references b(id),
  ref2 num      references b(id),
  data float
);
create table b (
  id   num      primary key,
  lnk1 num      references c(id),
  lnk2 num      references c(id),
  data float
);
create table c (
  id   num      primary key,
  data float
);
insert into с values (201,        78.9);
insert into с values (200,        67.8);
insert into с values (101,        56.7);
insert into с values (100,        45.6);
insert into b values (20, 200,201,34.5);
insert into b values (10, 100,101,23.4);
insert into a values (1,  10, 20, 12.3);

insert into tab (fld)
  select * from a#ref1.b#lnk1.c;

insert into tab (fld) values ('
  <a     id=1   data=12.3>
    <b   id=10  data=23.4>
      <c id=100 data=45.6/>
    </b>
  </a>
');

Bear's ear is construction, in which single table is both parent and child table (foreign key refers to unique key of own table). We shall always consider several consecutive records in bear's ear as one section (section differs from tabment by this), and we shall name this section as list.

Set and list are differ only in rational representation, they look identical in xml representation.

at condition of following DDL

request on DML

request is executed so

create table a (
  id   num      primary key,
  ref  num      references a(id),
  lnk  num      references b(id),
  data float
);
create table b (
  id   num      primary key,
  ref  num      references b(id),
  data float
);
insert into b values (300,null,3.3);
insert into b values (30, 300, 3.2);
insert into b values (3,  30,  3.1);
insert into b values (400,null,4.3);
insert into b values (40, 400, 4.2);
insert into b values (4,  40,  4.1);
insert into b values (500,null,5.3);
insert into b values (50, 500, 5.2);
insert into b values (5,  50,  5.1);

insert into a values (100,null,500,34.5);
insert into a values (10, 100, 400,23.4);
insert into a values (1,  10,  300,12.3);

insert into tab (fld)
  select * from a.b;

insert into tab (fld) values ('
  <a     id=1   data=12.3>
    <b   id=3   data=3.1>
    <b   id=30  data=3.2>
    <b   id=300 data=3.3>
  </a>
  <a     id=1   data=23.4>
    <b   id=4   data=4.1>
    <b   id=40  data=4.2>
    <b   id=400 data=4.3>
  </a>
  <a     id=1   data=34.5>
    <b   id=5   data=5.1>
    <b   id=50  data=5.2>
    <b   id=500 data=5.3>
  </a>
');

Inserting of xml into rational

Xml-elements can be transformated into binary kind and written into tables with identical names (xml-attributes are written into fields with identical names), and whole xml-tree will be transformated into rational tree, formed by records, which refer to each other by foreign keys. This inserting of data is executed not into field, but under table, creating sub-tree. So name of (numeric) field must be omitted, otherwise attempt to transform textual (xml) string into numeric kind will occur, and message about mistake will appear.

insert into tab values ('<tag>...</tag>');

Moreover, not only xml-text can be inserted under table, but also other rational tree

insert into tab select * from a.b.c;

Success of writing of xml-text into tables depends of, is scheme of database corresponds to entering xml (some xml-sub-trees are ignored at discrepancy).

If enclosed table refers by two own fields to two different records of embracing table, then it' necessary to specify name of corresponding field of embracing table in opening tag of enclosed table after its name through sign "#".

If embracing table refers by two own fields to two different records of enclosed table, then it' necessary to specify name of corresponding field of enclosed table in opening tag of embracing table after its name through sign "#".

Thus table and its refering field are specified in one opening tag. We shall name such specification of field in opening tag also as determination (it's also provided browser). Different variants of determination are considered in separate document. It's supposed in example below, that all primary keys have sequences, values from which are entered into these keys by default.

at condition of following DDL	request on DML	request is executed so
create table a ( id num primary key, lnk num references tab, data float ); create table b ( id num primary key, ref num references a(id), data float );	insert into tab values (' <a data=12.3> <b data=23.4> <b data=34.5> <b data=45.6> </a> ');	insert into tab values (1); insert into a values (10, 1, 12.3); insert into b values (101,10,23.4); insert into b values (102,10,34.5); insert into b values (103,10,45.6); -- section "b" is set
create table a ( id num primary key, lnk num references tab, data float ); create table b ( id num primary key, ref1 num references a(id), ref2 num references a(id), data float );	insert into tab values (' <a data=12.3> <b data=23.4> <b data=34.5> <b data=45.6> </a> ');	insert into tab values (1); insert into a values (10, 1, 12.3); -- "b" are not entered, because ambiguity -- is not solved by determination
	insert into tab values (' <a data=12.3> <b#ref1 data=23.4> <b#ref1 data=34.5> <b#ref1 data=45.6> </a> ');	insert into tab values (1); insert into a values (10, 1, 12.3); insert into b values (101,10,null,23.4); insert into b values (102,10,null,34.5); insert into b values (103,10,null,45.6);

create table a ( id num primary key, lnk num references tab, ref num references b(id), data float ); create table b ( id num primary key, data float );	insert into tab values (' <a data=12.3> <b data=23.4> </a> ');	insert into tab values (1); insert into a values (10, 1,100,12.3); insert into b values (100, 23.4); -- section "b" is relay-race
	insert into tab values (' <a data=12.3> <b data=23.4> <b data=34.5> <b data=45.6> </a> ');	insert into tab values (1); insert into a values (10, 1,null,12.3); -- no one "b" is entered, because scheme of database -- contain neither set, nor list for them -- (entering of one "b" could delude -- programmer, debugging program)

create table a ( id num primary key, lnk num references tab, ref1 num references b(id), ref2 num references b(id), data float ); create table b ( id num primary key, data float );	insert into tab values (' <a data=12.3> <b data=23.4> </a> ');	insert into tab values (1); insert into a values (10, 1,null,null,12.3); -- "b" are not entered, because ambiguity -- is not solved by determination
	insert into tab values (' <a#ref1 data=12.3> <b data=23.4> </a> ');	insert into tab values (1); insert into a values (10, 1,100,null,12.3); insert into b values (100, 23.4);

create table a ( id num primary key, lnk num references tab, ref num references b(id), data float ); create table b ( id num primary key, ref num references b(id), data float );	insert into tab values (' <a data=12.3> <b data=23.4> <b data=34.5> <b data=45.6> </a> ');	insert into tab values (1); insert into a values (10, 1,101, 12.3); insert into b values (101, 102, 23.4); insert into b values (102, 103, 34.5); insert into b values (103, null,45.6); -- section "b" is list

Inserting under xml-element, selection from xml-element

It's possible to insert data not only under rational table, but also under concrete xml-element, being in textual field of rational table (inside this xml-element), if to specify it by XPath.

insert into tab/@fld/k/m/n values ('<tag>...</tag>');
insert into tab/@fld/k/m/n select * from a.b.c;

For example

was (in field 'fld' of table 'tab')

request on DML

became (in field 'fld' of table 'tab')

<k>
  <m>
    <n>
      <p/>
    </n>
  </m>
</k>

insert into tab/@fld/k/m/n
  values ('<tag>...</tag>');

<k>
  <m>
    <n>
      <p/>
      <tag>...</tag>
    </n>
  </m>
</k>

<k>
  <m>
    <n>
      <p/>
    </n>
  </m>
</k>

insert into tab/@fld/k/m/n
  select * from a.b.c;

<k>
  <m>
    <n>
      <p/>

      <a>
        <b> <c/> <c/> </b>
        <b> <c/> <c/> </b>
      </a>
      <a>
        <b> <c/> <c/> </b>
        <b> <c/> <c/> </b>
      </a>

    </n>
  </m>
</k>

It's possible to select not only several binded rational tables, but also part of xml-tree, containing in textual field of rational table. It's necessary to specify root of sub-tree by XPath, and to specify sub-tree itself by XTree. We shall name this combination of XPath and XTree as XLang.

insert into tabname (field) select * from tab/@fld/k/m/n/a.b.c;

For example

was (in field 'field' of table 'tabname')		request on DML		became (in field 'field' of table 'tabname')
		insert into tabname (field) select * from tab/@fld/k/m/n/a.b.c;		<a> <b> <c/> <c/> </b> <b> <c/> <c/> </b> </a> <a> <b> <c/> <c/> </b> <b> <c/> <c/> </b> </a>

Inserting not under

To have opportunity to insert xml-data not under table, but data itself, it's possible to not specify super-table.

at condition of following DDL

request on DML

request is executed so

create table a (
  id   num      primary key,
  data float
);
create table b (
  id   num      primary key,
  ref  num      references a(id),
  data float
);

insert values ('
  <a        data=12.3>
    <b      data=23.4>
    <b      data=34.5>
    <b      data=45.6>
  </a>
');

insert into a values (10,    12.3);
insert into b values (101,10,23.4);
insert into b values (102,10,34.5);
insert into b values (103,10,45.6);

insert select * from tab/@fld/k/m/n/a.b;

Updating

There is no difficulty to update field (of rational table)

update tab set fld=( select * from a.b.c                );
update tab set fld=( select * from a.b#ref1.c#lnk1      );
update tab set fld=( select * from t/@field/k/m/n/a.b.c );
update tab set fld='<a>...</a>';

or attribute (of xml-element)

update tab set @fld/p/q/r/@r1=( select * from a.b.c                );
update tab set @fld/p/q/r/@r1=( select * from a.b#ref1.c#lnk1      );
update tab set @fld/p/q/r/@r1=( select * from t/@field/k/m/n/a.b.c );
update tab set @fld/p/q/r/@r1='<a>...</a>';

I propose to designate content of xml-element as '@@', then replacement of it looks like shown in example below. Rational tree can be only deleted and created again.

update tab set @fld/p/q/@@=( select * from a.b.c                );
update tab set @fld/p/q/@@=( select * from a.b#ref1.c#lnk1      );
update tab set @fld/p/q/@@=( select * from t/@field/k/m/n/a.b.c );
update tab set @fld/p/q/@@='<a>...</a>';

Deleting

The following possibilities to delete are appended: to delete concrete xml-element (with whole its content)

delete from tab/@fld/k/m/n;

to delete xml-tree

delete from tab/@fld/k.m.n;

to delete rational tree

delete from a.b.c;

Predicates

Predicates should be created also for xml-attributes.

where @fld/k/m/n/@n1 =  5;
where @fld/k/m/n/@n1 in (select a1 from a);
where @fld/k/m/n/@@  =  '<tag>...</tag>';
where @fld/k/m/n     in (select * from a/b/n);

Permissions

Permissions should be given also for xml-elements.

grant  insert on tab/@fld/k/m/n to   UserName;
revoke delete on tab/@fld/k/m/n from UserName;

Triggers

Triggers should be created also for xml-elements.

create trigger TriggerName for tab/@fld/k/m/n
after insert as begin
  ...
end;;

Symmetrizing of XPath and XTree

Enclosed tabments in XTree are not extracted, if they are not specified. For example, if 'a' has enclosed tabment 'm', then it will not be extracted by expression 'a.b.c'.

XPath has expressions like 'a//b' - i propose to enter symmetric costruction 'a..b' in XTree, which designate tabment 'a', tabment 'b' and any quantity of tabments with any name between them.

Besides that, i propose to use sign '!' for designation of level of hierarchy in square brackets (i.e. 'tab[!=5]'). For example, tabment 'a' has 0-th, 'b' has 1-st, 'c' has 2-nd, 'd' has 3-rd, 'e' has 4-th level in expression 'a.b.c.d.e'. All elements of list (tabments of one section) has identical value of level of hierarchy.

Dmitry Turin

List of articles Terminology Choose language