Spreadsheets can use PHP to execute parameterized queries on databases or APIs and return results as single values or dynamic arrays.

The first script, called by Excel's WEBSERVICE() function, uses PDO to execute a SQL query and converts the response to XML.  
This is ultra-handy for inserting aggregate query results of up to 32,767 characters in length into Excel 365 dynamic arrays. 

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: text/html; charset=utf8mb4");
header("Cache-Control: no-cache, must-revalidate");
$servername = "SQLSERVER";
$username = "WORLDWIDEadmin";
$password = "WORLDWIDEpassword";
$database = "WORLDWIDE";
$port = "1433";
$conn = new PDO("sqlsrv:Server=$servername,$port;Database=$database;ConnectionPooling=0",$username,$password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "SELECT s.SupplierID,s.SupplierName,sc.SupplierCategoryName, pp.FullName AS SupplierContact,s.PhoneNumber,s.FaxNumber,s.WebsiteURL,
COUNT(O.PurchaseOrderID) AS CountaOfPOs
FROM Purchasing.Suppliers AS s
JOIN Purchasing.SupplierCategories AS sc ON s.SupplierCategoryID = sc.SupplierCategoryID
JOIN [Application].People AS pp
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN Purchasing.PurchaseOrders O ON O.SupplierID = s.SupplierID
GROUP BY s.SupplierID,s.SupplierName,sc.SupplierCategoryName,pp.FullName,s.PhoneNumber,s.FaxNumber,s.WebsiteURL";
$stmt = $conn->prepare($sql);
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
$array = json_decode($json, true);
$dom = new DOMDocument('1.0', 'utf-8');
$dom->preserveWhiteSpace = false;
$dom->formatOutput = true;
$root = $dom->createElement('root');
$dom->appendChild($root);
array2xml($array, $root, $dom);
echo $dom->saveXML();
function array2xml($array, $node, &$dom)
{
    foreach($array as $key => $value)
    {
        if(preg_match("/^[0-9]/", $key))
            $key = "node-{$key}";
        $key = preg_replace("/[^a-z0-9_\-]+/i", '', $key);

        if($key==='')
            $key = '_';

        $a = $dom->createElement($key);
        $node->appendChild($a);

        if(!is_array($value))
            $a->appendChild($dom->createTextNode($value));
        else
            array2xml($value, $a, $dom);
    }
}




This second example uses a custom Array2XML class, called by the script at the bottom of the page, to convert JSON responses to XML.  


Array2XML: A PHP class used to convert JSON arrays in to valid XML.
It also takes into account attribute names unlike SimpleXML in PHP.
It returns the XML in form of DOMDocument class for further manipulation.
It throws exception if the tag name or attribute name has illegal characters.
Author : Lalit Patel
License: Apache License 2.0 http://www.apache.org/licenses/LICENSE-2.0

<?php
class Array2XML {

    private static $xml = null;
  private static $encoding = 'UTF-8';

    public static function init($version = '1.0', $encoding = 'UTF-8', $format_output = true) {
        self::$xml = new DomDocument($version, $encoding);
        self::$xml->formatOutput = $format_output;
		self::$encoding = $encoding;
    }
    public static function &createXML($node_name, $arr=array()) {
        $xml = self::getXMLRoot();
        $xml->appendChild(self::convert($node_name, $arr));

        self::$xml = null;
        return $xml;
    }
    private static function &convert($node_name, $arr=array()) {
        $xml = self::getXMLRoot();
        $node = $xml->createElement($node_name);
        if(is_array($arr)){
            if(isset($arr['@attributes'])) {
                foreach($arr['@attributes'] as $key => $value) {
                    if(!self::isValidTagName($key)) {
                        throw new Exception('[Array2XML] Illegal character in attribute name. attribute: '.$key.' in node: '.$node_name);
                    }
                    $node->setAttribute($key, self::bool2str($value));
                }
                unset($arr['@attributes']); 
            }
            if(isset($arr['@value'])) {
                $node->appendChild($xml->createTextNode(self::bool2str($arr['@value'])));
                unset($arr['@value']);
                return $node;
            } else if(isset($arr['@cdata'])) {
                $node->appendChild($xml->createCDATASection(self::bool2str($arr['@cdata'])));
                unset($arr['@cdata']);
                return $node;
            }
        }

        if(is_array($arr)){
            foreach($arr as $key=>$value){
               if(!self::isValidTagName($key)) {
                  throw new Exception('[Array2XML] Illegal character in tag name. tag: '.$key.' in node: '.$node_name);
               }
                if(is_array($value) && is_numeric(key($value))) {
                    foreach($value as $k=>$v){
                        $node->appendChild(self::convert($key, $v));
                    }
                } else {
                    $node->appendChild(self::convert($key, $value));
                }
                unset($arr[$key]);
            }
        }

        if(!is_array($arr)) {
            $node->appendChild($xml->createTextNode(self::bool2str($arr)));
        }

        return $node;
    }

    private static function getXMLRoot(){
        if(empty(self::$xml)) {
            self::init();
        }
        return self::$xml;
    }

    private static function bool2str($v){
        $v = $v === true ? 'true' : $v;
        $v = $v === false ? 'false' : $v;
        return $v;
    }

    private static function isValidTagName($tag){
       $pattern = '/^[a-z_]+[a-z0-9\:\-\.\_]*[^:]*$/i';     
        return preg_match($pattern, $tag, $matches) && $matches[0] == $tag;
    }
}
?>

Comment // and uncomment lines 159 - 161 of the script below to test with different APIs.
<?php
  require_once('array2xml.php');
  $json = file_get_contents('https://api.coinlore.net/api/tickers/');
  //$json = file_get_contents('http://ergast.com/api/f1/circuits.json');
  //json = file_get_contents('https://api.nytimes.com/svc/news/v3/content/all/all.json?api-key=gb5yAzGoOvnG7G3TmrGninqF9flFlQrt');
  $php_array = json_decode($json, true);
  header("Content-type: text/xml");
  $xml = Array2XML::createXML('root', $php_array);
  echo $xml->saveXML();
?>