March 6, 2007 at 10:08 am
Hi Guys I need help with writing the following query
Select daydesc as description ,Features,
adfull, askprice, bedrooms as bedroom,rooms,
floorarea as sqm, (pADDRESS1 + ''+ PADDRESS2+ ''+PADDRESS2+ ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS ,directions
from dbo.PROPERTY as Property
where property_id = @id
for xml auto,elements
<Property>
<description>Space and comfort set the tone of this stylish four bedroomed dormer bungalow on the Moynalty Road just outside the village of Mullagh. Situated just short stroll into Mullagh village and all it's amenities, this proeprty offers a purchaser a spacious family home with open-plan kitchen, dining and living areas. Althrough deceptive from the outside, this property is surprisingly spacious and well-laid out with three bedrooms (one en-suite) and family bathroom on the first floor and a very useful double bedroom with adjacent guest w.c, large sitting room, kitchen/dining, separate living/dining and utility room on the ground floor. Viewing by appointment with the Auctioneers is highly recommended.</description>
<features>Deceptively spacious family home
Quality cream fitted kitchen
Two fine reception rooms
Slate-tiled kitchen/dining room
Television points in all rooms
Just a short stroll or minutes drive from the village of Mullagh
Close to local Primary School
Close to shops, school, church, hotel, playgroup etc.
On bus route to secondary schools</features>
<askprice>3.7000000e+005</askprice>
<bedroom>4</bedroom>
<rooms>Entrance Hall (5.38m x 1.78m)
Guest W.C. (2.39m x 1.40m)
Bedroom (4.35m x 2.51m)
Sitting Room (5.98m x 3.62m)
Kitchen/Dining Room (8.22m x 3.68m)
Utility Room (2.84m x 2.42m)
Dining/Living Room (5.38m x 2.84m)
Landing ()
Bedroom (5.16m x 2.38m)
En-Suite Shower Room (1.92m x 1.87m)
Bedroom (4.91m x 2.83m)
Bedroom (4.17m x 2.88m)
Family Bathroom (3.44m x 2.19m)
</rooms>
<sqm>166</sqm>
<directions>From Kells, travel about 4.5 miles into Moynalty village, taking left at the RC church and travel almost two miles. The property is just outside the village of Mullagh on the right hand side.</directions>
</Property>
It works as expected However, what I would like to achieve is that within the Features field, which is held as a text field in the db, contains the features separated by carriage return statements. I would like to extract features XML so that it looks like
<Features>
<feature>blah</feature>
<feature>blah blah</feature>
</Features>
Is this possible ?
I thought about using something like
CHARINDEX(features,CHAR(13)+CHAR(10))as feature
But a little unsure of how to make it work? Any tips welcome
Gary Woodfine
threenineconsulting.com
March 7, 2007 at 3:30 am
I got an answer to my problem on another forum, but thought I would paste it here just in case anyone was intrested.
This will do it, just put an opening tag at the beginning, a closing tag at the end and then replace all carriage returns with an open and a close. Much easier with SQL Server 2005 because you can use varchar(max) instead of text for the column type and varchar(max) lets you use string functions like replace. You should change your column to varchar(max) if it isn't already.
select daydesc as description ,
cast(''
+ replace(Features, char(13)+char(10), '')
+ '' as xml) AS Features,
adfull, askprice, bedrooms as bedroom,rooms,
floorarea as sqm, (pADDRESS1 + ''+ PADDRESS2+ ''+PADDRESS2
+ ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS ,directions
from dbo.PROPERTY as Property
where property_id = @id
for xml auto,elements
If Features is text instead of varchar(max), you'll get an error saying you cannot use the text data type with the replace function. As I said I'd recommend that you change the column, but you could also do this in the query
select daydesc as description ,
cast(''
+ replace(cast(Features as varchar(max)), char(13)+char(10), '')
+ '' as xml) AS Features,
adfull, askprice, bedrooms as bedroom,rooms,
floorarea as sqm, (pADDRESS1 + ''+ PADDRESS2+ ''+PADDRESS2
+ ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS ,directions
from dbo.PROPERTY as Property
where property_id = @id
for xml auto,elements
Gary Woodfine
threenineconsulting.com
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply