Parsing XML outputting the content of specific tags in html using PL/SQL

Question

I'm currently having issues parsing some XML using PL/SQL. Essentially I have some XML which is stored in a clob variable supplied by an existing function. I create a new variable of xmltype which is used to convert the clob to an xmltype. I then loop through the contents of this new xmltype variable in an attempt to pull out the content of each of the tags and output it in its own div. However my code currently just outputs the contents of all of tags into just one div which gives the impression that it is not looping through at all.

The XML structure is (obviously with more internal tags nested in return):

 <?xml version="1.0" encoding="UTF-8"?>
    <results>
       <return>
          <createDate> Date 1 Here </createDate>
          <description> Description 1 here </description>
       </return>
       <return>
          <createDate> Date 2 Here </createDate>
          <description> Description 2 here </description>
       </return>
    </results>

And the PLSQL I am using to loop through can be found here:

    -- parsing : Parse the xml and wrap description content in a html div
        l_html_build := '<!DOCTYPE html><html><head></head><body>';
        l_parse_xml := (xmltype(l_xml_response));
        l_parse_xml_index := 1;
  while l_parse_xml.existsNode('//description[' || To_Char(l_parse_xml_index) || ']') > 0 
     loop
        l_html_build := l_html_build || '<div class="description">';
        l_html_build := l_html_build || (xmltype(l_xml_response).extract('//description[' || To_Char(l_parse_xml_index) 
        || ']/text()').getclobval());
        l_html_build := l_html_build || '</div>';
        l_html_build := replace(l_html_build,'&lt;','<');
        l_html_build := replace(l_html_build,'&gt;','>');
        l_html_build := replace(l_html_build,'&amp;lt;','');
        l_html_build := replace(l_html_build,'&amp;gt;','');
        l_html_build := replace(l_html_build,'&amp;nbsp;','&nbsp;');
        l_html_build := l_html_build ||'</body></html>';
        l_parse_xml_index := l_parse_xml_index + 1; 
     end loop;

An explanation of some of the parameters and variables can be found below:

    l_xml_response     clob;  -- XML from another function is stored here
    l_html_build        clob; -- Used as a variable to store the output to be sent to the page
    l_parse_xml        xmltype; -- l_xml_response content is passed into this xmltype variable which is used for parsing
    l_parse_xml_index  number; -- Used as an index to assist with looping through the tags

The output is as follows:

 <div class = "description">
  Description 1 here Description 2 here
 </div>

When it should be:

 <div class = "description">
  Description 1 here
 </div> 
 <div class = "description">
  Description 2 here
 </div>

Any help would be greatly appreciated as I'm a html/css/php/javascript programmer by trade and I've not really done PL/SQL before (And my boss hasn't really provided any real training which doesn't help).

Thanks in advance!

I would create a SQL query using XPath which returns the values of the nodes in question. — user330315
– user330315, Commented Feb 8, 2013 at 13:33
This has to be done with PLSQL code only and not SQL (As I haven't been told which tables I can use) — jezzipin
– jezzipin, Commented Feb 8, 2013 at 13:37
Something like this: sqlfiddle.com/#!4/68b32/66 (but I don't know if this is possible with a CLOB variable inside PL/SQL) — user330315
– user330315, Commented Feb 8, 2013 at 13:44
The XML is already generated from a web service so we cannot set it as we want (as you do here). At the moment, all of the XML is stored in a clob. There must be a modification/tweak to my code above that would solve this situation. This is where I got the idea from: anononxml.blogspot.co.uk/2010/05/… — jezzipin
– jezzipin, Commented Feb 8, 2013 at 13:57
That's just an example, you could use an xml variable instead. Something like this: sqlfiddle.com/#!4/d41d8/7078 (note that SQFiddle cannot show the output from dbms_output but you can run that locally) — user330315
– user330315, Commented Feb 8, 2013 at 14:01

collapsar · Accepted Answer · 2013-02-08 16:39:42Z

1

this seems to be more efficient than jme1988's solution by about a magnitude - at least on a toy example with ~1000 return records. obviously i can't say how this approach will scale to the amount of data you'd be working with (however, it should scale fairly well).

DECLARE
   l_c CLOB;
BEGIN
   l_html_build   := '<!DOCTYPE html><html><head></head><body>';
   l_parse_xml    := xmltype(l_xml_response);
   --
   BEGIN
      WITH xdata AS
         (
            SELECT xmltype ( l_xml_response )   xml
              from dual
         )
    SELECT XMLSERIALIZE ( CONTENT extract(xml, '//description') )  x
      INTO l_c
      FROM xdata
         ;
   END;
   l_html_build := l_html_build || l_c;
   --
   l_html_build := replace(l_html_build,'<description', '<div class="description"');
   l_html_build := replace(l_html_build,'</description>', '</div>');
   l_html_build := replace(l_html_build,'&amp;lt;','&lt;');
   l_html_build := replace(l_html_build,'&amp;gt;','&gt;');
   l_html_build := replace(l_html_build,'&amp;nbsp;','&nbsp;');
   l_html_build := l_html_build ||'</body></html>';
END;

hopefully that stuff meets your needs. regards, carsten

edited Feb 8, 2013 at 16:39

answered Feb 8, 2013 at 16:03

collapsar

17.4k5 gold badges40 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jezzipin Over a year ago

And how would this be adapted to retrieve multiple tags? For example, if there were a status tag in the XML also that I wished to receive?

collapsar Over a year ago

there are 2 code pieces to alter. first change the xpath expression inside the xmlserialize function from //description to (//description|//status|//whatever)``. second, you'd change the first uses of replace` from replace(l_html_build,'<description', '<div class="description"'); to regexp_replace(l_html_build,'<(description|status|whatever)', '<div class="\1"'); and replace(l_html_build,'</description>', '</div>'); to replace(l_html_build,'</(description|status|whatever)>', '</div>');.

collapsar Over a year ago

regarding the xpath pattern, //*[(local-name()='description' or local-name()='status' or local-name()='whatever') may be more efficient. if you know the precise structure of your xml data, you should factor that into the xpath expression to speed up processing substantially. based on your example, like (/results/return/description|/results/return/status|/results/return/whatever).

jezzipin Over a year ago

So was your benchmarking conducted merely on the xmlserialize function? I've performed some testing on both this solution and my solution above and mine is marginally faster.

collapsar Over a year ago

no, i benchmarked the complete code. of course, my tests were far from being exhaustive - anyway, performance depends on the amount of data, dbms settings, machine load, hardware, so the results can nothing but hint to a potential performance gain.

jezzipin · Accepted Answer · 2013-02-08 15:18:30Z

A_horse_with_no_name's suggestion is correct however a more efficient way of doing this would be:

     begin
          for value_set_rec in 
            (select extract(value(x),'/description/text()').getclobval() l_description
                 from table(xmlsequence(xmltype(l_xml_response).extract('//description')))x) 
            loop
              l_html_build := l_html_build || '<div class="description">';
              l_html_build := l_html_build || value_set_rec.l_description;
              l_html_build := l_html_build || '</div>';     
            end loop;

            exception
                when others then
                     --Error handling here
      end;    

        l_html_build := replace(l_html_build,'&lt;','<');
        l_html_build := replace(l_html_build,'&gt;','>');
        l_html_build := replace(l_html_build,'&amp;lt;','');
        l_html_build := replace(l_html_build,'&amp;gt;','');
        l_html_build := replace(l_html_build,'&amp;nbsp;','&nbsp;');
        l_html_build := l_html_build ||'</body></html>';

Collectives™ on Stack Overflow

Parsing XML outputting the content of specific tags in html using PL/SQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related