DevOps, CI/CD and Automation

Hello All,

   I have a XQuery that reads from an XML Table. I use it to flatten the XML Data and load into regular (staging) tables created for the purpose in our database. There were no issues for the first few months, but in the last 5 weeks, I have had three instances where the XQuery fails to return any data from the XML Table.

I tried loading the same XML files on a different instance and encountered the same issue. The issue disappeared on its own every single time for the subsequent XML files. I'm not sure if there is anything particularly different about the data in these specific files.

Is there anything specific I have to keep an eye out for in the data? I don't see any error messages as well. The XQuery simply does not return any rows.

Kindly let me know if you need any more specific information. To provide the XML files in question as a sample here, I will need approval from my Supervisors. 

Here is the query for the very first level of data in the XML File.

      SELECT x.*

        FROM <XMLTABLENAME> t

        ,XMLTABLE('/xml/item[@key="awards"]/item'

                  PASSING t.object_value

                  COLUMNS key          INTEGER PATH '@key'

                 ,accountNumber        INTEGER PATH 'item[@key="accountNumber"]'

                 ,awardCloseoutItems   XMLTYPE PATH 'item[@key="awardCloseoutItems"]/item'

                 ,accountTypeCode      VARCHAR2(3) PATH 'item[@key="accountTypeCode"]'

                 ,activityTypeCode     VARCHAR2(3) PATH 'item[@key="activityTypeCode"]'

                 ,awardAmountInfos     XMLTYPE PATH 'item[@key="awardAmountInfos"]/item'

                 ,awardFandaRate       XMLTYPE PATH 'item[@key="awardFandaRate"]/item'

                 ,awardEffectiveDate   DATE PATH 'item[@key="awardEffectiveDate"]'

                 ,awardId              INTEGER PATH 'item[@key="awardId"]'

                 ,awardNumber          VARCHAR2(30) PATH 'item[@key="awardNumber"]'

                 ,awardReportTerms     XMLTYPE PATH 'item[@key="awardReportTerms"]/item'

                 ,awardSponsorContacts XMLTYPE PATH 'item[@key="awardSponsorContacts"]/item'

                 ,budgets              XMLTYPE PATH 'item[@key="budgets"]/item'

                 ,cfdaNumber           VARCHAR2(7) PATH 'item[@key="cfdaNumber"]' 

                 ,id                   INTEGER PATH 'item[@key="id"]'

                 ,leadUnitNumber       INTEGER PATH 'item[@key="leadUnitNumber"]'

                 ,noticeDate           DATE PATH 'item[@key="noticeDate"]'

                 ,primeSponsorCode     VARCHAR2(15) PATH 'item[@key="primeSponsorCode"]'

                 ,projectPersons       XMLTYPE PATH 'item[@key="projectPersons"]/item'

                 ,sponsorAwardNumber   VARCHAR2(240) PATH 'item[@key="sponsorAwardNumber"]'

                 ,sponsor              XMLTYPE PATH 'item[@key="sponsor"]/item'

                 ,statusCode           INTEGER PATH 'item[@key="statusCode"]'

                 ,title                VARCHAR2(240) PATH 'item[@key="title"x

                 where x.accountNumber between 70000 and 79999;

Thanks

-Madhuri