1.05k likes | 1.21k Views
SQL :2008. Dmitry Turin sql 2008 .euro.ru. Web integration. Output as XML. <tab id=3 data=12.3> <tab id=7 data=23.4> <tab id=10 data=34.5> <tab id=25 data=45.6> <tab1 data2=3 data1=12.3> <tab1 data2=7 data1=23.4> <tab1 data2=10 data1=34.5>
E N D
SQL:2008 Dmitry Turin sql2008.euro.ru
Output as XML <tab id=3 data=12.3> <tab id=7 data=23.4> <tab id=10 data=34.5> <tab id=25 data=45.6> <tab1 data2=3 data1=12.3> <tab1 data2=7 data1=23.4> <tab1 data2=10 data1=34.5> <tab1 data2=25 data1=45.6> <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> <?xml-stylesheet type="text/xsl" href="a.xsl"?>' select @id, @data from tab; select tab2/@data2, tab1/@data1 from tab1, tab2 where tab2/@fld2=tab1/@fld1; select * from a.b.c; -- XTree select '<?xml-stylesheet type="text/xsl" href="a.xsl"?>';
Select rational: Set a select * from a.b.c; <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> b c
Set: Determination a select * from a.b#ref1.c#lnk1; <a id=1 data=12.3> <b#ref1 id=10 ref2=2 data=34.5> <c#lnk1 id=100 lnk2=20 data=78.9/> <c#lnk1 id=101 lnk2=20 data=89.1/> </b> <b#ref1 id=30 ref2=2 data=56.7> <c#lnk1 id=200 lnk2=40 data=91.2/> <c#lnk1 id=201 lnk2=40 data=88.8/> </b> </a> b c
Select rational: Relay-race a select * from a.b.c; b <a id=1 data=12.3> <b id=10 data=23.4> <c id=100 data=34.5/> </b> </a> c
Relay-race: Determination a select * from a#ref1.b#lnk1.c; b <a#ref1 id=1 data=12.3> <b#lnk1 id=10 data=23.4> <c id=100 data=45.6/> </b> </a> c
Select rational: List select * from a.b.c; a <a id=88 data=77.7> <b id=1 data=12.3> <c id=3 data=3.1/> <c id=30 data=3.2/> <c id=300 data=3.3/> </b> <b id=10 data=23.4> <c id=4 data=4.1/> <c id=40 data=4.2/> <c id=400 data=4.3/> </b> <b id=100 data=34.5> <c id=5 data=5.1/> <c id=50 data=5.2/> <c id=500 data=5.3/> </b> </a> b c
List: Determination select * from a.b$ref1.c$ref3; a <a id=88 data=77.7> <b$ref1 id=1 data=12.3> <c$ref3 id=3 data=3.1/> <c$ref3 id=30 data=3.2/> <c$ref3 id=300 data=3.3/> </b> <b$ref1 id=10 data=23.4> <c$ref3 id=4 data=4.1/> <c$ref3 id=40 data=4.2/> <c$ref3 id=400 data=4.3/> </b> </a> b c
a/(b | k) / c a / b*/ c a / b+/ c a / b?/ c a / b[@b1] / c a / b[@b1=5] / c a / b[0]/ c a /(b | k)[0]/ c a//b a.(b | k).c a.b*.c a.b+.c a.b?.c a.b[@b1].c a.b[@b1=5].c a.b[0].c a.(b | k)[0].c a..b tab[!=5] XLang XPath XTree
Input as XML DBMS Browser <a data=12.3/> a <form> auto-insert trigger for a <r> <s> <t/> </s> </r> new document select …; • Value for PK of new record is • by trigger on insert (Oracle, etc) • by datatype ‘serial’ (Postgres)
Auto-insert rational: Set a <a data=12.3> <b data=23.4> <c data=56.7/> <c data=67.8/> </b> <b data=34.5> <c data=78.9/> <c data=89.1/> </b> <b data=45.6> <c data=91.2/> </b> </a> b c
Set: Determination a <a data=12.3> <b#ref1 data=34.5> <c#lnk1 data=78.9/> <c#lnk1 data=89.1/> </b> <b#ref1 data=56.7> <c#lnk1 data=91.2/> <c#lnk1 data=88.8/> </b> </a> b c
Auto-insert rational: Relay-race a <a data=12.3> <b data=23.4> <c data=34.5/> </b> </a> b c
Relay-race: Determination a <a#ref1 data=12.3> <b#lnk1 data=23.4> <c data=45.6/> </b> </a> b c
Auto-insert rational: List <a data=77.7> <b data=12.3> <c data=3.1/> <c data=3.2/> <c data=3.3/> </b> <b data=23.4> <c data=4.1/> <c data=4.2/> <c data=4.3/> </b> <b data=34.5> <c data=5.1/> <c data=5.2/> <c data=5.3/> </b> </a> a b c
List: Determination a <a data=77.7> <b$ref1 data=12.3> <c$ref3 data=3.1/> <c$ref3 data=3.2/> <c$ref3 data=3.3/> </b> <b$ref1 data=23.4> <c$ref3 data=4.1/> <c$ref3 data=4.2/> <c$ref3 data=4.3/> </b> </a> b c
Communication terms Sament SAg eleMENT Sag Sending xml-tAG Saribute SAg attRIBUTE
HTTP … <form> <hidden> <a#ref a1=“v1”> </hidden> <input name="a2" value=“v2"> <input name=“a3“ type=“file"> <hidden> <b b1=“bb”/> </a> </hidden> </form> Html-document: Saments: <formdata> <a#ref a1=“v1“ a2=“v2”a3=“UniqueId”> <b b1=“bb”/> </a> </formdata> <file name=“a3“ filename="UniqueId">Y29udG</file>
… is the most … <form> <hidden> <data a1=“v1”> </hidden> <tr id="order#ref" repeat="template"> <td><input type="text" name="row[order].product"></td> <td><input type="text" name="row[order].quantity"></td> <td><button type="remove">Remove This Row</button></td> </tr> <tr> <td><button type="add" template="order">Add Row</button></td> </tr> <hidden> </data> </hidden> </form> Html-document: <formdata> <data a1=“v1”> <order#refproduct=“p1” quantity=“q1”> <order#ref product=“p2” quantity=“q2”> <order#ref product=“p3” quantity=“q3”> </data> </formdata> Saments:
… wide-spread protocol Picture can be value of field, but its URLinstead of picture is sent into output. Database: Saments: b <b b1=“5” b2=“./3652435.jpg”> <img href=“./7254843.jpg”> • SqlPlus.exe (Oracle) • PgSql.exe (Postgres) • iSql.exe (InterBase), etc
FK between rational and xml create table a ( a1 string, a2 number, a3 string ); create table b ( b1 number references a(@a1/k/m/@m1), b2 string, foreign key (@b2/p/q/@q1) references a(@a2), b3 string, foreign key (@b3/p/q/@q1) references a(@a3/k/m/@m1) );
Select xml select * from tab/@fld/p/q/r/a.b.c; Insert into sub-xml insert into tab/@fld/k/m/n/@@ select * from a.b.c; insert into tab/@fld/k/m/n/@@ values ('<a>...</a>'); insert into tab/@fld/k/m/n/@@ select * from t/@f/p/q/r/a.b.c; Delete xml delete from tab/@fld/p/q/a/b/c; delete from tab/@fld/p/q/a.b.c;
Update xml update tab set @fld=( select * from a.b#ref.c#lnk ); update tab set @fld=( select * from t/@f/k/m/a.b.c ); update tab set @fld='<a>...</a>'; update tab set @fld/p/q/@q1=( select * from a.b#ref.c#lnk ); update tab set @fld/p/q/@q1=( select * from t/@f/k/m/a.b.c ); update tab set @fld/p/q/@q1='<a>...</a>'; update tab set @fld/p/q/@@=( select * from a.b#ref.c#lnk ); update tab set @fld/p/q/@@=( select * from t/@f/k/m/a.b.c ); update tab set @fld/p/q/@@='<a>...</a>';
Predicates where @fld/k/m/n/@n1 = 5; where @fld/k/m/n/@n1 in (select @a1 from a); where @fld/k/m/n/@@ = '<a>...</a>'; Permissions grant insert on tab/@fld/k/m/nto UserName; revoke delete on tab/@fld/k/m/n from UserName; Triggers create trigger TriggerName for tab/@fld/k/m/n after insert as begin ...end;;
Update rational update tab set p/q/r/@fld=5; Delete rational delete from tab/p/q/a.b.c;
Insert rational: Set insert into tab select * from p/@p1/q/r/a.b; tab insert into tab values (' <a data=12.3> <b data=23.4/> <b data=34.5/> <b data=45.6/> </a> '); a b
Set: determination insert into tab values (' <a#ref1 data=12.3> <b#lnk1 data=23.4/> <b#lnk1 data=34.5/> <b#lnk1 data=45.6/> </a> '); tab a b
Insert rational: Relay-race insert into tab select * from p/@p1/q/r/a.b; tab insert into tab values (' <a data=12.3> <b data=23.4/> </a> '); a b
Relay-race: Determination insert into tab#ref1 values (' <a#lnk1 data=12.3> <b data=23.4/> </a> '); tab a b
Insert rational: List insert into tab select * from p/@p1/q/r/a.b; insert into tab values (' <a data=1.1 > <b data=21.1/> <b data=21.2/> <b data=21.3/> </a> <a data=1.2 > <b data=22.1/> <b data=22.2/> <b data=22.3/> </a> '); tab a b
List: Determination insert into tab values (' <a$ref1 data=1.1 > <b$ref3 data=21.1/> <b$ref3 data=21.2/> <b$ref3 data=21.3/> </a> <a$ref1 data=1.2 > <b$ref3 data=22.1/> <b$ref3 data=22.2/> <b$ref3 data=22.3/> </a> '); tab a b
Insert rational: into database insert into default: select * from p/@p1/q/r/a.b; insert into default: values (' <a data=12.3> <b#lnk data=23.4/> <b#lnk data=34.5/> <b#lnk data=45.6/> </a> '); insert into default: select* from a.b#lnk; insert into default: values (' <a#ref data=12.3> <b data=23.4/> <b data=34.5/> <b data=45.6/> </a> '); insert into default: select* from a#ref.b;
Database terms Tabment TABle-eleMENT Fattrib Field-ATTRIBute Section level of hierarchy of XTree (whole list is one section) a/@a1 rational or xml @a1/@b2 rational (created by "row") a/b rational or xml @a1/b "a1" is rational, "b" is xml
Extracted sections select b.d from p/q/a.b.c.d; select b.d from p/q/@q1/a.b.c.d; <b b1=3 b2=5 data=10> <d data=234> <d data=345> </b> <b b1=7 b2=8 data=10> <d data=456> <d data=678> </b>
Extracted fatribs <a a1=56 a2=67 a3=78> <b b1=11 b2=12 b3=13/> <b b1=21 b2=22 b3=23/> <b b1=31 b2=32 b3=33/> </a> select * from a.b; select * from p/q/@q1/s/a.b; select a.b[@b1 @b2] from a.b; select a.b[@b1 @b2] from p/q/@q1/s/a.b; <a a1=56 a2=67 a3=78> <b b1=11 b2=12 /> <b b1=21 b2=22 /> <b b1=31 b2=32 /> </a> select a.b[@b1 as @m, @b2 as @n] from a.b; select a.b[@b1 as @m, @b2 as @n] from p/q/@q1/s/a.b; <a a1=56 a2=67 a3=78> <b m=11 n=12 /> <b m=21 n=22 /> <b m=31 n=32 /> </a>
Virtual foreign key (vFK) select a.b.k.m from p/q/a.b , p/q/k.m where b/@b1=k/@k1; select a.b.k.m from p/q/@q1/s/a.b , p/q/@q1/s/k.m where b/@b1=k/@k1;
vFK and spec-symbols ‘*’, ‘+’ select * from b* where previous(b)/@b2=next(b)/@b1; select * from a.b*.c where previous(b)/@b2=next(b)/@b1; select * from k/m/@m1/a/b* where previous(b)/@b2=next(b)/@b1; select * from k/m/@m1/a.b*.c where previous(b)/@b2=next(b)/@b1;
Aliases and vFK select k.m.n from p/q/c k m n-- rational from p/q/@q1/r/c k m n-- xml where k/@c2=m/@c3and m/@c4=n/@c5; select a.b.k.m.n from a.b , p/q/c k m n -- rational & rational from a.b , p/q/@q1/c k m n -- rational & xml from t/u/@u1/a.b , p/q/@q1/c k m n -- xml & xml where k/@c2=m/@c3 and m/@c4=n/@c5;
XPath continues XPath delete from p/q/a.b.c where t/u/@u1=5 -- rational fatrib 'p/q/t/u/@u1' and t/u/@u1/k/m/@m1=5; -- xml fatrib 'p/q/t/u/@u1/k/m/@m1' delete from p/q/@q1/k/m/a.b.c where t/u/@u1=5; -- xml fatrib 'p/q/@q1/k/m/t/u/@u1' update p/q set k/m/@m1=1, -- rational fatrib 'p/q/k/m/@m1' a/b/@b1/c/d/@d1=3 -- xml fatrib 'p/q/a/b/@b1/c/d/@d1' where t/u/@u1=5 -- rational fatrib 'p/q/t/u/@u1' and t/u/@u1/k/m/@m1=5; -- xml fatrib 'p/q/t/u/@u1/k/m/@m1' update p/q/@q1/r/s set k/m/@m1=1 -- xml fatrib 'p/q/@q1/r/s/k/m/@m1' where t/u/@u1=5; -- xml fatrib 'p/q/@q1/r/s/t/u/@u1'
XPath continues XPath insert into p/q/@@ ( k/m/@m1, -- rational fatrib 'p/q/k/m/@m1' a/b/@b1/c/d/@d1 -- xml fatrib 'p/q/a/b/@b1/c/d/@d1' ) values (1,3) where t/u/@u1=5 -- rational fatrib 'p/q/t/u/@u1' and t/u/@u1/k/m/@m1=5; -- xml fatrib 'p/q/t/u/@u1/k/m/@m1' insert into p/q/@q1/r/s/@@ ( k/m/@m1 -- xml fatrib 'p/q/@q1/r/s/k/m/@m1' ) values (1) where t/u/@u1=5; -- xml fatrib 'p/q/@q1/r/s/t/u/@u1' select * from p/q/a.b.c where t/u/@u1=5 -- rational fatrib 'p/q/t/u/@u1' and t/u/@u1/k/m/@m1=5; -- xml fatrib 'p/q/t/u/@u1/k/m/@m1' select * from p/q/@q1/r/s/a.b.c where t/u/@u1=5; -- rational fatrib 'p/q/@q1/r/s/t/u/@u1'
Conditions for fields from a.b[@b1=5] from a.b[@b1] from a.b.c[@c1=../../@a1] from a.b[position()=1 and @b1=5]*.c from a.b[last() and @b1=5]*.c ‘where’ ‘from’ from a.b where @b1=5 from a.b where @b1 is not null from a.b.c where a/@a1=c/@c1 from a.b*.c where first(b)/@b1=5 from a.b*.c where last(b)/@b1=5 from a.b*.c where @a1 in (select ... )
Permutation and ‘*’, ‘+’ ‘where’ ‘from’ select * from a.b*[@b1~/x/@x1].c; select a.b*.c from a.b*.c, x where @b1~@x1; select * from a.b[@b1 as k1].b[@b1 as m1].b[@b1 as n1].b[@b1 as p1].b[@b1 as q1].b[@b1 as r1].b[@b1 as s1]. ... .c where @k1<>@m1 and @k1<>@n1 and @k1<>@p1 and @k1<>@q1 and @k1<>@r1 and @k1<>@s1 and ... and @m1<>@n1 and @m1<>@p1 and @m1<>@q1 and @m1<>@r1 and @m1<>@s1 and ... and @n1<>@p1 and @n1<>@q1 and @n1<>@r1 and @n1<>@s1 and ... and @p1<>@q1 and @p1<>@r1 and @p1<>@s1 and ... and @q1<>@r1 and @q1<>@s1 and ... and @r1<>@s1 and ... and @k1 in (select @x1 from x) and @m1 in (select @x1 from x) and @n1 in (select @x1 from x) and @p1 in (select @x1 from x) and @q1 in (select @x1 from x) and @r1 in (select @x1 from x) and @s1 in (select @x1 from x) and ... ;
Reject previous designations select a.a1 where b.b1=5 from scheme.tab update a set b=5 update a set b/@c=5 select a/@a1 where b/@b1=5 from scheme§tab update a set @b=5
Nog create table a ( id num primary key, data float ); create table b ( id num primary key, ref1 num references a(id), -- important ref2 num references a(id), -- ballast ref3 num references c(id), -- important ref4 num references c(id), -- ballast data float ); create table c ( id num primary key, data float ); .a.b#ref1:ref3.c.
Buckle create table a ( id num primary key, ref1 num references b(id), -- important ref2 num references b(id), -- ballast data float ); create table b ( id num primary key, data float ); create table c ( id num primary key, ref1 num references b(id), -- important ref2 num references b(id), -- ballast data float ) .a#ref1.b.c#ref3.