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

Implement Queue Using two Stacks – JavaScript algorithm

String Ordered Permutation Algorithm Problem

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

Flatten nested javascript array

Array reversal in Recurrsion

Find longest palindrom from sequence of characters

Find K Complementary numbers from array Java implementation

Find the first occurence of number in the sorted array

testing k complementary pairs algorithm with junit

Kadane’s algorithm in C – Dynamic Programming