CODEWITHSUNDEEP
sqldatabaseoracle-database
mongo116
mongo116

Reputation: 1

SQL - insert into from one table to another but manipulate data

I am trying to copy data from one table to another by using the following script:

insert into test_report
( company_id
, report_id
, brch_code
, definition
, description
, editable_flag
, executable_flag
, name
, report_type ) 
values
( 2420
, 'RP00002004'
, '0001'
, (select definition from test_template_report where template_id='RP00001242')
, (select description from test_template_report where template_id='RP00001242')
, (select editable_flag from test_template_report where template_id='RP00001242')
, (select executable_flag from test_template_report where template_id='RP00001242')
, (select name from test_template_report where template_id='RP00001242')
, '01' );

This is working fine, but the definition field contains XML which would need to be modified slightly.

The following is part of the definition data:

<listdef page='25'><reportId>RP00000390</reportId><name>Fund Transfer</name><description>Fund Transfer</description>

The <reportId>RP00000390</reportId> part would need to be changed to be RP00002004 as per the insert into script.

Like the following:

<listdef page='25'><reportId>RP00002004</reportId><name>Fund Transfer</name><description>Fund Transfer</description>

Is this possible?

Upvotes: 0

Views: 172

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

You can use XMLQuery with a modify ... replace value of node:

insert into test_report (company_id,report_id,brch_code,definition,description,
  editable_flag,executable_flag,name,report_type)
select 2420, 'RP00002004', '0001',
  XMLQuery('copy $i := $d modify
      (for $j in $i//reportId return replace value of node $j with $r)
      return $i'
    passing definition as "d", 'RP00002004' as "r"
    returning content),
  description, editable_flag, executable_flag, name, '01'
from test_template_report where template_id='RP00001242';

You don't need all the individual selects from the template table, a single insert-select will do.

The XML manipulation assumes definition is an XMLType; if it isn't you can convert it to one in the passing clause, i.e. passing XMLType(definition) as "d". The value of the reportId node (or nodes) is replaced with the string passed as "r".

As a quick static demo of that replacement happening, with the XML supplied in-line as a string literal:

select
  XMLQuery('copy $i := $d modify
      (for $j in $i//reportId return replace value of node $j with $r)
      return $i'
    passing XMLType(q'[<listdef page='25'><reportId>RP00000390</reportId><name>Fund Transfer</name><description>Fund Transfer</description></listdef>]') as "d",
      'RP00002004' as "r"
    returning content)
  as modified_definition
from dual;

MODIFIED_DEFINITION                                                                                                           
------------------------------------------------------------------------------------------------------------------------------
<listdef page="25"><reportId>RP00002004</reportId><name>Fund Transfer</name><description>Fund Transfer</description></listdef>

Read more.

Upvotes: 2

William Robertson
William Robertson

Reputation: 16001

The replace function replaces one text string with another, so you could change

definition

to

replace(definition, '<reportId>RP00000390</reportId>', '<reportId>RP00002004</reportId>')

You can also get all the columns you need from test_template_report in one go:

insert into test_report
     ( company_id
     , report_id
     , brch_code
     , definition
     , description
     , editable_flag
     , executable_flag
     , name
     , report_type )
select 2420
     , 'RP00002004'
     , '0001'
     , replace(tr.definition, '<reportId>RP00000390</reportId>', '<reportId>RP00002004</reportId>')
     , tr.description
     , tr.editable_flag
     , tr.executable_flag
     , tr.name
     , '01'
from   test_template_report tr
where  tr.template_id = 'RP00001242';

If you wanted to replace any value of reportIf and not just 'RP00000390', you could use regexp_replace:

insert into test_report
     ( company_id
     , report_id
     , brch_code
     , definition
     , description
     , editable_flag
     , executable_flag
     , name
     , report_type )
select 2420
     , 'RP00002004'
     , '0001'
     , regexp_replace(definition,'<reportId>[^<]+</reportId>','<reportId>RP00002004</reportId>')
     , tr.description
     , tr.editable_flag
     , tr.executable_flag
     , tr.name
     , '01'
from   test_template_report tr
where  tr.template_id = 'RP00001242';

Upvotes: 0

Related Questions