Did you know, that XML procedures like sp_xml_preparedocument or sp_xml_removedocument are not supported in MSSQL when the server is configured as "Use Windows NT fibers" (under server » processor) ? I didn't. Consider the following T-SQL snippet, which extracts data from XML document:
DECLARE @xml VARCHAR(1000) DECLARE @idoc INT SET @xml = '< ROOT> < User Id="1" /> < User Id="2" /> < User Id="3" /> < /ROOT>' EXEC sp_xml_preparedocument @idoc OUTPUT, @xml SELECT UserId FROM OPENXML (@idoc, '/ROOT/User', 2) WITH (UserId INT '@Id') EXEC sp_xml_removedocument @idoc
If the processor is configured in "NT fibers mode", you get run-time exception:
"XML stored procedures are not supported in fibers mode"
BOL does not tell anything about the issue (at least under sp_xml_preparedocument it doesn't ). To fix this problem, go to the processor tab, and uncheck the box "Use Windows NT fibers" (requires restart). If it is your server - everything is fine.
Friday, January 12, 2007 5:25 PM