Home » Developer & Programmer » JDeveloper, Java & XML » Extract XML data into columns
Extract XML data into columns [message #677503] Tue, 24 September 2019 00:50 Go to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi All,
I want to extract the XML data into separate column.

Below is the XML . I want to display each series code as a column and its corresponding value.

<PivotSet>
<item>
<column name = "SERIES_CODE">FY1</column>
<column name = "VAL">3.505331702434</column>
</item>
<item>
<column name = "SERIES_CODE">FY2</column>
<column name = "VAL">3.357474212727</column>
</item>
</PivotSet>

Expected output is
FY1       FY2
3.5053    3.3574
Re: Extract XML data into columns [message #677507 is a reply to message #677503] Tue, 24 September 2019 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (
  2    select xmltype('<PivotSet>
  3  <item>
  4  <column name = "SERIES_CODE">FY1</column>
  5  <column name = "VAL">3.505331702434</column>
  6  </item>
  7  <item>
  8  <column name = "SERIES_CODE">FY2</column>
  9  <column name = "VAL">3.357474212727</column>
 10  </item>
 11  </PivotSet>') data from dual
 12    ),
 13    extracted as (
 14      select rownb-1 rownb, name, val
 15      from data,
 16           xmltable ('/PivotSet/item/column' passing data
 17                     columns
 18                       rownb for ordinality,
 19                       name varchar2(50) path '@name',
 20                       val  varchar2(50) path '.'
 21                    )
 22    )
 23  select max(decode(trunc(rownb/2),0,val)) item1,
 24         max(decode(trunc(rownb/2),1,val)) item2
 25  from extracted
 26  group by mod(rownb,2)
 27  order by mod(rownb,2)
 28  /
ITEM1                                              ITEM2
-------------------------------------------------- --------------------------------------------------
FY1                                                FY2
3.505331702434                                     3.357474212727

2 rows selected.
Re: Extract XML data into columns [message #677508 is a reply to message #677507] Tue, 24 September 2019 01:40 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Thanks Michel.

Can we get the column details dynamically?

suppose if we get one more item tag for "FY3" in the XML , how can we get it dynamically?
Re: Extract XML data into columns [message #677511 is a reply to message #677508] Tue, 24 September 2019 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not in pure SQL which defines the number of columns when the query is written and so is fixed.
You can get it using PL/SQL dynamically building the SQL text and executing it, for instance.

[Updated on: Tue, 24 September 2019 01:53]

Report message to a moderator

Re: Extract XML data into columns [message #677517 is a reply to message #677511] Tue, 24 September 2019 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's an example of what can be done.

Setting the source table:
SQL> drop table t;

Table dropped.

SQL> create table t (data)
  2  as select xmltype('<PivotSet>
  3  <item>
  4  <column name = "SERIES_CODE">FY1</column>
  5  <column name = "VAL">3.505331702434</column>
  6  </item>
  7  <item>
  8  <column name = "SERIES_CODE">FY2</column>
  9  <column name = "VAL">3.357474212727</column>
 10  </item>
 11  </PivotSet>') data from dual;

Table created.

SQL> commit;

Commit complete.

Creating the procedure and executing it:
SQL> create or replace procedure getItems (rc out sys_refcursor)
  2  is
  3    data    xmltype;
  4    nbitems pls_integer;
  5    sqltext varchar2(32765);
  6  begin
  7    select regexp_count(data,'<item>') into nbitems from t;
  8    sqltext := q'[with
  9    extracted as (
 10      select rownb-1 rownb, name, val
 11      from t,
 12           xmltable ('/PivotSet/item/column' passing data
 13                     columns
 14                       rownb for ordinality,
 15                       name varchar2(50) path '@name',
 16                       val  varchar2(50) path '.'
 17                    )
 18    )
 19  select ]';
 20    for i in 1..nbitems loop
 21      sqltext := sqltext || '
 22         max(decode(trunc(rownb/2),'||(i-1)||',val)) item'||i||',';
 23    end loop;
 24    sqltext := rtrim(sqltext,',') || '
 25  from extracted
 26  group by mod(rownb,2)
 27  order by mod(rownb,2)';
 28    open rc for sqltext;
 29  end;
 30  /

Procedure created.

SQL> var rc refcursor
SQL> exec getItems(:rc)

PL/SQL procedure successfully completed.

SQL> col item1 format a30
SQL> col item2 format a30
SQL> col item3 format a30
SQL> print rc
ITEM1                          ITEM2
------------------------------ ------------------------------
FY1                            FY2
3.505331702434                 3.357474212727

2 rows selected.

Modifying the data (adding a new item) and re-executing the procedure:
SQL> update t set data = xmltype('<PivotSet>
  2  <item>
  3  <column name = "SERIES_CODE">FY1</column>
  4  <column name = "VAL">3.505331702434</column>
  5  </item>
  6  <item>
  7  <column name = "SERIES_CODE">FY2</column>
  8  <column name = "VAL">3.357474212727</column>
  9  </item>
 10  <item>
 11  <column name = "SERIES_CODE">FY3</column>
 12  <column name = "VAL">3.1415926535897932384626</column>
 13  </item>
 14  </PivotSet>');

1 row updated.

SQL> commit;

Commit complete.

SQL> exec getItems(:rc)

PL/SQL procedure successfully completed.

SQL> print rc
ITEM1                          ITEM2                          ITEM3
------------------------------ ------------------------------ ------------------------------
FY1                            FY2                            FY3
3.505331702434                 3.357474212727                 3.1415926535897932384626

2 rows selected.

[Updated on: Tue, 24 September 2019 03:49]

Report message to a moderator

icon14.gif  Re: Extract XML data into columns [message #677540 is a reply to message #677517] Tue, 24 September 2019 23:50 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Thank you Michel. Razz
Re: Extract XML data into columns [message #678804 is a reply to message #677540] Tue, 14 January 2020 23:50 Go to previous message
Louise M Marquez
Messages: 1
Registered: January 2020
Location: United Kingdom
Junior Member
This is informative
Previous Topic: If condition for LOV columns
Next Topic: ORA-29532: Java call terminated by uncaught Java exception: java.lang.AbstractMethodError
Goto Forum:
  


Current Time: Thu Mar 28 04:54:12 CDT 2024