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

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";
**taken from

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');

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

String Ordered Permutation Algorithm Problem

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

Array reversal in Recurrsion

Kadane’s algorithm in C – Dynamic Programming

testing k complementary pairs algorithm with junit

Find the first occurence of number in the sorted array

Find K Complementary numbers from array Java implementation

binary tree problems with solution

Check if two strings are anagrams or not

Find longest palindrom from sequence of characters

  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


    1. Daco

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

    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 ANSI_PADDING ON’);
    $DBH->exec(‘SET ANSI_NULLS 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 *