i have a table in MSSQL 2k5 with one PK and one xml datatype.
I wrote a stored proc to insert values in my xml column.
But its not working. I can read and insert in other data type columns but xml is not working.
Please see the below code snippet. PHP Code calls the stored proc.
stored proc receives 2 values to update in table.
xml file in xml column has following initial structure
<sroot/>
MSSQL table : usid INT NOT NULL PK
xml XML NOT NULL
Please advice on any mistake I am doing.
function db_query_proc($usid,$msg, $link = 'db_link') {
global $$link;
$stmt=mssql_init("addchat",$$link);
/* now bind the parameters to it */
mssql_bind($stmt, "@usid", $usid, SQLINT4, FALSE);
mssql_bind($stmt, "@msg", $msg, SQLVARCHAR, FALSE);
/* now execute the procedure */
$result = mssql_execute($stmt);
return $result;
}
///////mSSQL code//////////////////
ALTER PROCEDURE dbo.addchat
(
@usid int ,
@msg nvarchar(50)
)
AS
UPDATE mdb
SET xml.modify('insert <Text>{xs:string(sql:variable("@msg"))}</Text> as last into sroot[1]')
WHERE (usid = @usid)
RETURN
