Formating Ntext

Hi,
I am using SQLServer 2000
I currently have a table with just one field in it of type (ntext), the
reason for this is there is a very large amount of data in it of an XML
format. The data looks something like below althought this is only a small
sample there is actually a lot more in this field:-
<Main>This is going to be a lot of text.....<Main/><Paragraph>To buy a
house in the UK now requires a very large deposit, more than 5 times the
amount that was necessary in 1999 <Paragraph/><House
Type>Detached<HouseType/><Price>￡300,000<Price/><Location>Berkshire<Locatio
n/>
<Garage>Yes<Garage/><Bedrooms>4<Bedrooms/><
What I want is to break out the data into columns so you have something like
Paragraph HouseType Price Location
Garage Bedrooms
To buy a house... Detached ￡300,000 Berkshire Yes 4
I want to ignore the data between the <Main> Tags
Generally I think I need to break the data up based on the foward slash and
work left from there, I think it's going to be based on a left/charindex
principal but I also have to do a convert to varchar in the first place as
you are unable to do much with the data in it's current data type.
If anyone can help it would be much appreciated.
Thanks PDAre you going to do this exactly once, or regularly?
In either case, I think it would be better to have an app pull the whole
value out, parse it using XML or RegEx or whatever, and generate the INSERT
statement or procedure call necessary to break the data out.
Going forward, it would probably be better to store the individual pieces of
data and build the XML when selecting. Or, if you can move to SQL Server
2005, you can use the new XML datatype, which provides a whole slew of
options to make your implementation choice harder.
A
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:46877615-148D-4912-992D-AAF2DB435243@.microsoft.com...
> Hi,
> I am using SQLServer 2000
> I currently have a table with just one field in it of type (ntext), the
> reason for this is there is a very large amount of data in it of an XML
> format. The data looks something like below althought this is only a
> small
> sample there is actually a lot more in this field:-
> <Main>This is going to be a lot of text.....<Main/><Paragraph>To buy a
> house in the UK now requires a very large deposit, more than 5 times the
> amount that was necessary in 1999 <Paragraph/><House
> Type>Detached<HouseType/><Price>￡300,000<Price/><Location>Berkshire<Locat
ion/>
> <Garage>Yes<Garage/><Bedrooms>4<Bedrooms/><
> What I want is to break out the data into columns so you have something
> like
> Paragraph HouseType Price Location
> Garage Bedrooms
> To buy a house... Detached ￡300,000 Berkshire Yes
> 4
> I want to ignore the data between the <Main> Tags
> Generally I think I need to break the data up based on the foward slash
> and
> work left from there, I think it's going to be based on a left/charindex
> principal but I also have to do a convert to varchar in the first place as
> you are unable to do much with the data in it's current data type.
> If anyone can help it would be much appreciated.
> Thanks PD|||Hi Aaron,
Thanks for the reply, I was going to go down the RegEx route but as this is
only a one of I just wanted to make it as quick and simple as possible so I
wanted to opt for querying it straight out of the field!!!.
Thanks PD
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are you going to do this exactly once, or regularly?
> In either case, I think it would be better to have an app pull the whole
> value out, parse it using XML or RegEx or whatever, and generate the INSER
T
> statement or procedure call necessary to break the data out.
> Going forward, it would probably be better to store the individual pieces
of
> data and build the XML when selecting. Or, if you can move to SQL Server
> 2005, you can use the new XML datatype, which provides a whole slew of
> options to make your implementation choice harder.
> A
>
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:46877615-148D-4912-992D-AAF2DB435243@.microsoft.com...
>
>