SQL to XML in a Script

Here’s an example of how to read data from M3’s database using SQL, and how to convert the result into XML, in a Personalized Script for Lawson Smart Office. This example is for Microsoft SQL Server.

import System.Data;
import System.Data.SqlClient;

package MForms.JScript {
     class Test {
         public function Init(element: Object, args: Object, controller : Object, debug : Object) {
             var connection = new SqlConnection('server=sqlserver;database=M3EDBTST;uid=userid;pwd=password');
             connection.Open();
             var cmd: SqlCommand = new SqlCommand('SELECT DISTINCT OKCONO, OKCUNO FROM MVXJDTA.OCUSMA', connection);
             var da: SqlDataAdapter = new SqlDataAdapter(cmd);
             var ds: DataSet = new DataSet('result');
             da.Fill(ds);
             debug.WriteLine(ds.GetXml());
         }
     }
}

It produces the following XML:

<result>
  <Table>
    <OKCONO>1</OKCONO>
    <OKCUNO>Y60000    </OKCUNO>
  </Table>
  <Table>
    <OKCONO>1</OKCONO>
    <OKCUNO>Y60001    </OKCUNO>
  </Table>
  ...
</result>

The result looks like: