October 2, 2012 at 9:57 am
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
October 2, 2012 at 11:54 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 2, 2012 at 4:13 pm
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)
October 2, 2012 at 6:54 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 3, 2012 at 5:06 am
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)
UniqueIDIDNameLatitudeLongitude
ABC1LPR1.2342.345
ABC2LPR2.2343.345
ABC3LPR3.2344.345
ABC4LPR4.2345.345
I want the results in below format ( first three columns is same, but last two columns in XML format)
UniqueIDIDDataMetadata
ABC1LPR<Metadata><Latitude>1.234000000000000e+000</Latitude><Longitude>2.345000000000000e+000</Longitude></Metadata>
ABC2LPR<Metadata><Latitude>2.234000000000000e+000</Latitude><Longitude>3.345000000000000e+000</Longitude></Metadata>
ABC3LPR<Metadata><Latitude>3.234000000000000e+000</Latitude><Longitude>4.345000000000000e+000</Longitude></Metadata>
ABC4LPR<Metadata><Latitude>4.234000000000000e+000</Latitude><Longitude>5.345000000000000e+000</Longitude></Metadata>
CAn any body help me out?
October 3, 2012 at 8:31 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply