This first script use PDO to execute a SQL aggregate query and then converts the response from PHP to JSON to XML. This is ultra-handy for returning query results up to 32,767 characters in length to 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 2 scripts 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 // or uncomment lines 158 - 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'); //$json = file_get_contents('https://api.publicapis.org/entries?https=true'); $php_array = json_decode($json, true); header("Content-type: text/xml"); $xml = Array2XML::createXML('root', $php_array); echo $xml->saveXML(); ?> This script processes JSON returned from a particular API in order to add missing quotation marks and fix format problems prior to XML conversion. <?php require_once('array2xml.php'); $json = file_get_contents('https://xlforms.net/getcryptoinfo.php?searchkey=name&searchvalue=Bitcoin'); $filter1 = str_replace ( "volume24\":", "volume24\":\"", $json); $filter2 = str_replace ( ",\"volume24a\":", "\",\"volume24a\":\"", $filter1); $filter3 = str_replace ( ",\"csupply\":","\",\"csupply\":", $filter2); $filter4 = str_replace ( "[","", $filter3); $filter5 = str_replace ( "]","", $filter4); $php_array = json_decode($filter5, true); header("Content-type: text/xml"); $xml = Array2XML::createXML('root', $php_array); echo $xml->saveXML(); ?> |