Error Problem in reading XML using dataset

John

Member
Hello Team,

Somehow temp table "ttCustomer" is not getting populated while values for flag "lReturn" is coming true. Kindly suggest what I am doing incorrect. There can be multiple "<CallOFF>" tags under one "<PurchaseRequisition>" tag.
PFB the progress code and XML file:
Thank You.

Code:
DEFINE VARIABLE hPDS    AS HANDLE  NO-UNDO.
DEFINE VARIABLE lReturn AS LOGICAL NO-UNDO.
DEFINE VARIABLE cSourceType             AS CHARACTER NO-UNDO.
DEFINE VARIABLE cReadMode               AS CHARACTER NO-UNDO.
DEFINE VARIABLE lOverrideDefaultMapping AS LOGICAL   NO-UNDO.
DEFINE VARIABLE cFile                   AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSchemaLocation         AS CHARACTER NO-UNDO.

DEFINE TEMP-TABLE ttCustomer NO-UNDO
  FIELD ttOrdM   AS CHARACTER
  FIELD ttOrdNum AS CHARACTER.

DEFINE TEMP-TABLE ttOrder  NO-UNDO
  FIELD ttOrdNum  AS CHARACTER
  FIELD ttQty     AS CHARACTER
  FIELD ttName    AS CHARACTER.
 
DEFINE DATASET PurchaseRequisitions FOR ttCustomer, ttOrder
  DATA-RELATION custOrd FOR ttCustomer, ttOrder
    RELATION-FIELDS(ttOrdNum, ttOrdNum) NESTED.

hPDS = DATASET PurchaseRequisitions:HANDLE.

ASSIGN
  cSourceType             = "FILE"
  cFile                   = "/qond/apps/itg/work/src/a.XML"
  cReadMode               = "EMPTY"
  cSchemaLocation         = ?
  lOverrideDefaultMapping = NO.
 
lReturn = hPDS:READ-XML (cSourceType, cFile, cReadMode,
  cSchemaLocation, lOverrideDefaultMapping).

IF lReturn  THEN DO:
  MESSAGE "OK" view-as alert-box.
END.
ELSE
  MESSAGE "FAILED" view-as alert-box.
 
   FOR EACH ttCustomer:
        message "In ttCust ttOrdM   = " ttCustomer.ttOrdM   skip
                "ttOrdNum = " ttCustomer.ttOrdNum view-as alert-box.
    
   END.

XML:
<?xml version="1.0" encoding="UTF-8"?>
<PurchaseRequisitions>
  <PurchaseRequisition>
     <ORDER_METHOD>C</ORDER_METHOD>
     <ORDER_NUMBER>Ab11</ORDER_NUMBER>
   <CallOFF>
      <ORDER_NUMBER>Ab11</ORDER_NUMBER>
      <qty>1</qty>
      <name>RG</name>
   </CallOFF>
   <CallOFF>
     <ORDER_NUMBER>Ab11</ORDER_NUMBER>
     <qty>2</qty>
     <name>RG2</name>
  </CallOFF>
 </PurchaseRequisition>
</PurchaseRequisitions>
 

TomBascom

Curmudgeon
Kindly suggest what I am doing incorrect.

Upper case keywords and hungarian notation leap off the screen as wrong to my eyes. (Hang on a moment, I need to flush them out after seeing that.)

Aside from my burning eyes... if I understand what you are trying to do -- I think you may need to use SERIALIZE-NAME to have CallOFF seen as a ttOrder.
 

TomBascom

Curmudgeon
Adding serialize-name to the TT definitions helped but looking at your xml you seem to have 3 different tables not 2.
 

John

Member
Thanks for your reply Tom.

A do not see where you have made the changes in my code. Could you please send updated & working snippet.
There are only tow TT:

1) ttCustomer for <PurchaseRequisition>
2) ttOrder for <CallOFF>

Regards
 

TomBascom

