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.
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');