FM.SQL.XMLRecords

Returns rows as XML.

Parameters

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
firstRow	Available in MBS FileMaker Plugin 14.2 or newer. The index of first row. Default is 0.	0	Optional
lastRow	Available in MBS FileMaker Plugin 14.2 or newer. The index of last row. Default is FM.SQL.RowCount-1.	5	Optional

Result

Returns OK or error.

Description

Examples

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)) ]

See also

• FM.SQL.Execute
• FM.SQL.Field
• FM.SQL.JSONRecord
• FM.SQL.JSONRecords
• FM.SQL.Release
• FM.SQL.RowCount
• FM.SQL.XMLRecord
• XML.Colorize
• XML.Format
• XML.SetPathXML

Release notes

• Version 14.2
  • Added firstRow and lastRow parameter to FM.SQL.JSONRecords and FM.SQL.XMLRecords functions.

Blog Entries

• MBS FileMaker Plugin, version 14.2pr5
• What is new in the MBS FileMaker Plugin Version 10.3
• FileMaker records to XML or JSON

This function checks for a license.

Created 29th June 2020, last changed 6th May 2024

FM.SQL.XMLRecord - FM.ScriptIDForScriptName