splitting table data into columns and xml in another table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » General » splitting table data into columns and xml in...

splitting table data into columns and xml in another table

Rate Topic

Display Mode

Topic Options

Author

Message

Vijay_Raju

Posted Wednesday, October 03, 2012 5:07 AM

Forum 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

Mark-101232

Posted Wednesday, October 03, 2012 5:16 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 9:30 AM
Points: 1,500, Visits: 18,174

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 »

Permissions