PHP Create well formatted XML out of SQL result

Question

I have a problem. I am struggling for a few days now to create a well formatted XML. I already created this code, but I have no idea if this is what I need:

$sql = "SELECT * FROM Contacts ORDER BY Id ASC";
$result = $conn->query($sql);

$arr_contacts = array();

while ($row = mysqli_fetch_assoc($result))
{
    $contact_array = array(
        $row["Id"]=>array(
        'id'=>$row["Id"],
        'name'=>$row["Name"])
    );

    $arr_contacts = array_merge($arr_contacts, $contact_array);
}

Now I want a XML like this:

<?xml version="1.0"?>
<Contacts>
    <Contact>
        <id>1</id>
        <name>Smith</name>
    </Contact>
    <Contact>
        <id>2</id>
        <name>John</name>
    </Contact>
    <Contact>
        <id>3</id>
        <name>Viktor</name>
    </Contact>
</Contacts>

The problem is that I don't understand XML very well, so my result is as following:

1Smith2John3Viktor

I used his code: https://www.codexworld.com/convert-array-to-xml-in-php/

How can I get the result I want?

Well you should try and give that guide a go, post the code if you are facing problems — Juakali92, Jan 26 '20 at 23:15
I have a problem with creating the array to use for XML. It's a array in an array in an array, but I don't know how to do the naming. — A. Vreeswijk, Jan 26 '20 at 23:29
What is your database vendor? It is possible to produce XML via SQL. — Yitzhak Khabinsky, Jan 26 '20 at 23:48

Mohamed23gharbi · Accepted Answer · 2020-01-27T00:20:30.910

You can use the following example which responds to your need :

$contact_array = array(
        array(
        'id'=>1,
        'name'=>'Ali'),
        array(
        'id'=>2,
        'name'=>'John'),
        array(
        'id'=>3,
        'name'=>'Victor')
    );

// Function that converts your array to Xml Object
function toXml(SimpleXMLElement $xml, array $data, $mainKey = null)
{   
    foreach ($data as $key => $value) {
        // if the key is an integer, it needs text with it to actually work.
        if (is_numeric($key)) {
            $key = $mainKey ? : "key_$key";
        }
        if (is_array($value)) {
            $childXml = $xml->addChild($key);
            toXml($childXml, $value);
        } else {
            $xml->addChild($key, $value);
        }   
    }   
    return $xml;
}

// Pretty print Xml
function formatXml($simpleXMLElement)
{
    $xmlDocument = new DOMDocument('1.0');
    $xmlDocument->preserveWhiteSpace = false;
    $xmlDocument->formatOutput = true;
    $xmlDocument->loadXML($simpleXMLElement->asXML());

    return $xmlDocument->saveXML();
}

$xml = toXml(new SimpleXMLElement('<Contacts/>'), $contact_array, 'Contact');
// $output = $xml->asXML();
print formatXml($xml);


// Which prints the following
   <?xml version="1.0"?>
    <Contacts>
      <Contact>
        <id>1</id>
        <name>Ali</name>
      </Contact>
      <Contact>
        <id>2</id>
        <name>Vincent</name>
      </Contact>
      <Contact>
        <id>3</id>
        <name>Victor</name>
      </Contact>
    </Contacts>

You can live test it here: http://sandbox.onlinephpfunctions.com/code/230507b1113504fc37427384e3609d0d0cb95f43

Note, You can ignore the Keys of your elements (so they remain numeric) so it become and use same functions as the example above :

$sql = "SELECT * FROM Contacts ORDER BY Id ASC";
$result = $conn->query($sql);

$arr_contacts = array();

while ($row = mysqli_fetch_assoc($result))
{
    $contact_array = array(
        array(
        'id'=>$row["Id"],
        'name'=>$row["Name"])
    );

    $arr_contacts = array_merge($arr_contacts, $contact_array);
}

Hope It helps !

PHP Create well formatted XML out of SQL result

1 Answers1