Converting table data into Table columns + <xmlformat> in last column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » XML » Converting table data into Table columns +...

Converting table data into Table columns + <xmlformat> in last column

Rate Topic

Display Mode

Topic Options

Author

Message

Vijay_Raju

Posted Tuesday, October 02, 2012 9:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 08, 2012 8:24 AM
Points: 7, Visits: 83

I have a Table with 20 columns the first 12 columns needs to be same as it is, but from 13th column to 20 column i want it in xml format and should be placed in 13th column.

Can any one suggest best way of doing this in SSIS

Post #1367141

Sean Lange

Posted Tuesday, October 02, 2012 11:54 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 8,967, Visits: 8,530

Vijay_Raju (10/2/2012)

Not sure why you need to use SSIS to change a single table. Not sure what you mean by xml format but can you just change the datatype to xml for the columns you want to change?

Or are you saying to want to intentionally combine columns 13 - 20 into a single xml column? Again I don't know why you want to use SSIS for this.

I am pretty sure that a query can do everything you are trying to do here. Take a look at the first link in my signature about best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1367210

Vijay_Raju

Posted Tuesday, October 02, 2012 4:13 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 08, 2012 8:24 AM
Points: 7, Visits: 83

Let me explain my requirement in detail.

I have a table A with 20 rows. I need to supply the data for my down stream system in a format of a table B with 13 rows . first 12 rows of Table A and Table B are same. I need to populate the 13th column of table B in xml format ( with 13-20 rows in table A)

Post #1367322

Sean Lange

Posted Tuesday, October 02, 2012 6:54 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 8,967, Visits: 8,530

Vijay_Raju (10/2/2012)

That isn't exactly what I would call explaining it in detail. I can't see what you see from here and I have no knowledge of your project, data or requirements.

I suggested previously you read the article in my signature. It seems you did not. I can't even begin to help you because I don't have anything to work with. I don't know what the source table and data look like and I don't know what you want for output. If you can post ddl (create table statements), sample data (insert statements) along with the desired output based on your sample data this is probably fairly straight forward.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1367363

Vijay_Raju

Posted Wednesday, October 03, 2012 5:06 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 #1367559

Sean Lange

Posted Wednesday, October 03, 2012 8:31 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 8,967, Visits: 8,530

Thanks for the ddl and sample data. It makes all the difference.

This produces your desired results based on the sample you provided.

select UniqueID, ID, Name, 
(
	select Latitude, Longitude
	from tab1
	where ID = t1.ID
	for XML RAW, ROOT('Metadata')
)
from tab1 t1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1367724

« Prev Topic | Next Topic »

Permissions