Solved

Convert MSSQL_query result to xml

Posted on 2009-05-20
7
868 Views
Last Modified: 2012-05-07
Hi all

I'm looking for a function that will convert a MSSQL_query to XML. So far with no luck.

Either I could make an associative array and convert to xml - but so far also that fails - OR find a function that would convert a native MSSQL object to XML.

$res = mssql_query( $sql );
            while($r = mssql_fetch_assoc( $res ) )
            {
                  $t[] = $r;
            }
            header ("content-type: text/xml");
            echo toXml( $t );

Any ideas or code ??
0
Comment
Question by:trg_dk
  • 5
  • 2
7 Comments
 
LVL 11

Expert Comment

by:BrianMM
ID: 24431451
Here is a rough guide, obviously add whatya need in the loop :)
    <?php
 

    // get query from URL

    $query    = urldecode($_GET['q']);

    if(empty($_GET['q'])) exit();

    echo <?xml version=\1.0\ encoding=\UTF-8\?>;

        /*

        sample query:

        $query = SELECT * FROM table_name;

        */
 

        // connection open to MSSQL

        $link = mssql_connect(hostname, username, password);

        mssql_select_db(dbname, $link);
 

        // get the query result

        $result = mssql_query($query, $link);

        $i = 0;
 

        // get all columns readed from query

        while ($column = mssql_fetch_field ($result)){

            $columnx[$i] = $column->name;

            $i++;

        }

        $column = null;
 

        // read all rows with its column name

        while (($row = mssql_fetch_array($result, MSSQL_BOTH)))

        {

            echo \n <new_row>;

            foreach($columnx as $C){

                // $C                : column name

                // $row[$C]            : it give the current rows column variable

                // sample output    : <column_name>column_content</column_name>

                echo \n  < . $C . > . $row[$C] . </ . $C . >;

            }

            echo \n </new_row>;

        }
 

        // close the current connection

        mssql_free_result($result);

        mssql_close($link);

    echo \n</xml>;

    ?>

Open in new window

0
 
LVL 11

Expert Comment

by:BrianMM
ID: 24431457
Damn pasting stripped out some "'s and things!
0
 
LVL 11

Accepted Solution

by:
BrianMM earned 500 total points
ID: 24431473
(HOPEFULY BETTER)
    <?php
 

    // get query from URL

    $query    = urldecode($_GET['q']);

    if(empty($_GET['q'])) exit();

    echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>";

        /*

        sample query:

        $query = "SELECT * FROM table_name";

        */
 

        // connection open to MSSQL

        $link = mssql_connect('hostname', 'username', 'password');

        mssql_select_db('dbname', $link);
 

        // get the query result

        $result = mssql_query($query, $link);

        $i = 0;
 

        // get all columns readed from query

        while ($column = mssql_fetch_field ($result)){

            $columnx[$i] = $column->name;

            $i++;

        }

        $column = null;
 

        // read all rows with its column name

        while (($row = mssql_fetch_array($result, MSSQL_BOTH)))

        {

            echo \n <new_row>;

            foreach($columnx as $C){

                // $C                : column name

                // $row[$C]            : it give the current rows column variable

                // sample output    : <column_name>column_content</column_name>

                echo "\n  <" . $C . ">" . $row[$C] . "</" . $C . ">";

            }

            echo \n </new_row>;

        }
 

        // close the current connection

        mssql_free_result($result);

        mssql_close($link);

    echo "\n</xml>";

    ?>

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Author Comment

by:trg_dk
ID: 24431476
hmmmm - I'd be very much PRO a function that I could use, as I would be using this for several functions
0
 
LVL 11

Expert Comment

by:BrianMM
ID: 24431486
ach... still missed some on line 37 and 27... I'm sure you can work it out :)
0
 
LVL 2

Author Comment

by:trg_dk
ID: 24431522
I found this function which works perfectly for me.

http://www.sean-barton.co.uk/2009/03/turning-an-array-or-object-into-xml-using-php/

Points given for the effort :-)
0
 
LVL 11

Expert Comment

by:BrianMM
ID: 24431545
should't be too hard to shove it into a function and use them in the select & loop... if you can't give me a shout and will do it tonight for ya.

make a function with a param say $data

a loop like this should build the select with the required cols... and then use a loop similarly within the recordset for loop to read the wanted columns...

      foreach($data as $key=>$val) {
            if(strtolower($val)=='null') $q.= "`$key` = NULL, ";
            elseif(strtolower($val)=='now()') $q.= "`$key` = NOW(), ";
            else $q.= "`$key`='".$val."', ";
      }
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now