Curmudgeon
I didn't post any changed code. I just added "SERIALIZE-NAME" to everything since your table and field names do not match your element names.

By "3 tables" I mean that your XML appears to contain: "PurchaseRequisitions", "PurchaseRequisition", and "CallOFF". To my eye "PurchaseRequistions" (with an "s") looks like another layer of table relationships. Since you say you only want 2 tables I removed the "PurchaseRequisitions" start and end tags and that, combined with the SERIALIZE-NAME additions resulted in the ttOrder table being populated. ttCustomer did not get populated which might just be a typo or silly error on my part but my time budget for this question is already running over and it is a very nice weekend day that just begs for me to be outside :)

In another XML related thread I mentioned that one approach that I often take with these problems is to work it backwards -- create a program that executes WRITE-XML to create your sample XML file. That will show you exactly what Progress is expecting to see for READ-XML to function correctly.

Anyhow -- this my latest (not quite working) code:
Code:
DEFINE VARIABLE hPDS    AS HANDLE  NO-UNDO.
DEFINE VARIABLE lReturn AS LOGICAL NO-UNDO.
DEFINE VARIABLE cSourceType             AS CHARACTER NO-UNDO.
DEFINE VARIABLE cReadMode               AS CHARACTER NO-UNDO.
DEFINE VARIABLE lOverrideDefaultMapping AS LOGICAL   NO-UNDO.
DEFINE VARIABLE cFile                   AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSchemaLocation         AS CHARACTER NO-UNDO.

DEFINE TEMP-TABLE ttCustomer NO-UNDO serialize-name "PurchaseRequistition"
  FIELD ttOrdM   AS CHARACTER serialize-name "ORDER_METHOD"
  FIELD ttOrdNum AS CHARACTER serialize-name "ORDER_NUMBER"
.

DEFINE TEMP-TABLE ttOrder  NO-UNDO serialize-name "CallOFF"
  FIELD ttOrdNum  AS CHARACTER serialize-name "ORDER_NUMBER"
  FIELD ttQty     AS CHARACTER serialize-name "qty"
  FIELD ttName    AS CHARACTER serialize-name "name".

DEFINE DATASET PurchaseRequisitions FOR ttCustomer, ttOrder
  DATA-RELATION custOrd FOR ttCustomer, ttOrder
    RELATION-FIELDS(ttOrdNum, ttOrdNum) NESTED.

hPDS = DATASET PurchaseRequisitions:HANDLE.

ASSIGN
  cSourceType             = "FILE"
  cFile                   = "sample.xml"
  cReadMode               = "EMPTY"
  cSchemaLocation         = ?
  lOverrideDefaultMapping = no.

lReturn = hPDS:READ-XML (cSourceType, cFile, cReadMode,
  cSchemaLocation, lOverrideDefaultMapping).

IF lReturn  THEN DO:
  MESSAGE "OK" view-as alert-box.
END.
ELSE
  MESSAGE "FAILED" view-as alert-box.

for each ttCustomer:
  display ttCustomer.
end.
pause.

for each ttOrder:
  display ttOrder.
end.
pause.

and the modified XML:
Code:
<?xml version="1.0" encoding="UTF-8"?>
  <PurchaseRequisition>
     <ORDER_METHOD>C</ORDER_METHOD>
     <ORDER_NUMBER>Ab11</ORDER_NUMBER>
   <CallOFF>
      <ORDER_NUMBER>Ab11</ORDER_NUMBER>
      <qty>1</qty>
      <name>RG</name>
   </CallOFF>
   <CallOFF>
     <ORDER_NUMBER>Ab11</ORDER_NUMBER>
     <qty>2</qty>
     <name>RG2</name>
  </CallOFF>
 </PurchaseRequisition>
 

Bounty

New Member
Upper case keywords and hungarian notation leap off the screen as wrong to my eyes. (Hang on a moment, I need to flush them out after seeing that.)

