Topics
All
MacOS
(Only)
Windows
(Only)
Linux
(Only, Not)
iOS
(Only, Not)
Components
Crossplatform Mac & Win
Server
Client
Old
Deprecated
Guides
Examples
Videos
New in version:
12.1
12.2
12.3
12.4
12.5
13.0
13.1
13.2
13.3
13.4
Statistic
FMM
Blog
Returns rows as XML.
Component | Version | macOS | Windows | Linux | Server | iOS SDK |
FM FMSQL | 10.3 | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes |
Parameter | Description | Example | Flags |
---|---|---|---|
SQLref | The reference number returned by FM.SQL.Execute function. | $SQLRef | |
RootNodeName | The name for the root XML node for all records. Defaults to "records". |
"Companys" | |
RecordNodeName | The name for the parent XML nodes for ome record. Defaults to "record". |
"Company" | |
FieldNames | A list of field names for the JSON. | "Model¶Names" | |
Flags | The flags for the json creation. Pass 1 to get all values as text. Pass 2 to get all dates, times and timestamps in SQL format. |
1+2 | Optional |
Returns OK or error.
Query records as XML:
# run query to get some data
Set Variable [ $sql ; Value: MBS( "FM.SQL.Execute"; ""; "SELECT \"First\", \"Last\", Company, \"Photo Container\" FROM Contacts") ]
# query data
Set Variable [ $xml ; Value: MBS( "FM.SQL.XMLRecords"; $sql; "people"; "person"; "First¶Last¶Company¶Photo"; 2) ]
# store result
Set Field [ Contacts::Output ; $xml ]
# free memory
Set Variable [ $xml ; Value: MBS( "FM.SQL.Release"; $sql) ]
Query contacts with related phone records as XML:
# Run query to fetch some records
Set Variable [ $sql1 ; Value: MBS( "FM.SQL.Execute"; ""; "SELECT \"PrimaryKey\", \"First Name\", \"Last Name\", \"Company\", \"Title\" FROM Contacts") ]
# get them as XML
Set Variable [ $xml ; Value: MBS( "FM.SQL.XMLRecords"; $sql1; "people"; "person"; "ID¶First¶Last¶Company¶Title"; 2+1) ]
#
# loop over records to look for related record
Set Variable [ $count ; Value: MBS( "FM.SQL.RowCount"; $sql1 ) ]
Set Variable [ $index ; Value: 0 ]
If [ $index ≤ $count ]
Loop
# get primary key from result
Set Variable [ $key ; Value: MBS( "FM.SQL.Field"; $sql1; $index; 0 ) ]
# Run query for related records
Set Variable [ $sql2 ; Value: MBS( "FM.SQL.Execute"; ""; "SELECT Type, Number FROM \"Phone Numbers\" WHERE ForeignKey = ?"; $key) ]
# get them as XML
Set Variable [ $xml2 ; Value: MBS( "FM.SQL.XMLRecords"; $sql2; "phones"; "phone"; "Type¶Number"; 2+1) ]
# Now insert them in the person node as new child phones
Set Variable [ $xml ; Value: MBS( "XML.SetPathXML"; $xml; "people.person[" & $index &"].phones"; 0; $xml2 ) ]
# don't forget to release memory
Set Variable [ $r ; Value: MBS( "FM.SQL.Release"; $sql2) ]
#
# next
Set Variable [ $index ; Value: $index + 1 ]
Exit Loop If [ $index ≥ $count ]
End Loop
End If
#
# don't forget to release memory
Set Variable [ $r ; Value: MBS( "FM.SQL.Release"; $sql1) ]
# Output the XML with format & color
Set Field [ Contacts::XML ; MBS("XML.Colorize"; MBS("XML.Format"; $xml)) ]
This function checks for a license.
Created 29th June 2020, last changed 26th February 2021