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
Author
Message
Posted Wednesday, October 03, 2012 5:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 08, 2012 8:24 AM
Points: 7, Visits: 83
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)


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 03, 2012 5:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550


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



____________________________________________________

How to get the best help on a forum

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

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