tt to XML and array attributes

m.hansler · Feb 23, 2007

Hello everyone,

I'm stuck with generating an xml file from a temp-table for third party software. In particular the array fields that are in the temp-table. Examples that I found don't really help me much because in those examples every extent in an array gets it's own node and I need an array to be an attribute of another corresponding array.

For example:

Code:

DEF TEMP-TABLE DebtorList
    FIELD debtor_number AS CHAR FORMAT "x(24)"
    FIELD name          AS CHAR FORMAT "x(100)"
    FIELD search_code   AS CHAR FORMAT "x(20)"
    FIELD currency      AS CHAR FORMAT "x(3)"
    FIELD credit_limit  AS DEC  FORMAT ">>>>>>>>>>>>>>>>>9"
    FIELD interest_rate AS DEC  FORMAT ">>>>9" INITIAL 700
    FIELD registration_number AS CHAR FORMAT "x(50)"
    FIELD userfield     AS CHAR FORMAT "x(100)" EXTENT 5
    FIELD usrfielddt    AS CHAR                 EXTENT 5
    INDEX debtor_number IS PRIMARY debtor_number ASCENDING.

Where 'userfield' has a free to define fieldvalue and the corresponding extentID from 'usrfielddt' defines the datatype of that value (string, integer, decimal).

A record of one debtor should look like this:

Code:

   <debtor>
      <debtor_number>001234</debtor_number>
      <search_code>DOE</search_code>
      <name>John Doe Inc.</name>
      <credit_limit currency='EUR'>12000</credit_limit>
      <interest_rate>7</interest_rate>
      <registration_number>0123456789</registration_number>
      <userfield id='1' datatype='decimal'>15000</userfield>
      <userfield id='2' datatype='string'>P00009</userfield>
   </debtor>

The normal fields are set in the XML file without a problem, but I can't figure out a way to get the correct result with the array fields.

Here is the code I have (Note: the output of the array fields is not correct, I know):

Code:

RUN CreateXML.
 
DEFINE VARIABLE hDoc AS HANDLE NO-UNDO.
DEFINE VARIABLE hRoot AS HANDLE NO-UNDO.
DEFINE VARIABLE hRow AS HANDLE NO-UNDO.
DEFINE VARIABLE hField AS HANDLE NO-UNDO.
DEFINE VARIABLE hText AS HANDLE NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hDBFld AS HANDLE NO-UNDO.
DEFINE VARIABLE iNumFields AS INTEGER NO-UNDO.
DEFINE VARIABLE iExtents AS INTEGER NO-UNDO.
/* ================================== */
/* -          DATA TO XML         - */
/* ================================== */
PROCEDURE CreateXML:
/* Create the objects we need. */
CREATE X-DOCUMENT hDoc.
CREATE X-NODEREF hRoot.
CREATE X-NODEREF hRow.
CREATE X-NODEREF hField.
CREATE X-NODEREF hText.
/* Get a buffer for the Customer table. */
hBuffer = BUFFER DebtorList:HANDLE.
/* Set up a root node. */
hDoc:CREATE-NODE (hRoot, "debtors", "ELEMENT").
hDoc:APPEND-CHILD (hRoot).
/* Set up the attributes of the root node */
hRoot:SET-ATTRIBUTE ("xmlns:xsi", "'http://www.w3.org/2001/XMLSchema-instance'").
hRoot:SET-ATTRIBUTE ("xsi:noNamespaceSchemaLocation", "'debtors.xsd'").
FOR EACH DebtorList:
    /* Create a customer row node. */
    hDoc:CREATE-NODE (hRow, "debtor", "ELEMENT").
    hRoot:APPEND-CHILD (hRow).
    /* Add the other fields as elements. */
    REPEAT iNumFields = 1 TO hBuffer:NUM-FIELDS:
        hDBFld = hBuffer:BUFFER-FIELD (iNumFields).
 
       IF (hDBFld:NAME = "userfield" OR hDBFld:NAME = "usrfielddt") THEN
       DO:
          REPEAT iExtents = 1 TO hDBFld:EXTENT:
            IF hDBFld:NAME = "userfield" THEN
                hDoc:CREATE-NODE (hField, hDBFld:NAME, "ELEMENT").
            hField:SET-ATTRIBUTE("id":U, STRING(iExtents)).
            hField:SET-ATTRIBUTE("datatype":U, hDBFld:BUFFER-VALUE(iExtents)).
            hRow:APPEND-CHILD (hField).
        hDoc:CREATE-NODE (hText, "", "TEXT").
        /* Node to hold value. */
        hField:APPEND-CHILD (hText).
        /* Attach text to field */
        hText:NODE-VALUE = STRING(hDBFld:BUFFER-VALUE(iExtents)).
          END.
          NEXT.
       END.

       /* Create an element with the field name as the tag.*/
        hDoc:CREATE-NODE (hField, hDBFld:NAME, "ELEMENT").
        hRow:APPEND-CHILD (hField).
        /* Make new field next row child. */
        hDoc:CREATE-NODE (hText, "", "TEXT").
        /* Node to hold value. */
        hField:APPEND-CHILD (hText).
        /* Attach text to field */
        hText:NODE-VALUE = STRING (hDBFld:BUFFER-VALUE).
    END. /* REPEAT iNumFields = 1 TO hBuffer:NUM-FIELDS: */
