Accessing XML column of SQL Server from PHP (PDO)

Accessing XML column of SQL Server from PHP (PDO)

One cool feature that SQL Server provides is the XML data type. This enables to save XML data as it is just like any other data type in the database.

Once we put the xml, then we can apply XPath to query the data from the sql server nifty IDE.

But, when you try to use the same query outside of the IDE, you would not be able to get the data as expected.

I was working on PHP project where the data (XML) has to be retrieved from SQL Server. Here is the hack step by step

1. Configure your PDO
Say:


try {
    $hostname = "host";            //host
    $dbname = "dbname";            //db name
    $username = "user";            // username like 'sa'
    $pw = "pass";                // password for the user

    $dbh = new PDO ("mssql:host=$hostname;dbname=$dbname","$username","$pw");
  } catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "n";
    exit;
  } 
**taken from php.net

2. Once you got the handle ($dbh) add the following on the handle:


$handle = getHandle(); //function like above to give you the handle
$handle->exec('SET QUOTED_IDENTIFIER ON');
$handle->exec('SET ANSI_WARNINGS ON');
$handle->exec('SET ANSI_PADDING ON');
$handle->exec('SET ANSI_NULLS ON');
$handle->exec('SET CONCAT_NULL_YIELDS_NULL ON');

That is all, this are actually additional parameters that sql server would add when sending xml based query to the engine.

Happy XMLing :)

See how you would solve these known algorithm problems

Find the first occurence of number in the sorted array

Java solution for checking anagram strings – tell if phrases are anagrams

Find longest palindrom from sequence of characters

Changing decimal number to its binary equivalent

Find K Complementary numbers from array Java implementation

String Ordered Permutation Algorithm Problem

Find the pairs that makes K Complementary in the given array java solution

Check if there are three numbers a, b, c giving a total T from array A

Array reversal in Recurrsion

Implementing tokenizer and adding tokens to the linked list

8 Comments
  1. Samuel

    Doesnt work :( I still get empty xml columns

    1. gullele

      The first thing would to make sure you have connection to the server from your pdo.
      Then those statements should run before you run any of your queries, once you set that, it should not be a problem.
      Still I suspect that your connection string might not have been set properly.
      Good luck

  2. Samuel

    oops I just noticed that im not using the old mssql stuff but the linux native driver through odbc. I guess its a completely different thing

    thanks

    1. Daco

      Samuel, did you ever get this to work with the Linux native driver?
      Thanks!

    2. fedil

      I’m having the same issue using the linux native driver through odbc. Any luck solving this?

  3. Patrick

    Thank you, Thank you. Two days of googling…

    This worked using the FreeTDS driver.

    A little change, though.
    $DBH = new PDO(‘odbc:Driver=FreeTDS; Server=xxxx; Port=1433;Database=xxx; UID=xxx; PWD=xxx;’);

    $DBH->exec(‘SET QUOTED_IDENTIFIER ON’);
    $DBH->exec(‘SET ANSI_WARNINGS ON’);
    $DBH->exec(‘SET ANSI_PADDING ON’);
    $DBH->exec(‘SET ANSI_NULLS ON’);
    $DBH->exec(‘SET CONCAT_NULL_YIELDS_NULL ON’);

    I did not need this line: $handle = getHandle();

    1. gulleman

      Thank you very much Patrick for the update.

  4. omarshariffdontlikeit

    Just wanted to add that this solution still works for returning multiple result sets from a stored procedure over dblib pdo driver.

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*