I am facing problem with casting from XML to varchar(max). I declared one variable as XML and that variable contains data in XML format and it contains more than 8000 chars (in our application some times it may contains nearly 100000 also).
Now, I am trying to cast that XML type variable to varchar(max), on that time varchar(max) variable taking 8000 chars only. I need all chars what XML variable contains into varchar(max) variable.
For easily reference, below is my code.
DECLARE @ParameterSQL AS VARCHAR(MAX) = 'Select Top 50 * from Certificates '
DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N'@XML XML OUTPUT'
SET @SQL = 'WITH PrepareTable (XMLString) '
SET @SQL = @SQL + 'AS ( '
SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW, TYPE, ELEMENTS '
SET @SQL = @SQL + ') '
SET @SQL = @SQL + 'SELECT @XML = XMLString FROM PrepareTable '
EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
--SET @XMLString = CONVERT(VARCHAR(MAX),@XML)
SET @XMLString = CAST(@XML AS VARCHAR(MAX))
Actually, the @xml contains more than 8000 chars, but @XMLString contains only 8000 chars after casting.
I tried in this way, SET @XMLString = CAST(@XML AS VARCHAR(15000)). This time i am getting error l
View Complete Post