× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I created a store procedure that needs to read XML file from an ifs folder
Called Excel. As far as I know the piece of code that read
the xml file from the ifs can not be tested from green screen using STRSQL
and it needs to be under commitment so i decided to wrap everthing in a
store procedure where internally I do commit=*CHG but when I run it from I
navigator or Access client solution it doesn't work, complaining that it
couldn't run because commit=*none when I'm doing that internally in the
procedure. I Would Like Someone Explain To Me How can I Run This From I
navigator or Access solution without any error of commitment control.

below is my store procedure
========================
CREATE or replace PROCEDURE GETDATATST
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC GETDATATST
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION COMMIT=*CHG

-- +======================================================================
-- | Main
-- +======================================================================
MAIN:BEGIN

-- +======================================================================
-- | Declare cursor When Item<>''
-- +======================================================================
DECLARE C1 CURSOR
WITH HOLD FOR
Select a.*
from XMLTABLE('Staff/Department/Employee'
passing xmlparse(Document Get_xml_file('/Excel/EmplXML.xml'))
Columns EmployeeNo Integer Path '@Id',
FirstName VarChar(30) Path 'Name/FirstName',
LastName VarChar(30) Path 'Name/LastName',
Title VarChar(30) Default '' Path
'Name/LastName/@Title',
Salary Dec(11, 2) Default 0 Path
'Privacy/Salary',
Currency VarChar(30) Default '' Path
'Privacy/Salary/@Currency') a;

-- +======================================================================
-- | Open CURSOR1 cursor When Item<>''
-- +======================================================================
OPEN C1 ;
SET RESULT SETS CURSOR C1 ;

END MAIN;


and below is the XML file structure
============================================
xml version="1.0" encoding="UTF-8"?>
<Staff>
<Department DeptName="Programming">
<Employee Id="1000">
<Name>
<FirstName>Fritz</FirstName>
<LastName>Fischer</LastName>
</Name>
<Privacy>
<eMail>FF@xxxxxxxxxx</eMail>
<phone CountryCode="0049" AreaCode="08191">9334455</phone>
<phone CountryCode="0049" AreaCode="0721">223777</phone>
<Birthday>1958-05-15</Birthday>
<Salary Currency="EUR">55000</Salary>
</Privacy>
<CostCenter>344</CostCenter>
</Employee>
<Employee Id="1010">
<Name>
<FirstName>Anna</FirstName>
<LastName>Meier</LastName>
</Name>
<Privacy>
<eMail>Anna.Meier@xxxxxx</eMail>
<phone CountryCode="0049" AreaCode="06074">111444</phone>
<Birthday>1974-12-24</Birthday>
<CostCenter>344</CostCenter>
<Salary Currency="EUR">70000</Salary>
</Privacy>
</Employee>
</Department>
</Staff>

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.