Status: Verified
GOAL:
How to create a XML document using dynamic query, buffers and fields?
GOAL:
How to Create an XML Document from Database Table?
FACT(s) (Environment):
OpenEdge Category: Language (4GL/ABL)
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
When reading table records, it is possible to create an XML document with the record values. This solution shows one way to create a XML document using dynamic query, buffers and fields.
Using dynamic objects, the field names are dynamically created as elements in the XML document.
The XML document tree is created with the following elements:
<table-name> Root element.
<Record> Record element
<field-element>field-value</field-element> Field name as element and field value as text.
?
?
</Record>
</table-name>
&SCOPED-DEFINE xpTable customer
DEFINE VARIABLE hXML AS HANDLE NO-UNDO.
DEFINE VARIABLE hRoot AS HANDLE NO-UNDO.
DEFINE VARIABLE hFieldName AS HANDLE NO-UNDO.
DEFINE VARIABLE hFieldValue AS HANDLE NO-UNDO.
DEFINE VARIABLE hRecord AS HANDLE NO-UNDO.
DEFINE VARIABLE hText AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hField AS HANDLE NO-UNDO.
DEFINE VARIABLE iNumFields AS INTEGER NO-UNDO.
DEFINE VARIABLE iExtents AS INTEGER NO-UNDO.
CREATE X-DOCUMENT hXML.
CREATE X-NODEREF hRoot.
CREATE X-NODEREF hRecord.
CREATE X-NODEREF hFieldName.
CREATE X-NODEREF hFieldValue.
CREATE X-NODEREF hText.
CREATE QUERY hQuery.
CREATE BUFFER hBuffer FOR TABLE "{&xpTable}":U.
hquery:SET-BUFFERS(hBuffer).
hQuery:QUERY-PREPARE("FOR EACH ":U + hBuffer:TABLE + " NO-LOCK":U).
hQuery:QUERY-OPEN().
hQuery:GET-FIRST().
/*Create de Root element with table name as element name*/
hXML:CREATE-NODE(hRoot, hBuffer:TABLE, "ELEMENT":U).
hXML:APPEND-CHILD(hRoot).
/*Get the description value for the specified table and set it as
node attribute*/
FIND FIRST _file WHERE _file._file-name = hBuffer:TABLE NO-LOCK.
IF _file._Desc NE "" THEN
hRoot:SET-ATTRIBUTE("Description":U, _file._Desc).
REPEAT:
/*Create a 'Record' element.*/
hXML:CREATE-NODE(hRecord, "Record":U, "ELEMENT":U).
/*The hText element is used just for create a new line
after each element*/
hXML:CREATE-NODE(hText, "", "TEXT":U).
hText:NODE-VALUE = "~n ".
hRoot:APPEND-CHILD(hText).
hRoot:APPEND-CHILD(hRecord).
DO iNumFields = 1 TO hBuffer:NUM-FIELDS:
ASSIGN hField = hBuffer:BUFFER-FIELD(iNumFields).
IF hField:EXTENT > 0
THEN DO:
REPEAT iExtents = 1 TO hField:EXTENT:
RUN createField (INPUT iExtents).
END.
END.
ELSE RUN createField (INPUT 0).
END. /*DO iNumFields = 1 TO hBuffer:NUM-FIELDS:*/
hXML:CREATE-NODE(hText, "", "TEXT":U).
hText:NODE-VALUE = "~n ".
hRecord:APPEND-CHILD(hText).
hQuery:GET-NEXT().
IF hQuery:QUERY-OFF-END THEN LEAVE.
END. /*repeat*/
hXML:CREATE-NODE(hText, "", "TEXT":U).
hText:NODE-VALUE = "~n".
hRoot:APPEND-CHILD(hText).
hXML:SAVE("FILE":U, hBuffer:TABLE + ".xml").
PROCEDURE createField:
DEFINE INPUT PARAMETER iExtent AS INTEGER NO-UNDO.
/*In order to improve performance decrease the .xml file size
the fields with a "" value will be not added as element*/
IF hField:EXTENT = 0 THEN
IF hField:BUFFER-VALUE = "" THEN RETURN.
hXML:CREATE-NODE(hText, "", "TEXT":U).
hTex.t:NODE-VALUE = "~n ".
hRecord:APPEND-CHILD(hText).
/*Create the field name as element*/
hXML:CREATE-NODE(hFieldName, hField:NAME, 'ELEMENT':U).
IF hField:EXTENT > 0
THEN hFieldName:SET-ATTRIBUTE("Extent":U, string(iExtent - 1)).
hRecord:APPEND-CHILD(hFieldName).
/*Create the field value as text*/
hXML:CREATE-NODE(hFieldValue, "text":U, 'TEXT':U).
hFieldName:APPEND-CHILD(hFieldValue).
hFieldValue:NODE-VALUE = IF hField:EXTENT = 0 THEN
hField:BUFFER-VALUE ELSE hField:BUFFER-VALUE(iExtent).
END PROCEDURE.
.