Home » Developer & Programmer » JDeveloper, Java & XML » XMLQuery - extract document version (10g Enterprise)
 XMLQuery - extract document version [message #530353] |
Mon, 07 November 2011 16:12  |
ph1975
Messages: 7
Registered: February 2008
Location: Zürich
|
Junior Member |
|
|
Hi folks,
is there a way to accomplish the following XML Query:
<document>
<title revision=1>Old Title
<subtitle revision=1>Oldest Subtitle</subtitle>
<subtitle revision=2>Old Subtitle</subtitle>
<subtitle revision=3>New Subtitle</subtitle>
<subtitle revision=4>Newest Subtitle</subtitle>
</title>
<title revision=2>Newer Title
<subtitle revision=1>New Subtitle</subtitle>
<subtitle revision=2>Newer Subtitle</subtitle>
</title>
<body revision=1>Current Body</body>
<footer revision=2>Current Footer</footer>
</document>
With the query i am looking for hours now, i want to extract the complete XML Document, with only the nodes with the newest revision.
the output should be:
<document>
<title revision=2>Newer title
<subtitle revision=2>Newer Subtitle</subtitle>
</title>
<body revision=1>Current Body</body>
<footer revision=2>Current Footer</footer>
</document>
The document is almost completely free and could have a lot more other nodes like <image> or something else. The requirement is, to always get the newest revision of this node in the output document.
Is this possible?
Thank you very much in advance,
Ph1975
|
|
|
|
| Re: XMLQuery - extract document version [message #530354 is a reply to message #530353] |
Mon, 07 November 2011 18:34  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need to have double quotes around the revision values for valid xml. The only thing that I can think of is to extract the parts, rank them, then put them back together, as shown below. If there are others, such as image, then you will need to modify the code, following the pattern already demonstrated.
SCOTT@orcl_11gR2> set define off
SCOTT@orcl_11gR2> column title format a15
SCOTT@orcl_11gR2> column trev format a4
SCOTT@orcl_11gR2> column subtitle format a15
SCOTT@orcl_11gR2> column strev format a5
SCOTT@orcl_11gR2> with
2 data as
3 (select xmltype (
4 '<document>
5 <title revision="1">Old Title
6 <subtitle revision="1">Oldest Subtitle</subtitle>
7 <subtitle revision="2">Old Subtitle</subtitle>
8 <subtitle revision="3">New Subtitle</subtitle>
9 <subtitle revision="4">Newest Subtitle</subtitle>
10 </title>
11 <title revision="2">Newer Title
12 <subtitle revision="1">New Subtitle</subtitle>
13 <subtitle revision="2">Newer Subtitle</subtitle>
14 </title>
15 <body revision="1">Current Body</body>
16 <footer revision="2">Current Footer</footer>
17 </document>') col
18 from dual)
19 select xmltype
20 ('<document>'
21 || '<title revision="' || trev || '">' || title
22 || '<subtitle revision="' || strev || '">' || subtitle || '</subtitle>'
23 || '</title>'
24 || '<body revision="' || brev || '">' || body || '</body>'
25 || '<footer revision="' || frev || '">' || footer || '</footer>'
26 || '</document>')
27 from (select title, trev, subtitle, strev, body, brev, footer, frev,
28 rank () over
29 (order by trev desc,
30 strev desc,
31 brev desc,
32 frev desc) rk
33 from (select extractvalue (value (x), '/title/text()') title,
34 extractvalue (value (x), '/title/@revision') trev,
35 extractvalue (value (y), '/subtitle/text()') subtitle,
36 extractvalue (value (y), '/subtitle/@revision') strev,
37 extractvalue (value (z), '/body/text()') body,
38 extractvalue (value (z), '/body/@revision') brev,
39 extractvalue (value (a), '/footer/text()') footer,
40 extractvalue (value (a), '/footer/@revision') frev
41 from data t,
42 table (xmlsequence (extract (t.col, '/document/title'))) x,
43 table (xmlsequence (extract (x.column_value, '/title/subtitle'))) y,
44 table (xmlsequence (extract (t.col, '/document/body'))) z,
45 table (xmlsequence (extract (t.col, '/document/footer'))) a))
46 where rk = 1
47 /
XMLTYPE('<DOCUMENT>'||'<TITLEREVISION="'||TREV||'">'||TITLE||'<SUBTITLEREVISION=
--------------------------------------------------------------------------------
<document>
<title revision="2">Newer Title
<subtitle revision="2">Newer Subtitle</subtitle>
</title>
<body revision="1">Current Body</body>
<footer revision="2">Current Footer</footer>
</document>
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: XMLQuery - extract document version [message #530511 is a reply to message #530353] |
Tue, 08 November 2011 12:21 |
ph1975
Messages: 7
Registered: February 2008
Location: Zürich
|
Junior Member |
|
|
Dear Barbara,
many thanks for your help. I´d like to ask one further question:
is there a possible way, to not having the query adapted to the special XML i posted? Meaning, what if a different document with different nodes but same basic <document>...</document> structure is saved into the table.
Is there any generic query, which can deliver the highest revisions, without having to name each node?
Thank you very much in advance,
Philipp
|
|
|
|
| Re: XMLQuery - extract document version [message #530523 is a reply to message #530511] |
Tue, 08 November 2011 14:34 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following assumes that all documents begin with <document> and end with </document> and that there are only two levels of subnodes, such as title and subtitle.
SCOTT@orcl_11gR2> create or replace function new_revs
2 (p_xml in xmltype)
3 return xmltype
4 as
5 v_clob clob;
6 begin
7 v_clob := '<document>';
8 for n in
9 (select distinct n.column_value.getrootelement() nodes
10 from table (xmlsequence (p_xml.extract ('/document/node()'))) n)
11 loop
12 for r in
13 (select '<' || n.nodes || ' revision="' || trev || '">'
14 || tval || '</' || n.nodes || '>' newrev,
15 valx
16 from (select tval, trev, valx,
17 rank () over (order by to_number (trev) desc) rk
18 from (select value (x) valx,
19 extractvalue (value (x), '/' || n.nodes || '/text()') tval,
20 extractvalue (value (x), '/' || n.nodes || '/@revision') trev
21 from table (xmlsequence (extract (p_xml, '/document/' || n.nodes))) x))
22 where rk = 1)
23 loop
24 v_clob := v_clob || r.newrev;
25 for n2 in
26 (select distinct n.column_value.getrootelement() nodes
27 from table (xmlsequence (r.valx.extract ('/' || n.nodes || '/node()'))) n)
28 loop
29 for r2 in
30 (select '<' || n2.nodes || ' revision="' || trev || '">'
31 || tval || '</' || n2.nodes || '>' newrev
32 from (select tval, trev,
33 rank () over (order by to_number (trev) desc) rk
34 from (select extractvalue (value (x), '/' || n2.nodes || '/text()') tval,
35 extractvalue (value (x), '/' || n2.nodes || '/@revision') trev
36 from table (xmlsequence (extract (r.valx, '/' || n.nodes || '/' || n2.nodes))) x))
37 where rk = 1)
38 loop
39 v_clob := v_clob || r2.newrev;
40 end loop;
41 end loop;
42 end loop;
43 end loop;
44 v_clob := v_clob || '</document>';
45 return xmltype (v_clob);
46 end new_revs;
47 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> column title format a15
SCOTT@orcl_11gR2> column trev format a4
SCOTT@orcl_11gR2> column subtitle format a15
SCOTT@orcl_11gR2> column strev format a5
SCOTT@orcl_11gR2> with
2 data as
3 (select xmltype (
4 '<document>
5 <title revision="1">Old Title
6 <subtitle revision="1">Oldest Subtitle</subtitle>
7 <subtitle revision="2">Old Subtitle</subtitle>
8 <subtitle revision="3">New Subtitle</subtitle>
9 <subtitle revision="4">Newest Subtitle</subtitle>
10 </title>
11 <title revision="2">Newer Title
12 <subtitle revision="1">New Subtitle</subtitle>
13 <subtitle revision="2">Newer Subtitle</subtitle>
14 </title>
15 <body revision="1">Current Body</body>
16 <footer revision="2">Current Footer</footer>
17 </document>') col
18 from dual)
19 select new_revs (col)
20 from data
21 /
NEW_REVS(COL)
--------------------------------------------------------------------------------
<document>
<title revision="2">Newer Title
</title>
<subtitle revision="2">Newer Subtitle</subtitle>
<footer revision="2">Current Footer</footer>
<body revision="1">Current Body</body>
</document>
1 row selected.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: XMLQuery - extract document version [message #530526 is a reply to message #530523] |
Tue, 08 November 2011 16:38 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following revised function is recursive and more generic. It should handle any number of levels of sub-nodes and does not have to start with document.
SCOTT@orcl_11gR2> create or replace function new_revs
2 (p_xml in xmltype)
3 return xmltype
4 as
5 v_node varchar2 (2000);
6 v_clob clob;
7 v_clob2 clob;
8 begin
9 v_node :=
10 rtrim
11 (substr
12 (p_xml.getclobval,
13 instr (p_xml.getclobval, '</', -1, 1) + 2),
14 '>');
15 v_clob := '<' || v_node || '>';
16 for n in
17 (select distinct n.column_value.getrootelement() nodes
18 from table (xmlsequence (p_xml.extract ('/' || v_node || '/node()'))) n)
19 loop
20 for r in
21 (select tval, trev, valx
22 from (select tval, trev, valx,
23 rank () over (order by to_number (trev) desc nulls last) rk
24 from (select value (x) valx,
25 extractvalue (value (x), '/' || n.nodes || '/text()') tval,
26 extractvalue (value (x), '/' || n.nodes || '/@revision') trev
27 from table (xmlsequence (extract (p_xml, '/' || v_node || '/' || n.nodes))) x))
28 where rk = 1)
29 loop
30 v_clob := v_clob || '<' || n.nodes
31 || case when r.trev is not null
32 then ' revision="' || r.trev || '"'
33 else null
34 end
35 || '>' || r.tval;
36 v_clob2 := new_revs(r.valx).getclobval;
37 v_clob2 := substr (v_clob2, instr (v_clob2, '<', 1, 2));
38 v_clob2 := substr (v_clob2, 1, instr (v_clob2, '>', -1, 2));
39 v_clob := v_clob || v_clob2 || '</' || n.nodes || '>';
40 end loop;
41 end loop;
42 v_clob := v_clob || '</' || v_node || '>';
43 return xmltype (v_clob);
44 end new_revs;
45 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
SCOTT@orcl_11gR2> column title format a15
SCOTT@orcl_11gR2> column trev format a4
SCOTT@orcl_11gR2> column subtitle format a15
SCOTT@orcl_11gR2> column strev format a5
SCOTT@orcl_11gR2> with
2 data as
3 (select xmltype (
4 '<document>
5 <title revision="1">Old Title
6 <subtitle revision="1">Oldest Subtitle</subtitle>
7 <subtitle revision="2">Old Subtitle</subtitle>
8 <subtitle revision="3">New Subtitle</subtitle>
9 <subtitle revision="4">Newest Subtitle</subtitle>
10 </title>
11 <title revision="2">Newer Title
12 <subtitle revision="1">New Subtitle</subtitle>
13 <subtitle revision="2">Newer Subtitle</subtitle>
14 </title>
15 <body revision="1">Current Body</body>
16 <footer revision="2">Current Footer</footer>
17 </document>') col
18 from dual)
19 select new_revs (col)
20 from data
21 /
NEW_REVS(COL)
--------------------------------------------------------------------------------
<document>
<title revision="2">Newer Title
<subtitle revision="2">Newer Subtitle</subtitle>
</title>
<footer revision="2">Current Footer</footer>
<body revision="1">Current Body</body>
</document>
1 row selected.
SCOTT@orcl_11gR2>
[Updated on: Tue, 08 November 2011 17:04] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Mon Feb 03 17:26:55 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
