CODEWITHSUNDEEP
xmloracle-database
user5005768Himadree
user5005768Himadree

Reputation: 1427

How to Add and Remove xml tags of existing data inside xmltype column in oracle

I have created the following table: enter image description here

Here is 1 data row sample:

enter image description here

Inside this table, at column SALPROFILE, each column data contains a big xml data like following sample:

<employee>
  <id>FMCSC00015</id>
  <year>2016</year>
  <month>1</month>
  <PAYMSTR_SALHDNM>BASIC PAY</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>35600</PAYMSTR_AMOUNT>
  <PAYMSTR_SALHDNM>ASSOCIATION SUBSCRIPTION</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>240</PAYMSTR_AMOUNT>
  <PAYMSTR_SALHDNM>TELEPHONE ALLOWANCE</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>800</PAYMSTR_AMOUNT>
  <PAYMSTR_SALHDNM>HOUSE RENT DEDUCTION</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>2587.5</PAYMSTR_AMOUNT>
  <PAYMSTR_SALHDNM>MEDICAL ALLOWANCE</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>700</PAYMSTR_AMOUNT>
  <PAYMSTR_SALHDNM>GAS BILL</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>450</PAYMSTR_AMOUNT>  
  <PAYMSTR_SALHDNM>LIFE INSURANCE PREMIUM (D)</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>1718</PAYMSTR_AMOUNT>  
</employee>

i want to Add and Remove xml tags from above sample. For example, i want to remove 

<PAYMSTR_SALHDNM>BASIC PAY</PAYMSTR_SALHDNM>
      <PAYMSTR_AMOUNT>35600</PAYMSTR_AMOUNT>

and i want to add

 <PAYMSTR_SALHDNM>BANK LOAN</PAYMSTR_SALHDNM>
  <PAYMSTR_AMOUNT>23490</PAYMSTR_AMOUNT>

How can i do this.is this possible doing it without tracing the entire xml data to make it more faster.I saw that Appendchildxml function deprecated

Upvotes: 1

Views: 3289

Answers (1)

Aleksej
Aleksej

Reputation: 22949

You can modify your XML by adding/deleting tags with something like the following, by the means of AppendChildXML and deleteXML.

In the example, the first part returns your XML with one tag more, while the second column deletes a tag that matches the condition between [...]:

with xmlTab(val) as (
    select xmlType('<employee>
      <id>FMCSC00015</id>
      <year>2016</year>
      <month>1</month>
      <PAYMSTR_SALHDNM>BASIC PAY</PAYMSTR_SALHDNM>
      <PAYMSTR_AMOUNT>35600</PAYMSTR_AMOUNT>
      <PAYMSTR_SALHDNM>ASSOCIATION SUBSCRIPTION</PAYMSTR_SALHDNM>
      <PAYMSTR_AMOUNT>240</PAYMSTR_AMOUNT>
      <PAYMSTR_SALHDNM>TELEPHONE ALLOWANCE</PAYMSTR_SALHDNM>
      <PAYMSTR_AMOUNT>800</PAYMSTR_AMOUNT>
      <PAYMSTR_SALHDNM>HOUSE RENT DEDUCTION</PAYMSTR_SALHDNM>
      <PAYMSTR_AMOUNT>2587.5</PAYMSTR_AMOUNT>
      <PAYMSTR_SALHDNM>MEDICAL ALLOWANCE</PAYMSTR_SALHDNM>
      <PAYMSTR_AMOUNT>700</PAYMSTR_AMOUNT>
      <PAYMSTR_SALHDNM>GAS BILL</PAYMSTR_SALHDNM>
      <PAYMSTR_AMOUNT>450</PAYMSTR_AMOUNT>  
      <PAYMSTR_SALHDNM>LIFE INSURANCE PREMIUM (D)</PAYMSTR_SALHDNM>
      <PAYMSTR_AMOUNT>1718</PAYMSTR_AMOUNT>  
    </employee>')
    from dual
    )
    select APPENDCHILDXML( val,
                           'employee',
                           XMLType('<PAYMSTR_SALHDNM>SONALI BANK LOAN-4</PAYMSTR_SALHDNM>')
                         ) as APPEND,  
           DELETEXML(val,'/employee/PAYMSTR_AMOUNT[.="1718"]') AS DEL
    from xmlTab

The XML to add may be dinamically built by reading data from another table, for example in this way:

with test as 
(
    select 'PAYMSTR_SALHDNM' tag, 'SONALI BANK LOAN-4' val from dual union all
    select 'PAYMSTR_SALHDNM' tag, 'GAS BILL' val from dual
)     
select XMLELEMENT(tag, val) from test

Upvotes: 1

Related Questions