oracle-xml-dboracle-xml-db-repository

Inserting new tag in XML in xmlDB


Is it possible to insert new tag in the XML which is there in the XML-database?

For example; below is my example which is exists in the database:

<PurchaseOrder 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:noNamespaceSchemaLocation=
    "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <SpecialInstructions>Air Mail</SpecialInstructions>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>

I need to insert new tag <sender>xxxx</sender> where <costCente> is 'S30'.

After inserting/updating the XML, it should be like below:

<PurchaseOrder 
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:noNamespaceSchemaLocation=
    "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <sender>xxx</sender>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <SpecialInstructions>Air Mail</SpecialInstructions>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
  </LineItems>
</PurchaseOrder>

Is it really a possible scenario?

Can any one give me query to do this, if it is possible.

Thanks in advance.


Solution

  • Below query worked for me.

    UPDATE TABLENAME dd SET dd.object_value =  
    insertXMLbefore(dd.object_value,   
    '/c:PurchaseOrder/c:Reference',  
    XMLType('<d:sender xmlns:d="http://www.w3.org/2001/XMLSchema-instance">XXXX</d:sender>'),  
    xmlns:c="http://www.w3.org/2001/XMLSchema-instance"')
    WHERE dd.id in (SELECT dd.id FROM TABLENAME dd,XMLTable(XMLNAMESPACES(
    DEFAULT 'http://www.w3.org/2001/XMLSchema-instance' AS "c"),
    '/PurchaseOrder' PASSING dd.object_value COLUMNS  sender VARCHAR2(8 CHAR) PATH 'c:PurchaseOrder/c:Reference/text()',
    rp VARCHAR2(8 CHAR) PATH 'CostCenter/text()') li
    WHERE sender is null and rp = 'S30');