SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Vijay_Raju
Vijay_Raju
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26748 Visits: 17557
Vijay_Raju (10/2/2012)
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


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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Vijay_Raju
Vijay_Raju
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26748 Visits: 17557
Vijay_Raju (10/2/2012)
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)


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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Vijay_Raju
Vijay_Raju
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26748 Visits: 17557
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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search