Oracle: Make XML uppercase regardless of what is passed in

Question

I am receiving XML from a separate application. My oracle proc picks them up and in a FOR loop inserts it into an Oracle table. My Oracle code is UPPERCASE and if the other application does not match this case it does not read the records. At the moment we are forcing both sides to use the same case but for future proofing - Can I convert the passed in XML to UPPERCASE so that it is always read?

    FOR REC IN (SELECT XT.*
                      FROM XMLTABLE ('/UPLOAD_DATA/RECORD'
                               PASSING IN_XML
                               COLUMNS COLA VARCHAR2(30) PATH 'COLA',
                                       COLB VARCHAR2(8) PATH 'COLB',
                                       COLC VARCHAR2(8) PATH 'COLC',
                                       COLD VARCHAR2(20) PATH 'COLD'
                                       ) XT)
    LOOP

Any help is appreciated. I have tried using UPPER in a few places but no joy yet. Thanks.

Be aware that XML element and attribute names are intensionally case-sensitive. Ignoring case is not "future proofing" your code; it is locking-in a poor design decision. — kjhughes, Nov 17 '22 at 18:50
Surely it would do no harm in this instance? Just want to make sure we don't miss data. I appreciate your point though. — Gerard McLaughlin, Nov 17 '22 at 19:17
Making it work for the future would ideally be both sides using the same XML schema. Since that may not be the _case_ now, combine [Converting XML Node Names to Uppercase (XSLT 1.0)](https://stackoverflow.com/a/7802924/20324967) with [How to rename an Oracle XMLTYPE node](https://stackoverflow.com/a/6518635/20324967). Be sure to read the caveats in the first solution. — Jason Seek Well, Nov 17 '22 at 19:31

score 0 · Answer 1 · answered Nov 17 '22 at 19:44

This doesn't seem like a great idea, but you could convert the XMLType to a CLOB, upper-case that, and convert back to XMLType:

PASSING XMLTYPE(UPPER(IN_XML))

Or you could leave the XML as it is and upper-case all the node names:

FROM XMLTABLE ('/*[upper-case(name())="UPLOAD_DATA"]/*[upper-case(name())="RECORD"]'
  PASSING IN_XML
  COLUMNS COLA VARCHAR2(30) PATH '*[fn:upper-case(name())="COLA"]',
          COLB VARCHAR2(8) PATH '*[fn:upper-case(name())="COLB"]',
          COLC VARCHAR2(8) PATH '*[fn:upper-case(name())="COLC"]',
          COLD VARCHAR2(20) PATH '*[fn:upper-case(name())="COLD"]'
) XT

... which is messy and harder to maintain.

fiddle using SQL not PL/SQL but the same approaches should apply.

Oracle: Make XML uppercase regardless of what is passed in

1 Answers1