Home » SQL & PL/SQL » SQL & PL/SQL » index by table to xml
index by table to xml [message #205557] Sun, 26 November 2006 00:44 Go to next message
angal_in
Messages: 12
Registered: July 2005
Location: chennai
Junior Member
hi

i have a set of value in an index by table inside a procedure now i like to covert this data in the index by table to xml format is their any way to do this

kindly help me.
Re: index by table to xml [message #205695 is a reply to message #205557] Mon, 27 November 2006 03:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes there is.
Next question?

Cool

At a simple level, you could just step through the table one row at a time, and use XmlElement to wrap each row in xml tags, but that's deeply unstatisfying.
What would be much better would be being able to get all the data out of the table and to create a single piece of XML from that, in one go, which is what I'm going to demonstrate.

Your first problem is to get the data out of the Pl/Sql table in a useable format.
For the purposes of this answer, I@m going to assume your table is of varchar2.

Step 1) Create a SQL type that mirrors the pl/sql type. This will allow us to use the data in SQL, rather than just in pl/sql.
SQL> create or replace type vc2000_table as table of varchar2(2000);
  2  /

Type created.

Step 2) Create a Pipelined Function to return the data out of the pl/sql table and return it in a way we can use in SQL.
For simplicity, I've used this code to populate my pl/sql table as well.
CREATE OR REPLACE PACKAGE PKG_TEST AS
  type plsql_table is table of varchar2(2000) index by binary_integer;
  
  function  piped_output (p_from  in  number
                         ,p_to    in  number) return vc2000_table pipelined;
                                 
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_TEST AS

  function  piped_output (p_from  in  number
                         ,p_to    in  number) return vc2000_table pipelined is
    v_table   plsql_table;
  begin
-- build Pl/Sql table
    for idx in p_from .. p_to loop
      v_table(idx) := 'Table Row '||to_char(idx)||' of '||to_char((p_to-p_from)+1);
    end loop;
  
    for idx in v_table.first .. v_Table.last loop
      if v_Table.exists(idx) then
        pipe row (v_table(idx));
      end if;
    end loop;
    return;
  end piped_output;
  
END;
/


Step 3)
Now we've got a way of getting at the contents of the Pl/Sql table from SQL:
SQL> select * from table(pkg_test.piped_output(1,10));

COLUMN_VALUE
------------------------------------------------------------
Table Row 1 of 10
Table Row 2 of 10
Table Row 3 of 10
Table Row 4 of 10
Table Row 5 of 10
Table Row 6 of 10
Table Row 7 of 10
Table Row 8 of 10
Table Row 9 of 10
Table Row 10 of 10


We can use XmlElement to wrap each line individually in an XML Tag:
SQL> select xmlelement("user_data",column_value) 
  2  from   table(pkg_test.piped_output(1,10));

XMLELEMENT("USER_DATA",COLUMN_VALUE)
----------------------------------------------------
<user_data>Table Row 1 of 10</user_data>
<user_data>Table Row 2 of 10</user_data>
<user_data>Table Row 3 of 10</user_data>
<user_data>Table Row 4 of 10</user_data>
<user_data>Table Row 5 of 10</user_data>
<user_data>Table Row 6 of 10</user_data>
<user_data>Table Row 7 of 10</user_data>
<user_data>Table Row 8 of 10</user_data>
<user_data>Table Row 9 of 10</user_data>
<user_data>Table Row 10 of 10</user_data>

10 rows selected.


Ideally, however, we'd like to get this in one big piece of XML.
To do this, we need to use the MULTISET function to change the set of individual rows of data into a table of data, and then use the XMLCONCAT function to convert this table into one big chunk of XML.
SQL> select xmlelement("all_data",xmlconcat(CAST(MULTISET(select xmlelement("user_data",column_value)
  2                                                       from table(pkg_test.piped_output(1,10))) 
  3                                         AS xmlsequencetype)))
  4  from dual;

XMLELEMENT("ALL_DATA",XMLCONCAT(CAST(MULTISET(SELECTXMLELEMENT("USER_DATA",COLUM
--------------------------------------------------------------------------------
<all_data>
  <user_data>Table Row 1 of 10</user_data>
  <user_data>Table Row 2 of 10</user_data>
  <user_data>Table Row 3 of 10</user_data>
  <user_data>Table Row 4 of 10</user_data>
  <user_data>Table Row 5 of 10</user_data>
  <user_data>Table Row 6 of 10</user_data>
  <user_data>Table Row 7 of 10</user_data>
  <user_data>Table Row 8 of 10</user_data>
  <user_data>Table Row 9 of 10</user_data>
  <user_data>Table Row 10 of 10</user_data>
</all_data>


Any questions?

Re: index by table to xml [message #205703 is a reply to message #205695] Mon, 27 November 2006 03:21 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1578/0/
Re: index by table to xml [message #205713 is a reply to message #205557] Mon, 27 November 2006 03:55 Go to previous messageGo to next message
angal_in
Messages: 12
Registered: July 2005
Location: chennai
Junior Member
thanks so much

pls update if u find some other way of doing this

Regards
Angal
Re: index by table to xml [message #205715 is a reply to message #205713] Mon, 27 November 2006 04:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is Oracle - there are always other ways of doing things.

If we knew more about what you want to do, and why this approach won't work, then we might be able to help more.
Re: index by table to xml [message #205802 is a reply to message #205557] Mon, 27 November 2006 11:57 Go to previous messageGo to next message
angal_in
Messages: 12
Registered: July 2005
Location: chennai
Junior Member
hi my senario is

i have a csv file as below

name,age,sex
raja,22,male
mani,25,male

for each column value the attribute and element is given in a table as below

label_name,attribute, element
name identity clientname
sex identity client sex
age life_identity lifespan
and etc

now i have to read the csv file and convert it to xml using the attribute table as below

<response>
<identity clientname = "raja" client sex="male" />
<life_identity lifespan = "22"/>
</response>

like way i have to create the xml form for all the record in the csv and write it in an xml file.

could you pls suggest a good way to do this.

Regards
Angal
Re: index by table to xml [message #205931 is a reply to message #205802] Tue, 28 November 2006 03:11 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, this doesn't look much like
Quote:
i have a set of value in an index by table inside a procedure

Hey ho.

If I were you, I'd create an External Table to access the CSV as a standard table, and then use something like the solution I gave you above.
Previous Topic: Trigger after Update?
Next Topic: Pivot Query in Oracle 8i
Goto Forum:
  


Current Time: Tue Dec 06 02:34:52 CST 2016

Total time taken to generate the page: 0.05662 seconds