How insert more then 4000 characters?

I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but in one step I can insert maximally 4000 character in uni-code.

Is there another way? Thanks' a lot.

I use such sequence T-SQL:

Declare

@.doc XML,

@.ixml nvarchar(4000)

SELECT @.ixml=(SELECT * FROM SAMPLE "d:SAMPLE" for XML AUTO)

SET @.ixml = 'SET @.Doc.modify(''

declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";

declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields";

insert ('+@.ixml+') into (dfs:myFields/dfs:dataFields)[1] '')'

exec sp_ExecuteSql @.STMT = @.ixml, @.PARAMS = N'@.DOC XML OUT', @.DOC = @.DOC OUT

Since you're using SQL2005 (I know that because of the XML data type), then you can use the new MAX length specification to provide up to 2GB of storage for @.ixml:

nvarchar(MAX)

This is a replacement for the old *LOB ntext data type, but much better because you get the best of both worlds. On one hand, you get the increased capacity that ntext provided, but on the other hand, Large Value Datatypes give you all of the benefits that nvarchar provides (you can compare data, index fields, convert, etc).

varchar(MAX) replaces text
nvarchar(MAX) replaces ntext
varbinary(MAX) replaces image

|||

Thanks’ a lot Jason, it was useful for me, but I wanted to solve another problem:

To modify a XML document this way is too slow, do you know anther way?

|||

You could also create a new XML instance by using FOR XML to bring the relational data and parts of the XML document (extracted with XQuery) together.

Best regards

Michael