Data Type for multi line text?

Hi,
I'm using bulkload to import data from an xml file. The data has several
lines of text before the closing tag. After I bulkload the data all the text
is ran together.
I'm using data type of "Text" on my SQL2005 server, should I use something
else that will keep the formatting? The text can actuall be over several
thousand characters.
Thanks
Charles W
XML format:
<data> Line one with data
Line two with data, a line may be skipped
Fourth line with data.
</data>
SQL format:
Line one with dataLine two with data, a line may be skippedFourth line with
data.Hello Charles,

> Hi,
> I'm using bulkload to import data from an xml file. The data has
> several
> lines of text before the closing tag. After I bulkload the data all
> the text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use
> something
> else that will keep the formatting? The text can actuall be over
> several
> thousand characters.
> Thanks
> Charles W
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
> with
> data.
is nvarchar(max) an option?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:eWjOBAsTGHA.4976@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm using bulkload to import data from an xml file. The data has several
> lines of text before the closing tag. After I bulkload the data all the
text
> is ran together.
> I'm using data type of "Text" on my SQL2005 server, should I use something
> else that will keep the formatting? The text can actuall be over several
> thousand characters.
>
> Thanks
> Charles W
>
> XML format:
> <data> Line one with data
> Line two with data, a line may be skipped
> Fourth line with data.
> </data>
> SQL format:
> Line one with dataLine two with data, a line may be skippedFourth line
with
> data.
>
How are you confirmin that the line feeds are actually being removed?
Note that XML will often replace any CRLF sequence with a simple LF.
Could it be that the LFs are there but what you are using to retreive and
display the value requires CRLFs?
Anthony.|||I thought changing the field to nvarchar(4000) worked, but I ran into a size
problem when processing my files. It seems that some of the data is over the
4000 max.
Any other ideas?
Thanks
CW
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741dc7f8c81cab6345f890@.news.microsoft.com...
> Hello Charles,
>
> is nvarchar(max) an option?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||"Charles W" <cwunderlich@.nospam.vrtlweb.com> wrote in message
news:OxLl6xbUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> I thought changing the field to nvarchar(4000) worked, but I ran into a
size
> problem when processing my files. It seems that some of the data is over
the
> 4000 max.
> Any other ideas?
>
> Thanks
> CW
>
NText is the field type you need. Still doesn't eliminate the Line feed
issue which as already pointed out is not a function of the SQL data type
you are choosing but is just how XML works.
See:-
http://www.w3.org/TR/REC-xml/#sec-line-ends
Anthony