Errors When Loading XML Data

General
TDW
Enthusiast

Errors When Loading XML Data

Apologies if this is not the right forum.

While I have been developing for the last 16 months in TD, I am a newbie when it comes to this issue.

The DBA who started this project has left and I am trying to work with the senior DBA to get this resolved.

Here is the BTQ.

.logon 111.111.111.111/SOSXML,SOX246
.SET WIDTH 2000

drop table SYSXML.XML_N140_SHRED;
drop table SYSXML.XML_SHRED_DOCS;

CREATE TABLE SYSXML.XML_N140_SHRED
(
    PeriodBeginDate       VARCHAR(128)
);

CREATE TABLE SOSXML.XML_SHRED_DOCS
(
    ID     INTEGER,
    XMLDOC CLOB
);

---- Upload xml document
.IMPORT VARTEXT '|' LOBCOLS=1 FILE='XML_FILES_LIST.txt'
.REPEAT *
USING (a CLOB AS DEFERRED, b VARCHAR(50))
INSERT INTO SOSXML.XML_SHRED_DOCS VALUES(:b, :a);

---- Register XSLT
.IMPORT VARTEXT '|' LOBCOLS=1 FILE='XML_STYLESHEETS_LIST.txt'
USING (a CLOB AS DEFERRED, b VARCHAR(50))
CALL SOSXML.SP_REGISTER_STYLESHEET_LITERAL('XML_N140_STYLESHEET', :a, res);

---- Execute xslt shredding
CALL SOSXML.SHRED_BATCH0('SELECT ID, XMLDOC FROM SOSXML.XML_SHRED_DOCS', 'XML_N140_STYLESHEET', NULL, NULL, res);

---- Cleanup stylesheet
CALL SYSXML.SP_DROP_STYLESHEET('XML_N140_STYLESHEET', res);

.quit

Here is Style Sheet.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
 <xsl:template match="//ReturnHeaderState">
  <Transaction>
   <Insert>
    <Table>
     <SOSXML.XML_N140_SHRED pkey="PeriodBeginDate">
        <xsl:for-each select="ReturnHeaderState">
         <Row>
          <PeriodBeginDate genexp="cast(? as integer)">
           <xsl:value-of select="PeriodBeginDate"/>
          </PeriodBeginDate>
         </Row>
        </xsl:for-each>
     </SOSXML.XML_N140_SHRED>
    </Table>
   </Insert>
  </Transaction>
 </xsl:template>
</xsl:stylesheet>

Here is the error log.

 *** Error: Open failed because: No such file or directory
 *** Error: Use IMPORT to open a file first before
           trying to read from it.
 *** Failure 3593 No DATA parcel sent and request uses a USING clause.
                Statement# 1, Info =0

 *** Error: Open failed because: No such file or directory
 *** Error: Use IMPORT to open a file first before
           trying to read from it.
 *** Failure 3593 No DATA parcel sent and request uses a USING clause.
                Statement# 1, Info =0

Here is the XML_STYLESHEETS_LIST.txt.

XML_NJ1040_STYLESHEET.xsl|1|

Here is the XML_FILES_LIST.txt.

20123.xml|1|

While there is only a single field in the style sheet for "proof of concept purporses'; there will be well over a hundred fields in the final project.

Any assistance is greatly appreciated.

Thanks and God Bless,

Genesius

2 REPLIES
TDW
Enthusiast

Re: Errors When Loading XML Data

There is a typo above.

SOSXML is supposed to be SYSXML.

In the running code this is correct throughout.

Thanks and God Bless,

Genesius

Enthusiast

Re: Errors When Loading XML Data

It looks like it is failing on the first and second steps.  Are the files XML_FILES_LIST.txt and XML_STYLESHEETS_LIST.txt in the directory you are running the code in?  The errors are saying that the file you've specified doesn't exist.  You may want to use the full path to the files in the code to see if that works.

You should also put error checking after each step so that if it fails you code doesn't just carry on.