Aside from my burning eyes...
The Progress documentation is all upper case and coming from languages using lower case it made my eyes burn too. The existing code base I'm working with is all upper case and it makes no sense changing that. I was told that using upper case is shouting or COBOL, or ... (yes, other ancient stuff)

As for hungarian notation check this:
OpenEdge 11.7 Documentation
 

RealHeavyDude

Well-Known Member
The Progress documentation is notorious for bad coding style examples and even bad practice. As always you have to take the examples with a grain of salt - at least I do as there are only there to illustrate usage of an isolated feature.
 

John

Member
Thanks for this Tom.

I am not able to figure out why Temp Table ttCustomer is somehow not getting populated. Could you please advise? Thank You.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I can't help you with why your temp-table isn't being populated. But I have read the code and the XML file and I'm struggling to understand this from a business perspective.

You have temp-tables for orders and customers, that makes sense so far. I would expect you can have more than one customer, a customer can have more than one order, and an order has exactly one customer. But then I look at your XML and I no longer get it.

It has a "PurchaseRequistion" element, which sounds like a synonym for an order, but apparently it maps to ttCustomer. Though its only members are order method and order numbers, which sound like attributes of an order, not a customer.

Then you have "CallOFF" elements which map to ttOrder. But the two rows have the same order number, with different combinations of name and quantity. So presumably these are what I would think of as order-lines; elements of an individual order. The XML looks like it contains orders and order-lines but no customers.

It might be easier to figure out what the code should do and how it should be written if the XML and data model had appropriate, comprehensible names.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Anyhoo, having said all that, it looks like the problem is a typo in "PurchaseRequisition":
Code:
DEFINE TEMP-TABLE ttCustomer NO-UNDO serialize-name "PurchaseRequistition"

Note the extra "t". Change that and you get a "ttCustomer" record.
 

TomBascom

Curmudgeon
Anyhoo, having said all that, it looks like the problem is a typo in "PurchaseRequisition":
Code:
DEFINE TEMP-TABLE ttCustomer NO-UNDO serialize-name "PurchaseRequistition"

Note the extra "t". Change that and you get a "ttCustomer" record.

So it was indeed a "silly typo" ;)
 

TomBascom

Curmudgeon
The Progress documentation is notorious for bad coding style examples and even bad practice. As always you have to take the examples with a grain of salt - at least I do as there are only there to illustrate usage of an isolated feature.

Exactly.

KEYWORDS ALL UPPERCASE is a typesetting convention from the dark ages. It was old-fashioned when I was young (which was a long, long time ago -- just ask my kids). It looks ok on paper sometimes. Mostly when it is a small code sample embedded in a lot of other text. It is sort of ok in an online discussion forum where you are mixing a small amount of code and commentary. It is otherwise terrible looking and hard to read. IMHO.
 

TomBascom

Curmudgeon
The Progress documentation is all upper case and coming from languages using lower case it made my eyes burn too. The existing code base I'm working with is all upper case and it makes no sense changing that. I was told that using upper case is shouting or COBOL, or ... (yes, other ancient stuff)

I would be ashamed to have anyone think that my code looks anything like documentation examples. As RHD says, those samples are intended to illustrate very narrow concepts and they are a far cry from anything that good be described as "best practices". In fact they are a very rich source of "practices to be avoided at all costs".

As for not changing UPPER CASE to lower (or mixed) case... consider the cost savings from wear-leveling on the memory chips.

As for hungarian notation check this:
OpenEdge 11.7 Documentation

Are we talking about the same concept? That seems to be a link to how to go about coding READ-XML with a ProDataSet. In fact it looks remarkably similar to our original poster's sample code. Which nicely illustrates why you shouldn't just copy stuff from the documentation and try to use it for real code ;)
 

Cecil

19+ years progress programming and still learning.
If I have time this evening I can look at it and come up with suggestions.
Is it possible for the XML to change it structure or the XML provided to you from some other external source?
 

Cecil

19+ years progress programming and still learning.
11 minutes of coding...Minimal testing...Zero optimization i.e. Indices

