| Author |
Topic |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
 Posted - 2008-06-19 : 17:21:34
|
| GreetingsI was tring to cast an xml document using varchar(max) but it chops it off at excatly 9643 when I do SELECT @varcharProductionBody = CAST(@xmlProductionBody AS VARCHAR(MAX))PRINT LEN(@varcharProductionBody)So what is the largest data type for string and/or characters in SQL 2005? I have looked high and low but am unable to find one.Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-20 : 00:54:07
|
Do you have a CHAR(0) at position 9643?Try to cast as VARBINARY(MAX) to see if the document gets chopped at same position.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
chetanb3
Yak Posting Veteran
52 Posts |
Posted - 2008-06-20 : 02:20:45
|
| max is 8000. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2008-06-20 : 10:37:08
|
| thanks y'all VARBINARY lets it go all the way to 21328 length but the result is unreadable. the main reason I am doing this is I have the header of an XML that pretty much stays static and the footer always stays static except for the body of the XML so I am trying to fuse all three. But since the body is real XML whereas the rest (header, footer) are just varchar I am unable to concatenate them even after I try to convert the non xml header/footer to xml. Anyways that is another forumThanks! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-20 : 14:25:23
|
It means you have a CHAR(0) (ascii zero) somewhere in the XML code terminating the string when converting to VARCHAR(MAX).Can you email the @xmlProductionBody content?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2008-06-20 : 14:57:59
|
U+0000 is not a legal character anywhere in XML, either as an encoded character or as a reference. For XML 1.0, the only low control characters allowed are TAB, CR and LF. XML 1.1 relaxes this for character references but specifically notes in the rationale (section 1.3): "Due to potential problems with APIs, #x0 is still forbidden both directly and as a character reference."quote:
Originally posted by Peso
It means you have a CHAR(0) (ascii zero) somewhere in the XML code terminating the string when converting to VARCHAR(MAX).
|
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2008-06-20 : 15:10:08
|
| Ok here is the select statement. the problem I am having now is that when trying to produce the xml the root node has 'task' as tag identifier <task id="1" ...> I guess you could call it, but I also want the child elements to have 'task' as their tag <task id="2" ...> . how can I go about getting that since I cannot name two views I am selecting from with same alias....SELECT EquipmentID + 1 as id, EquipmentDescr as [name], '#99ccff' AS color, 'true' AS [expand], (SELECT JobID + 2 AS id, 'Layout#' AS [name], '#99ccff' AS color, (SELECT [taskproperty-id] AS [taskproperty-id], [value] AS [value] FROM dbo.VXmlGanttBody customproperty WHERE customproperty.JobID = task2.JobID FOR XML AUTO, TYPE) FROM VJobs task2 WHERE task2.EquipmentID = task.EquipmentID FOR XML AUTO, TYPE ) FROM VEquipments taskORDER BY EquipmentDescrFOR XML AUTO, TYPE |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-06-20 : 15:37:08
|
Right now, I can't think of another possibility for the string to truncate, when varbinary is ok.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2008-06-21 : 02:11:46
|
I agree with you about the varchar truncation, I was really just pointing out that there would be problems in trying to treat such a string as XML, just at the XML level.quote:
Originally posted by Peso
Right now, I can't think of another possibility for the string to truncate, when varbinary is ok.
|
|
|
|
|
largest string data type