Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Update XMLTYPE in PL/SQL

MarwimMar 3 2025

I have an XML and I have to update it to hte current Schema definition.

I tried different approaches with transform() but somehow I don't get the correct syntax. Perhaps someone can give me a solution for my three expected changes

<FinInstnId><BICFI>BYLADEMMXXX</BICFI></FinInstnId> --rename node
<Ntry><Sts><Cd>BOOK</Cd></Sts></Ntry>--add node in hierarchy
<PI><PId>86</PId></PI>--delete this node

SQL*Plus: Release 19.0.0.0.0 - Production on Mo Mrz 3 10:54:19 2025
Version 19.3.0.0.0

DECLARE  
myXML XMLTYPE := XMLTYPE('
<doc>
<FinInstnId><BIC>BYLADEMMXXX</BIC></FinInstnId> 
<Ntry><Sts>BOOK</Sts></Ntry>
<PI><PId>86</PId><BB>true</BB></PI>
</doc>
');
xslt XMLTYPE := XMLTYPE(
'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
   <xsl:template match="/">
       <xsl:apply-templates/>
   </xsl:template>
</xsl:stylesheet>'
);
BEGIN 
   myXML := myXML.transform(xslt);
   dbms_output.put_line(myXML.getClobVal());
END;
/
This post has been answered by Marwim on Mar 4 2025
Jump to Answer
Comments
Post Details
Added on Mar 3 2025
8 comments
156 views