Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zamankazi
Starting Member
5 Posts |
 Posted - 2008-10-09 : 12:16:51
|
| I was writing an Stored Proc where I do a select using FOR XML and store it in a variable with NVARCHAR(MAX). The select statements append the xml to the variable then write to the a database.When the size of the xml gets little bigger then it cuts of the text.Any thoughts on why it is cutting off? Any idea how to construct large xml files and write to a table?Thanks,I am using Microsoft SQL Server 2005 - 9.00.3073.00 (X64) Aug 5 2008 14:31:47 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-09 : 12:20:42
|
| why do you use NVARCHAR(MAX)? if xml data is wellformed you could simply use xml datatype available in sql 2005 to store it. in that case processing of data from xml will be easier using xml functions like query(),nodes(),modify(),... |
 |
|
|
zamankazi
Starting Member
5 Posts |
Posted - 2008-10-09 : 12:24:58
|
| I am using the nvarchar cause the xml I am constructing is from many diffrent select statements and appending it in a temp variable. I am not sure if there is a better way to do it. An example would be much appreciated. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-09 : 12:41:28
|
quote:
Originally posted by zamankazi
I am using the nvarchar cause the xml I am constructing is from many diffrent select statements and appending it in a temp variable. I am not sure if there is a better way to do it. An example would be much appreciated.
refer this linkhttp://www.developer.com/db/article.php/3531196 |
|
|
|
zamankazi
Starting Member
5 Posts |
Posted - 2008-10-09 : 14:41:55
|
| Thanks for the link, but my problem is not how I save the data into a table. I have to built the XML file from several select statement and construct the xml file before I can save. So I am using a temp variable to hold the segment of the xml file and construct it. Problem is the variable cuts off string once it gets larger.Thanks, |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-10 : 01:05:28
|
quote:
Originally posted by zamankazi
Thanks for the link, but my problem is not how I save the data into a table. I have to built the XML file from several select statement and construct the xml file before I can save. So I am using a temp variable to hold the segment of the xml file and construct it. Problem is the variable cuts off string once it gets larger.Thanks,
it wont cut off if you're using xml variable to hold the xml data you construct and also if its wellformed. then you can save the data by extracting from xml variable. |
|
|
|
Jacob.sebastian
Starting Member
2 Posts |
Posted - 2008-10-11 : 06:03:43
|
quote:
Originally posted by zamankazi
So I am using a temp variable to hold the segment of the xml file and construct it. Problem is the variable cuts off string once it gets larger.
It looks to me that you are not casting the value to NVARCHAR(MAX) when concatenating the values. This will result the variable being cut off at 4000. For example if you have two NVARCHAR(MAX) variables having 3000 characters stored in them, and if you add them together, you will get a string with 4000 characters long, instead of 6000. To avoid this, you should cast the variable to NVARCHAR(MAX) while doing the concatenation. I have explained this in this post: http://jacobsebastian.blogspot.com/2008/09/understanding-tsql-functions-1-len.htmlJacob Sebastian, SQL Server MVP (http://jacobsebastian.blogspot.com) |
|
|
|
|
|
|
|
|
NVARCHAR(MAX) and XML