splitting table data into columns and xml in another table

• Vijay_Raju

  Valued Member

  Points: 67

  More actions

  October 3, 2012 at 5:07 am

  #96978

  I have a table in the below format ,

  Create TABLE tab1

  (

  UniqueID VARCHAR(3),

  ID INT,

  Name VARCHAR(25),

  Latitude FLOAT,

  Longitude FLOAT

  );

  INSERT INTO tab1 VALUES ('ABC', 1, 'LPR', 1.234, 2.345)

  INSERT INTO tab1 VALUES ('ABC', 2, 'LPR', 2.234, 3.345)

  INSERT INTO tab1 VALUES ('ABC', 3, 'LPR', 3.234, 4.345)

  INSERT INTO tab1 VALUES ('ABC', 4, 'LPR', 4.234, 5.345)

  UniqueIDIDNameLatitudeLongitude

  ABC1LPR1.2342.345

  ABC2LPR2.2343.345

  ABC3LPR3.2344.345

  ABC4LPR4.2345.345

  I want the results in below format ( first three columns is same, but last two columns in XML format)

  UniqueIDIDDataMetadata

  ABC1LPR<Metadata><Latitude>1.234000000000000e+000</Latitude><Longitude>2.345000000000000e+000</Longitude></Metadata>

  ABC2LPR<Metadata><Latitude>2.234000000000000e+000</Latitude><Longitude>3.345000000000000e+000</Longitude></Metadata>

  ABC3LPR<Metadata><Latitude>3.234000000000000e+000</Latitude><Longitude>4.345000000000000e+000</Longitude></Metadata>

  ABC4LPR<Metadata><Latitude>4.234000000000000e+000</Latitude><Longitude>5.345000000000000e+000</Longitude></Metadata>

  CAn any body help me out?

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  October 3, 2012 at 5:16 am

  #1544812

  SELECT UniqueID,ID,Name,

  (SELECT Latitude,

  Longitude

  FOR XML PATH('Metadata'),TYPE) AS Metadata

  FROM tab1;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537