//a[./@b]
,
<book isbn="1-2345-6789-0" year="1994"> <title>TCP/IP Illustrated</title> <author><last>Stevens</last><first>John</first></author> <publisher>Addison-Wesley</publisher> <price currency="USD">65.95</price> </book>
> XPath2Rel book.xml "//author" CREATE TABLE z1234567_book_tbl ( pre INTEGER PRIMARY KEY, post INTEGER, level INTEGER, tag VARCHAR(256), text VARCHAR(1000) ); CREATE TABLE z1234567_book_attr ( pre INTEGER REFERENCES z1234567_book_tbl (pre), attr VARCHAR(256), value VARCHAR(1000) ); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (0, 13, 0, null, null); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (1, 12, 1, "book", null); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (2, 2, 2, "title", null); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (3, 1, 3, null, "TCP/IP Illustrated"); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (4, 7, 2, "author", null); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (5, 4, 3, "last", null); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (6, 3, 4, null, "Stevens"); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (7, 6, 3, "first", null); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (8, 5, 4, null, "John"); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (9, 9, 2, "publisher", null); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (10, 8, 3, null, "Addison-Wesley"); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (11, 11, 2, "price", null); INSERT INTO z1234567_book_tbl (pre, post, level, tag, text) VALUES (12, 10, 3, null, "65.95"); INSERT INTO z1234567_book_attr (pre, attr, value) VALUES (1, "isbn", "1-2345-6789-0"); INSERT INTO z1234567_book_attr (pre, attr, value) VALUES (1, "year", "1994"); INSERT INTO z1234567_book_attr (pre, attr, value) VALUES (11, "currency", "USD"); SELECT DISTINCT r2.pre FROM z1234567_book_tbl r1, z1234567_book_tbl r2 WHERE r1.pre=0 AND r2.pre>r1.pre AND r2.post<r1.post AND r2.tag="author" ORDER BY r2.pre; DROP TABLE z1234567_book_attr; DROP TABLE z1234567_book_tbl;
mysql -h cs4317.srvr -u stu1 -pstu1pass studentdb1 < test.sql
For the above example this will produce "pre 4", which
is the correct query answer.
SELECT DISTINCT r2.pre FROM book_tbl r1, book_tbl r2
WHERE r1.pre=0
AND r2.pre>r1.pre
AND r2.post<r1.post
AND r2.tag IS NOT NULL
ORDER BY r2.pre;
It is not important that you produce exactly this query,
but when executed then the query should produce the
correct pre-values.
> XPath2Rel book.xml "//*" | mysql -h cs4317.srvr -u stu1 -pstu1pass studentdb1 pre 1 2 4 5 7 9 11 >
"//a//b//preceding::*//d//preceding::e"
SELECT DISTINCT r8.pre
FROM z12345678_book_tbl r1, z12345678_book_tbl r2,
z12345678_book_tbl r3, z12345678_book_tbl r4,
z12345678_book_tbl r5, z12345678_book_tbl r6,
z12345678_book_tbl r7, z12345678_book_tbl r8
WHERE r1.pre = 0
AND r2.pre > r1.pre AND r2.post < r1.post AND r2.tag = "a"
AND r3.pre > r2.pre AND r3.post < r2.post AND r3.tag = "b"
AND r4.pre >= r3.pre AND r4.post <= r3.post
AND r5.pre < r4.pre AND r5.post < r4.post AND r5.tag IS NOT NULL
AND r6.pre > r5.pre AND r6.post < r5.post AND r6.tag = "d"
AND r7.pre >= r6.pre AND r6.post <= r7.post
AND r8.pre < r7.pre AND r8.post < r7.post AND r8.tag = "e"
ORDERED BY r8.pre;
Here are some examples on book.xml document; recall, however,
that your program need only
"//book/author[./first]"
SELECT DISTINCT r3.pre
FROM z12345678_book_tbl r1, z12345678_book_tbl r2, z12345678_book_tbl r3
WHERE r1.pre = 0
AND r2.pre > r1.pre AND r2.post < r1.post AND r2.tag = "book"
AND r3.pre > r2.pre AND r3.post < r2.post AND r3.level = r2.level+1 AND r3.tag = "author"
AND EXISTS
(SELECT DISTINCT sr1.pre
FROM z12345678_book_tbl sr1
WHERE sr1.pre > r3.pre AND sr1.post < r3.post AND sr1.level = r3.level+1 AND sr1.tag = "first")
ORDER BY r3.pre;
XPath: "//*[/book/price]"
SELECT DISTINCT r2.pre
FROM z12345678_book_tbl r1, z12345678_book_tbl r2
WHERE r1.pre = 0
AND r2.pre > r1.pre AND r2.post < r1.post AND r2.tag IS NOT NULL
AND EXISTS
(SELECT DISTINCT sr2.pre
FROM z12345678_book_tbl sr1, z12345678_book_tbl sr2
WHERE sr1.pre > r1.pre AND sr1.post < r1.post AND sr1.level = r1.level+1 AND sr1.tag = "book"
AND sr2.pre > sr1.pre AND sr2.post < sr1.post AND sr2.level = sr1.level+1 AND sr2.tag = "price")
ORDER BY r2.pre;
XPath: "//*/text()"
SELECT DISTINCT r3.pre
FROM z12345678_book_tbl r1, z12345678_book_tbl r2, z12345678_book_tbl r3
WHERE r1.pre = 0
AND r2.pre > r1.pre AND r2.post < r1.post AND r2.tag IS NOT NULL
AND r3.pre > r2.pre AND r3.post < r2.post AND r3.level = r2.level+1 AND r3.tag IS NULL
ORDER BY r3.pre;
XPath: "//*[@currency='USD']/text()"
SELECT DISTINCT r3.pre
FROM z12345678_book_tbl r1, z12345678_book_tbl r2, z12345678_book_tbl r3
WHERE r1.pre = 0
AND r2.pre > r1.pre AND r2.post < r1.post AND r2.tag IS NOT NULL
AND EXISTS
(SELECT DISTINCT sr1.pre
FROM z12345678_book_attr sr1
WHERE sr1.pre = r2.pre AND sr1.attr = "currency" AND sr1.value = 'USD')
AND r3.pre > r2.pre AND r3.post < r2.post AND r3.level = r2.level+1 AND r3.tag IS NULL
ORDER BY r3.pre;
XPath: "//*[@currency]"
SELECT DISTINCT r2.pre
FROM z12345678_book_tbl r1, z12345678_book_tbl r2
WHERE r1.pre = 0
AND r2.pre > r1.pre AND r2.post < r1.post AND r2.tag IS NOT NULL
AND EXISTS
(SELECT DISTINCT sr1.pre
FROM z12345678_book_attr sr1
WHERE sr1.pre = r2.pre AND sr1.attr = "currency")
ORDER BY r2.pre;
CRICOS Provider Number: 00098G