Home » Developer & Programmer » JDeveloper, Java & XML » XMLQuery - extract document version (10g Enterprise)
icon9.gif  XMLQuery - extract document version [message #530353] Mon, 07 November 2011 16:12 Go to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous message
Barbara Boehmer
Messages: 9077
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

Previous Topic: Extracting sub nodes from a XML with multiple nodes
Next Topic: Attachment problem
Goto Forum:
  


Current Time: Thu Mar 28 15:27:51 CDT 2024