CODEWITHSUNDEEP
oracle-databaseoracle11gxmltype
Rits
Rits

Reputation: 33

Formatting the data of xmltype in oracle

I am pretty new to XML in oracle, i have a very simple requirement.

I have a function that returns XMLTYPE data as e.g.

<ROWSET>
 <ROW>
  <RCODE>FIRST CHECK DATA</RCODE>
 </ROW>
 <ROW>
  <RCODE>SECOND CHECK DATA</RCODE>
 </ROW>
 <ROW>
  <RCODE>THIRD CHECK DATA</RCODE>
 </ROW>
</ROWSET>

I want to avoid all the tags and display the data in a normal way . I have tried as

DECLARE
l_xmltype XMLTYPE;
l_var VARCHAR2(1000);

BEGIN
l_xmltype := getdata(849,20130804);                   

l_var := l_xmltype.extract('*/ROW/RCODE/text()').getstringval();   
END;

OUTPUT:

FIRST CHECK DATASECOND CHECK DATAJOHNSON KOUL

I want to display the same as

FIRST CHECK DATA

SECOND CHECK DATA

THIRD CHECK DATA

How can i give linefeed or add whitespace in between the rows.

I dont want to save the data in any table.

Upvotes: 2

Views: 617

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

As one of the approaches you can use XMLSequence() function, which returns a collection of top-level nodes, and then apply extractvalue() to get the actual node value. As another approach, and the preferable one(as of oracle 11gr2 the XMLSequence() has been deprecated), you can use XMLTable() function:

Here is a simple function which returns data of XMLType data type:

SQL> create or replace function GetXML return xmltype
  2  is
  3  begin
  4    return xmltype('<ROWSET>
  5                      <ROW>
  6                        <RCODE>FIRST CHECK DATA</RCODE>
  7                      </ROW>
  8                      <ROW>
  9                        <RCODE>SECOND CHECK DATA</RCODE>
 10                      </ROW>
 11                      <ROW>
 12                        <RCODE>THIRD CHECK DATA</RCODE>
 13                      </ROW>
 14                    </ROWSET>');
 15  end;
 16  /

Function created

  1. Using XMLSequence()

    select extractvalue(column_value, '/RCODE') as rcode
  from table(
             xmlsequence(
                         extract(getxml, '/ROWSET/ROW/RCODE')
                        )
             )

    Result:

    RCODE
--------------------
FIRST CHECK DATA
SECOND CHECK DATA
THIRD CHECK DATA

  2. Using XMLTable()

    select Rcode
  from xmltable('/ROWSET/ROW/RCODE'
                  passing getxml columns Rcode varchar2(21) path '/RCODE')

    Result:

     RCODE
 ---------------------
 FIRST CHECK DATA
 SECOND CHECK DATA
 THIRD CHECK DATA

Upvotes: 3

Related Questions