Building xml with structure from database

Question

title maybe a bit misleading, so i'll try to describe the problem.

I need to build XML with data choosen from database with SELECT statement. The problem is that this is supposed to be pretty big xml which structure that must be consistent with xml already stored in another table. Is there any way to do something like:

new_xml := some xml already existing in database
modify new_xml with data from SELECT statement

I'd like to avoid building the whole xml with xmlelement etc... I'm aware that my description maybe a little chaotic, so if something is not clear i'll try to answer any questions.

Thank you for any help.

u mean you want to fetch data from database in the form of XML format? — Thiyagu ATR
– Thiyagu ATR, Commented Mar 25, 2013 at 12:08
I need to export data from one table (where data is in number, varchar etc. type) to another table (where data is in xml format). There are more than 50 fields to export and XML document has a lot of nodes that are constant so it's pretty big document. I'm wondering if there is a way to avoid building the whole xml document by creating each node with XMLelement. Like taking existing XML document (with constant fields filled) and just change the data i need from SELECT statement. — Jandrejc
– Jandrejc, Commented Mar 25, 2013 at 12:29

DazzaL · Accepted Answer · 2013-03-25 13:10:30Z

you could set a template if the structure is the same for each row. eg:

SQL> create table source
  2  (
  3   id number,
  4   cola varchar2(20),
  5   colb number
  6  );

Table created.

SQL>
SQL> insert into source values (1, 'teststr', 23898.234);

1 row created.

SQL> insert into source values (2, '', -9989.00);

1 row created.

SQL> insert into source values (3, 'test again', 0);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> with xml_template as (select xmltype(
  2  '<root>
  3     <somefield>asdas</somefield>
  4     <somefield2>asdas</somefield2>
  5     <typ>
  6        <col1>.</col1>
  7     </typ>
  8     <col2>.</col2>
  9     <col3>.</col3>
 10  </root>') xml
 11                         from dual)
 12  select updatexml(
 13           xt.xml,
 14           '/root/typ/col1/text()', s.id,
 15           '/root/col2/text()', s.cola,
 16           '/root/col3/text()', s.colb)
 17    from xml_template xt
 18         cross join source s;

UPDATEXML(XT.XML,'/ROOT/TYP/COL1/TEXT()',S.ID,'/ROOT/COL2/TEXT()',S.COLA,'/ROOT/
--------------------------------------------------------------------------------
<root>
  <somefield>asdas</somefield>
  <somefield2>asdas</somefield2>
  <typ>
    <col1>1</col1>
  </typ>
  <col2>teststr</col2>
  <col3>23898.234</col3>
</root>

<root>
  <somefield>asdas</somefield>
  <somefield2>asdas</somefield2>
  <typ>
    <col1>2</col1>
  </typ>
  <col2/>
  <col3>-9989</col3>
</root>

<root>
  <somefield>asdas</somefield>
  <somefield2>asdas</somefield2>
  <typ>
    <col1>3</col1>
  </typ>
  <col2>test again</col2>
  <col3>0</col3>
</root>

so an updatexml just fills in the fields that vary per row.

Collectives™ on Stack Overflow

Building xml with structure from database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related