END. /* FOR EACH */
/* Write the XML node tree to an xml file. */
hDoc:SAVE ("file", "z:\cust.xml").
 
/* Delete the objects. Note that deleting the document
   object deletes the DOM structure under it also. */
DELETE OBJECT hDoc.
DELETE OBJECT hRoot.
DELETE OBJECT hRow.
DELETE OBJECT hField.
DELETE OBJECT hText.
END PROCEDURE.

The output I get is as follows:

Code:

[FONT=Verdana][COLOR=#000000]  <?xml version="1.0" encoding="UTF-8" ?> 
  <debtors xmlns:xsi="'http://www.w3.org/2001/XMLSchema-instance'" xsi:noNamespaceSchemaLocation="'debtors.xsd'">
  <debtor>
  <debtor_number>001234</debtor_number> 
  <name>John Doe Inc.</name> 
  <search_code>DOE</search_code> 
  <currency>EUR</currency> 
  <credit_limit>12000</credit_limit> 
  <interest_rate>7</interest_rate> 
  <registration_number>0123456789</registration_number> 
  <userfield id="1" datatype="0">15000</userfield> 
  <userfield id="2" datatype="P00009">P00009</userfield> 
  <userfield id="3" datatype="" /> 
  <userfield id="4" datatype="" /> 
  <userfield id="5" datatype="">stringstring</userfield> 
  </debtor>
  </debtors>[/COLOR][/FONT]
[COLOR=#0000ff][/COLOR]

Can someone give me some pointers or direct links to some good sources?

Peter E · Sep 23, 2008

Here is some code to export data to XML, taking extents into account. Use proprocessors to change the tablename.

/* *************************** Definitions ************************** */
/* Writing to XML */
&GLOBAL-DEFINE TableName Salesrep
&GLOBAL-DEFINE WhereClause WHERE TRUE
&GLOBAL-DEFINE Attributes
&GLOBAL-DEFINE Attrib1
&GLOBAL-DEFINE Attrib2
&GLOBAL-DEFINE Save2File z:\salesrep.xml

DEFINE VARIABLE hDoc AS HANDLE NO-UNDO.
DEFINE VARIABLE hRoot AS HANDLE NO-UNDO.
DEFINE VARIABLE hRow AS HANDLE NO-UNDO.
DEFINE VARIABLE hField AS HANDLE NO-UNDO.
DEFINE VARIABLE hExtent AS HANDLE NO-UNDO.
DEFINE VARIABLE hText AS HANDLE NO-UNDO.
DEFINE VARIABLE hBuf AS HANDLE NO-UNDO.
DEFINE VARIABLE hDBFld AS HANDLE NO-UNDO.
DEFINE VARIABLE lcElemExt AS CHARACTER NO-UNDO.
DEFINE VARIABLE liCnt AS INTEGER NO-UNDO.
DEFINE VARIABLE liCntExt AS INTEGER NO-UNDO.
/* *************************** Main Block *************************** */
RUN ipCreateHandles.
hbuf = BUFFER {&TableName}:HANDLE.
/* Set up a root node */
RUN ipRootNode.
RUN ipMainLoop.
hDoc:SAVE("file","{&Save2File}").
RUN ipCLeanUp.
/* ************ Internal Procedures *************** */
PROCEDURE ipCreateHandles:
CREATE X-DOCUMENT hDoc.
CREATE X-NODEREF hRoot.
CREATE X-NODEREF hRow.
CREATE X-NODEREF hField.
CREATE X-NODEREF hExtent.
CREATE X-NODEREF hText.
END.
PROCEDURE ipRootNode:
/*------------------------------------------------------------------------------
Purpose: Set up a root node. Always needed.
Parameters: <none>
Notes:
------------------------------------------------------------------------------*/
hDoc:CREATE-NODE(hRoot,"{&TableName}s","ELEMENT").
hDoc:APPEND-CHILD(hroot).
END.
PROCEDURE ipMainLoop:
blMain:
FOR EACH {&TableName} NO-LOCK
{&WhereClause}
:
hdoc:CREATE-NODE(hRow,"{&TableName}","ELEMENT"). /* Create Row-node */
hRoot:APPEND-CHILD(hRow). /* put row in the tree */
/{&Attributes}*/
hRow:SET-ATTRIBUTE("{&Attrib1}", STRING({&TableName}.{&Attrib1}) ).
hRow:SET-ATTRIBUTE("{&Attrib2}", STRING({&TableName}.{&Attrib2}) ).
/{&Attributes}*/
/* Add other fields as tags in the XML */
REPEAT licnt = 1 TO hBuf:NUM-FIELDS
:
hDBFld = hBuf:BUFFER-FIELD(licnt).
/{&Attributes}*/
IF hDBFld:NAME = "{&Attrib1}"
OR hDBFld:NAME = "{&Attrib2}"
THEN NEXT.
/{&Attributes}*/
/* Create a tag with fieldname */
hDoc:CREATE-NODE(hField,hDBFld:NAME,"ELEMENT").
hRow:APPEND-CHILD(hField). /* put new field as next child of row */
IF hDBFld:EXTENT GT 0 THEN
DO liCntExt = 1 TO hDBFld:EXTENT:
ASSIGN
lcElemExt = hDBFld:NAME + STRING(liCntExt).
/* Create a tag with fieldname + extent */
hDoc:CREATE-NODE(hExtent,lcElemExt,"ELEMENT").
hField:APPEND-CHILD(hExtent). /* put new field as next child of row */
/* Node to hold the field value */
hDoc:CREATE-NODE(hText,"","TEXT").
hExtent:APPEND-CHILD(hText). /* Add node to hold the extent value */
hText:NODE-VALUE = STRING(hDBFld:BUFFER-VALUE(licntExt)).
END.
ELSE DO:
/* Node to hold the field value */
hDoc:CREATE-NODE(hText,"","TEXT").
hfield:APPEND-CHILD(hText). /* Add node to hold the field value */
hText:NODE-VALUE = STRING(hDBFld:BUFFER-VALUE).
END.
END. /* Repeat */
END.
END PROCEDURE. /* ipMainloop */
PROCEDURE ipCleanUp:
/* Delete the mess */
DELETE OBJECT hDoc.
DELETE OBJECT hRoot.
DELETE OBJECT hRow.
DELETE OBJECT hField.
DELETE OBJECT hExtent.
DELETE OBJECT hText.
END.

The result:

<?xml version="1.0" ?>
- <Salesreps>
- <Salesrep>
<Sales-Rep>BBB</Sales-Rep>

<Rep-Name>Brawn , Bubba B.</Rep-Name>

<Region>East</Region>

- <Month-Quota>
<Month-Quota1>1600</Month-Quota1>

<Month-Quota2>1648</Month-Quota2>

<Month-Quota3>1697</Month-Quota3>

<Month-Quota4>1748</Month-Quota4>

<Month-Quota5>1800</Month-Quota5>

<Month-Quota6>1854</Month-Quota6>

<Month-Quota7>1910</Month-Quota7>

<Month-Quota8>1967</Month-Quota8>

<Month-Quota9>2026</Month-Quota9>

<Month-Quota10>2087</Month-Quota10>

<Month-Quota11>2150</Month-Quota11>

<Month-Quota12>2215</Month-Quota12>

</Month-Quota>

</Salesrep>

- <Salesrep>
<Sales-Rep>DKP</Sales-Rep>

<Rep-Name>Pitt , Dirk K.</Rep-Name>

<Region>Central</Region>

- <Month-Quota>
<Month-Quota1>1800</Month-Quota1>

<Month-Quota2>1854</Month-Quota2>

<Month-Quota3>1910</Month-Quota3>

<Month-Quota4>1967</Month-Quota4>

<Month-Quota5>2026</Month-Quota5>

<Month-Quota6>2087</Month-Quota6>

<Month-Quota7>2150</Month-Quota7>

<Month-Quota8>2215</Month-Quota8>

<Month-Quota9>2281</Month-Quota9>

<Month-Quota10>2349</Month-Quota10>

<Month-Quota11>2419</Month-Quota11>

<Month-Quota12>2492</Month-Quota12>

</Month-Quota>

</Salesrep>

......

tt to XML and array attributes

m.hansler

New Member

Peter E

New Member