(imported topic written by SystemAdmin)
For BES 7.0 through 7.2 we add a stored procedure, fn_ExtractField, to help get data out of XML fields stored in the database. The pre-defined procedure had a limitation though, it would only be able to return 8000 characters from the XML field requested. If you had a lot of text in a field, you wouldn’t be able to get the entire data set with the pre-defined version of fn_ExtractField. This limitation was caused by needing to support SQL 2000 but we can get around the problem if you are using SQL 2005.
Here’s a new version of fn_ExtractField (named fn_ExtractFullField) that removes the 8000 character limitation. We’ll be using this definition for fn_ExtractField after 7.2 but you can add the ExtractFullField procedure in 7.0, 7.1 or 7.2 if you are using SQL 2005 as a way to work around the 8000 character limitation.
create function fn_ExtractFullField ( @fieldName nvarchar(max), @fieldNumber integer, @fieldsXml image ) returns nvarchar(max) as begin declare @xml xml set @xml = convert( xml, convert( varbinary(max), @fieldsXML ) )
return @xml.value(
'(/Object/Fields[Name=sql:variable( "@fieldName" ) and Number=sql:variable( "@fieldNumber" )]/Contents)[1]',
'nvarchar(max)' ) end