Door 21 XML

Welcome to door 21. We have already seen in other doors that we can output the results of SQL queries not only as text but also as a matrix or JSON. Today, I would like to introduce you to another return format: XML.

Like JSON, XML is format that is often used for exchange between applications. Its form is reminiscent of HTML, because tags are also used here to structure the data. In XML, the data is structured, hierarchical, and clearly described.

If we want to output an SQL result as XML, we use the FM.SQL.XMLRecords function. First, we use FM.SQL.Execute to determine the data set that we want to output as XML. Only then we can output the data set with FM.SQL.XMLRecords. Here, we first specify the SQL reference in the function. This is followed by the root node name and the name of the tag for each individual record. Then we list the individual field names as a list. This completes the parameters that we must specify in any case.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName);
   "SELECT Name,Launch FROM Movie" ) ]
Set Variable [ $xml ; Value: MBS("FM.SQL.XMLRecords"; $SQLRes; "Root"; 
   "Record"; "Name¶Launch") ]

The result can look like this:

<?xml version="1.0" encoding="UTF-8"?>
<Root>
  <Record>
    <Name>The Nightmare Before Christmas</Name>
    <Launch>1993</Launch>
  </Record>
  <Record>
    <Name>It's a Wonderful Life</Name>
    <Launch>1946</Launch>
  </Record>
  <Record>
    <Name>Die Hard</Name>
    <Launch>1988</Launch>
  </Record>
  <Record>
    <Name>Gremlins</Name>
    <Launch>1984</Launch>
  </Record>
…
 <Record>
    <Name>A Christmas Story</Name>
    <Launch>1983</Launch>
  </Record>
</Root>

In addition to the required parameters, there are also optional parameters. This allows us to set flags. The number 1 means that we can return all values as text values, and the number 2 means that we get all dates, times, and timestamps in SQL format. In addition, we can also define a range of rows to be output in the parameters. By default, we return all rows from our SQL result. In addition to the FM.SQL.XMLRecords function, there is also the FM.SQL.XMLRecord function. This returns a single data record as XML. In the parameters, we first specify the reference. Then we specify the node name for the XML node, the number of the row of the result we want, starting with 0, and again a list with the names of the fields. Optionally, we can again specify our flags 1 for text values and 2 for date, time, and timestamp.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
  "SELECT Name,Launch FROM Movie" ) ]
Set Variable [ $xml ; Value: MBS("FM.SQL.XMLRecord"; $SQLRes; "Record"; 0; "Name¶Launch") ]

That brings us to the end of our Advent calendar, and I wish you a wonderful Advent Sunday.