Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

splitting table data into columns and xml in another table Expand / Collapse
Posted Wednesday, October 3, 2012 5:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 8:24 AM
Points: 7, Visits: 83
I have a table in the below format ,
Create TABLE tab1
UniqueID VARCHAR(3),
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)

UniqueID ID Name Latitude Longitude
ABC 1 LPR 1.234 2.345
ABC 2 LPR 2.234 3.345
ABC 3 LPR 3.234 4.345
ABC 4 LPR 4.234 5.345

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

UniqueID ID Data Metadata
ABC 1 LPR <Metadata><Latitude>1.234000000000000e+000</Latitude><Longitude>2.345000000000000e+000</Longitude></Metadata>
ABC 2 LPR <Metadata><Latitude>2.234000000000000e+000</Latitude><Longitude>3.345000000000000e+000</Longitude></Metadata>
ABC 3 LPR <Metadata><Latitude>3.234000000000000e+000</Latitude><Longitude>4.345000000000000e+000</Longitude></Metadata>
ABC 4 LPR <Metadata><Latitude>4.234000000000000e+000</Latitude><Longitude>5.345000000000000e+000</Longitude></Metadata>

CAn any body help me out?
Post #1367561
Posted Wednesday, October 3, 2012 5:16 AM



Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555

SELECT UniqueID,ID,Name,
(SELECT Latitude,
FOR XML PATH('Metadata'),TYPE) AS Metadata
FROM tab1;


How to get the best help on a forum

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1367565
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse