SQL xml path script to amalgamate

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL xml path script to amalgamate

Author

Topic

advancesolutions
Starting Member

2 Posts

Posted - 2010-09-06 : 10:14:36

Hi,
I have the SQL syntax below which pulls records from a related table up to the same row as the master table and merges them into 1 field.
The script works perfectly except for 1 minor tweak which i cant seem to get.
I need to sort the related table by a field called ID but i cant get the order clause by fit in this syntax.
In this example the table to sort is called "nicars_images), its actually a view which has the records sorted by ID already but for some reason is lost when the query is outputted.

SELECT VEHICLES.ID AS VEHICLEID, '' AS REGISTRATION, MAKES.MAKE, VEHICLES.MODEL, '"' + REPLACE(VEHICLES.SUMMARY, '''', '\"') + '"' AS TITLE,
'' AS TRANSMISSION, VEHICLES.FUEL, '' AS BODYSTYLE, VEHICLES.PRICE, VEHICLES.YEAR, VEHICLES.MILEAGE, 0 AS DOORS, '' AS COLOUR,
'' AS INSGRP, '' AS LOCATION, '"' + REPLACE(VEHICLES.DESCRIPTION, '''', '\"') + '"' AS DESCRIPTION, IMAGES = '"' + REPLACE
((SELECT DISTINCT 'http://www.clivehamiltonmotors.com/images/vehicles/' + IMAGE + ',' AS [data()]
FROM NICARS_IMAGES
WHERE REL_VEHICLE_ID = VEHICLES.ID FOR XML PATH('')), '', '-') + '"'
FROM VEHICLES INNER JOIN
MAKES ON VEHICLES.MAKE = MAKES.ID

Mal Devlin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-06 : 13:29:38

thats peredictable. even if you've order by in view definition, it wont order results unless you use order by in select statement which retrieves data from view. From sql 2005 onwards order by used inside view has no effect. see below

http://visakhm.blogspot.com/2010/01/behaviour-of-order-by-inside-view.html

Now in your case i think what you need is


SELECT VEHICLES.ID AS VEHICLEID, '' AS REGISTRATION, MAKES.MAKE, VEHICLES.MODEL, '"' + REPLACE(VEHICLES.SUMMARY, '''', '\"') + '"' AS TITLE, 
'' AS TRANSMISSION, VEHICLES.FUEL, '' AS BODYSTYLE, VEHICLES.PRICE, VEHICLES.YEAR, VEHICLES.MILEAGE, 0 AS DOORS, '' AS COLOUR, 
'' AS INSGRP, '' AS LOCATION, '"' + REPLACE(VEHICLES.DESCRIPTION, '''', '\"') + '"' AS DESCRIPTION, IMAGES = '"' + REPLACE
((SELECT DISTINCT 'http://www.clivehamiltonmotors.com/images/vehicles/' + IMAGE + ',' AS [data()]
FROM NICARS_IMAGES
WHERE REL_VEHICLE_ID = VEHICLES.ID 
ORDER BY REL_VEHICLE_ID
FOR XML PATH('')), '', '-') + '"'
FROM VEHICLES INNER JOIN
MAKES ON VEHICLES.MAKE = MAKES.ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

advancesolutions
Starting Member

2 Posts

Posted - 2010-09-06 : 17:42:55

visakh16,
THANKS for your time on this, this has now worked perfectly for me, I just had to remove the DISTINCT after I added ORDER BY and it worked a treat. The help is much appreciated so thanks once more.

Mal Devlin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources