title: How to fetch data from oracle in xml format using php
body: <p>I would be very happy if anyone help me :)</p>
  
  <p>I am trying to write php web service that read a query from Oracle DB and display the result as XML. I spent days in searching for solution but unfortunately it didn't work</p>
  
  <p>Below is some queries I tried it:</p>
  
  <pre><code>    &lt;?php
    $db ="(DESCRIPTION=
           (ADDRESS_LIST=
             (ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1530))
            )
           (CONNECT_DATA=(SID=DEV))
         )";
  $c = oci_connect("uname", "pass", $db);
  
  $q = "select dbms_xmlgen.getxml(
         'select user_name
          from fnd_user
          where user_name = 007144') xml
        from dual";
  
  $s = oci_parse($c, $q);
  oci_execute($s);
  $r = oci_fetch_array($s, OCI_ASSOC);
  $mylob = $r['XML']-&gt;load();   // Treat column data as a LOB descriptor
  
  echo "&lt;pre&gt;";
  echo htmlentities($mylob);
  echo "&lt;/pre&gt;";
  ?&gt;
  </code></pre>
  
  <p>The second code:</p>
  
  <pre><code>&lt;?php
  //File: DOM.php
  $db="(DESCRIPTION=
       (ADDRESS_LIST=
         (ADDRESS=(PROTOCOL=TCP)
           (HOST=xxx)(PORT=1530)
         ) )
         (CONNECT_DATA=(SID=DEV))
   )";
   $conn = OCILogon("uname","pass",$db);
   if ($conn) {
     //echo "Successfully connected to Oracle.";  
   } else {
     $err = oci_error();
     echo "Oracle Connect Error " . $err['text'];
   }
  $query = "SELECT user_name from fnd_user where user_name = '007144'";
  $stmt = oci_parse($conn,$query);
  
  if (!oci_execute($stmt, OCI_DEFAULT)) {
  $err = oci_error($stmt);
  trigger_error('Query failed: ' . $err['message'], E_USER_ERROR);
  }
  $dom = new DOMDocument('1.0', 'UTF-8');
  $root = $dom-&gt;createElement('EMPLOYEES', 'Root');
  $dom-&gt;appendChild($root);//$root = $dom-&gt;appendChild($root);
  while ($row = oci_fetch_array($stmt, OCI_RETURN_NULLS)) 
  {
  $emp = $dom-&gt;createElement('EMPLOYEE', 'emp');
  $emp = $root-&gt;appendChild($emp);
  $emp-&gt;setAttribute('id', $row['user_name']);
  
  /*
  $emp = $dom-&gt;createElement('EMPLOYEE', '');
                  $emp = $root-&gt;appendChild($emp);
                  $emp-&gt;setAttribute('id', $row['user_name']);
                  $ename = $dom-&gt;createElement('ENAME', $row['user_name']);
                  $ename = $emp-&gt;appendChild($ename);
                  $salary = $dom-&gt;createElement('SALARY', $row['user_name']);
                  $salary = $emp-&gt;appendChild($salary);
  */
  }
          echo $dom-&gt;saveXML();
          //$dom-&gt;save("employees.xml");
  oci_close($conn); 
  ?&gt;
  </code></pre>
  
  <p>The above code give me XML without data as the below picture!
  <a href="http://i48.tinypic.com/2lnv5eo.jpg" rel="nofollow">http://i48.tinypic.com/2lnv5eo.jpg</a></p>
  
  <p>Third Code</p>
  
  <pre><code>&lt;?php
    //File: XMLFromSQL.php
    $user = 'uname';
    $pswd = 'pass';
    $db ='(DESCRIPTION=
           (ADDRESS_LIST=
             (ADDRESS=(PROTOCOL=TCP)(HOST=xxx)(PORT=1530))
            )
           (CONNECT_DATA=(SID=DEV))
         )';
  
   $sql = "SELECT user_name as RESULT FROM  fnd_user 
            WHERE user_name=:deptno";
    $deptno = 007144;
   //Connect to the database and obtain info on a given department in XML format
    $conn = oci_connect($user, $pswd, $db);
  
   $query = oci_parse($conn, $sql);
    oci_bind_by_name($query, ":deptno", $deptno, 2);
    oci_execute($query);
    oci_fetch($query);
    $strXMLData = oci_result($query, 'RESULT');
  
    //Create a new DOM document and load XML into its internal XML tree
    $doc = new DOMDocument("1.0", "UTF-8");
    $doc-&gt;loadXML($strXMLData);
  
    //For simplicity, just print out the XML document
    print $doc-&gt;saveXML();
  ?&gt;
  </code></pre>
  
  <p>If you have another way please help me or I will be crazy  :(</p>