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!

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
241 views