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

Converting table data into Table columns + <xmlformat> in last column Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 9:57 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 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
Posted Tuesday, October 2, 2012 11:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
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 Moden's 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)
Post #1367210
Posted Tuesday, October 2, 2012 4:13 PM
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
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
Posted Tuesday, October 2, 2012 6:54 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
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 Moden's 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)
Post #1367363
Posted Wednesday, October 3, 2012 5:06 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),
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
Posted Wednesday, October 3, 2012 8:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 13,085, Visits: 12,549
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1367724
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse