Modify xml column in Sql Server 2005 database using php 5.2 mssql_query

Question

I have an xml column setup in a table on a SQL Server 2005 database and I am trying to add a node to the xml tree through php 5.2. The structure of the xml consist of a root node with name "YearsAttended" and child nodes with a name of "year". Below is the current sql script I am running.

DECLARE @PID int
SET @PID = 8112
IF (SELECT Years FROM YearsAttended WHERE ParticipantID=@PID) IS NULL 
UPDATE YearsAttended Set Years = '<YearsAttended></YearsAttended>' 
WHERE ParticipantID = @PID;

UPDATE YearsAttended 
SET Years.modify('insert <year>2003</year> as first into (/YearsAttended)[1]') 
WHERE ParticipantID = @PID;

The first UPDATE statement works fine however I can not get the second update statement to work in php 5.2. However, it does work through SQL Server Management Studio while targeting the same database.

The php code I am currently using looks like:

$yaQuery = "DECLARE @PID int
SET @PID = 8112
IF (SELECT Years FROM YearsAttended WHERE ParticipantID=@PID) IS NULL 
UPDATE YearsAttended Set Years = '<YearsAttended></YearsAttended>' 
WHERE ParticipantID = @PID;

UPDATE YearsAttended 
SET Years.modify('insert <year>2015</year> as first into (/YearsAttended)[1]') 
WHERE ParticipantID = @PID;"; 

$yaResult = mssql_query($yaQuery);  
if(!$yaResult)
{
  die("Error: " . mssql_get_last_message());
}

No errors are being thrown, but the node <year>2015</year> is not inserted into <YearsAttended></YearsAttended> in php 5.2. I have also tried separating the query into 2 different mssql_query calls but the same issue occurs. I'm fairly new to php so I wouldn't doubt if I am overlooking something. Any ideas?

Note: 8112 and 2015 will be replaced with php variables.

add a GO statement in between and check – radar Sep 23 '14 at 04:16 — radar, Sep 23 '14 at 04:16

score 0 · Accepted Answer · answered Sep 23 '14 at 06:17

The syntax seems to be correct, and the fact that it works from SSMS proves that.

What else... well, all XML methods require some particular connection options to be set before they will work. Namely:

set quoted_identifier, ansi_nulls, ansi_warnings, ansi_padding, concat_null_yields_null, arithabort on;
set numeric_roundabort off;

Make sure your connections from PHP are initialised properly.

Modify xml column in Sql Server 2005 database using php 5.2 mssql_query

1 Answers1