Since you are using XML as a data source, I opted for XML-NODE-NAME attribute rather than the SERIALIZE-NAME.

Let us know how you got on.

Code:
DEFINE VARIABLE lReturn AS LOGICAL NO-UNDO.
DEFINE VARIABLE cSourceType             AS CHARACTER NO-UNDO.
DEFINE VARIABLE cReadMode               AS CHARACTER NO-UNDO.
DEFINE VARIABLE lOverrideDefaultMapping AS LOGICAL   NO-UNDO.
DEFINE VARIABLE cFile                   AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSchemaLocation         AS CHARACTER NO-UNDO.

DEFINE TEMP-TABLE ttPurchaseRequisition NO-UNDO  XML-NODE-NAME "PurchaseRequisition"
    FIELD OrderMethod AS CHARACTER   XML-NODE-NAME "ORDER_METHOD"
    FIELD OrderNumber AS CHARACTER   XML-NODE-NAME "ORDER_NUMBER".

DEFINE TEMP-TABLE ttCallOFF NO-UNDO XML-NODE-NAME "CallOff"
    FIELD OrderNumber  AS CHARACTER XML-NODE-NAME "ORDER_NUMBER"
    FIELD OrderQTY     AS INTEGER   XML-NODE-NAME "qty"
    FIELD CallOffName  AS CHARACTER XML-NODE-NAME "name".

    
DEFINE DATASET ttPurchaseRequisitions 
    FOR ttPurchaseRequisition, ttCallOFF
    DATA-RELATION RELATION1 FOR ttPurchaseRequisition, ttCallOFF
        RELATION-FIELDS (OrderNumber, OrderNumber) NESTED.



ASSIGN
  cSourceType             = "FILE"
  cFile                   = "a.XML"
  cReadMode               = "EMPTY"
  cSchemaLocation         = ?
  lOverrideDefaultMapping = NO.
 
lReturn = DATASET ttPurchaseRequisitions:READ-XML (cSourceType, cFile, cReadMode, cSchemaLocation, lOverrideDefaultMapping).

IF lReturn  THEN DO:
  MESSAGE "OK" view-as alert-box.
END.
ELSE
  MESSAGE "FAILED" view-as alert-box.
 
   FOR EACH ttPurchaseRequisition:
        MESSAGE "OrderMethod: " ttPurchaseRequisition.OrderMethod   skip
                "OrderNumber: " ttPurchaseRequisition.OrderNumber view-as alert-box.
                
        
        
        FOR EACH ttCallOFF
            WHERE ttCallOFF.OrderNumber EQ ttPurchaseRequisition.OrderNumber:
            
                MESSAGE
                    "Order NUmber: " QUOTER(ttCallOFF.OrderNumber)   skip
                    "Order Method: " QUOTER(ttPurchaseRequisition.OrderMethod) SKIP
                    "OrderQTY: " ttCallOFF.OrderQTY  SKIP
                    "name: " QUOTER(ttCallOFF.CallOffName)
                    view-as alert-box.
        
        END.
   END.
 

John

Member
Thanks to Cecil also for the response.

- There was no record showing when i run above code but when I ran "for each" for ttCallOFF only then I was able to see the records in it.It means,in your code also ttPurchaseRequisitions table is not getting populated. I am not able to identify where is the problem. Could you please help in this? Thank you.
- I corrected the typo which were in Tom's code still ttPurchaseRequisitions table not getting populated.
 

Cecil

19+ years progress programming and still learning.
Thanks to Cecil also for the response.

- There was no record showing when i run above code but when I ran "for each" for ttCallOFF only then I was able to see the records in it.It means,in your code also ttPurchaseRequisitions table is not getting populated. I am not able to identify where is the problem. Could you please help in this? Thank you.
- I corrected the typo which were in Tom's code still ttPurchaseRequisitions table not getting populated.

Did you change the cFile variable to point to your XML file location?
 
Top