Sparx Systems Forum

Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: jepessen on November 14, 2020, 08:29:30 pm

Title: How to parse xml from jscript
Post by: jepessen on November 14, 2020, 08:29:30 pm

Hi.

When I perform a sql query from a jscript, I obtain a XML. The problem is that I don't find a way to parse the XML in order to retrieve my data.

For example I need a function for retrieving a list of all phases related to all requirements. This is the function:

Code: [Select]

function getPhasesList()
{
var requirementsQuery = "select distinct Phase from t_object where Object_Type = 'Requirement' order by Phase asc;";
var elements = Repository.SQLQuery(requirementsQuery);
        // List from XML?
return elements;
}
elements is a string containing an xml, but I want it to be an array of strings, where every string is a phase. How can I extract those data from XML?

Title: Re: How to parse xml from jscript
Post by: Geert Bellekens on November 14, 2020, 09:10:10 pm

You'll have to use an xml library to do so.

This next section if from my vbscript library (https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Framework/Utils/Util.vbs), but it should translate rather easily to jscript

Code: [Select]

function getArrayFromQuery(sqlQuery)
dim xmlResult
xmlResult = Repository.SQLQuery(sqlQuery)
getArrayFromQuery = convertQueryResultToArray(xmlResult)
end function

'converts the query results from Repository.SQLQuery from xml format to a two dimensional array of strings
Public Function convertQueryResultToArray(xmlQueryResult)
    Dim arrayCreated
    Dim i
    i = 0
    Dim j
    j = 0
    Dim result()
    Dim xDoc
    Set xDoc = CreateObject( "MSXML2.DOMDocument" )
    'load the resultset in the xml document
    If xDoc.LoadXML(xmlQueryResult) Then       
'select the rows
Dim rowList
Set rowList = xDoc.SelectNodes("//Row")

Dim rowNode
Dim fieldNode
arrayCreated = False
'loop rows and find fields
For Each rowNode In rowList
j = 0
If (rowNode.HasChildNodes) Then
'redim array (only once)
If Not arrayCreated Then
ReDim result(rowList.Length, rowNode.ChildNodes.Length)
arrayCreated = True
End If
For Each fieldNode In rowNode.ChildNodes
'write f
result(i, j) = fieldNode.Text
j = j + 1
Next
End If
i = i + 1
Next
'make sure the array has a dimension even is we don't have any results
if not arrayCreated then
ReDim result(0, 0)
end if
end if
    convertQueryResultToArray = result
End Function
Geert

Title: Re: How to parse xml from jscript
Post by: jepessen on November 14, 2020, 10:19:13 pm

Ok thanks, it seems to work.

Title: Re: How to parse xml from jscript
Post by: Guillaume on October 28, 2022, 09:33:09 pm

Hi,

I'm opening this thread as I'm trying to achieve Geert's function to build an array from a query using JScript.

Below is the code that I currently have but I'm getting an error for line foreach (rowNode in rowList).

What I'd like to do is having a function similar to DBGetFieldValueArrayString in EAScriptLib.JScript-Database but for all columns returned by my query, not just one.
e.g. if I run "select object_ID, name, stereotype from t_object ...", I'd like to have a 2D array to read values for each row, and each field (result[0,0], result[0,1], result[1,0], result[1,1], etc.)

Code: [Select]

var queryResult = Repository.SQLQuery( sql );
if ( queryResult.length > 0 )
{
var resultDOM = XMLParseXML( queryResult );
if ( resultDOM ) {
var resultArray;
var rowList = resultDOM.selectNodes( "//EADATA//Dataset_0//Data//Row" );
var rowNode;
Session.Output(rowList.length);
if (rowList.length > 0) {
var i = 0;
foreach (rowNode in rowList)
{
Session.Output(i);
i++;
}
....