February 2, 2017 at 12:18 am
Hi I have doubt in ssis
source : emp
id |currency
1 |<?xml version="1.0" encoding="utf-16"?><double>0.7915</double>
2 |<?xml version="1.0" encoding="utf-16"?><double>-1</double>
based on this table I want output like below
target : empdest
id | currency -1
1 | 0.79152
2 |
I tried like below in derived column expression
SUBSTRING(currency,50,(FINDSTRING(currency),"</",1)))SUBSTRING(currency,50,(FINDSTRING(currency),"</",1)))
above expression not give expected result .
please tell me how to write expression in ssis to solve this issue in ssis[/code]target : empdestI tried like below in derived column expressionabove expression not give expected result .please tell me how to write expression in ssis to solve this issue in ssis
February 2, 2017 at 2:28 am
How are you getting the value from your table. Could you not instead retreive the value straight from your SQL table? For example:CREATE TABLE #Sample (ID INT IDENTITY(1,1),
XMLValue XML)
GO
INSERT INTO #Sample (XMLValue)
VALUES ('<?xml version="1.0"?><double>0.7915</double>'),
('<?xml version="1.0"?><double>-1</double>');
GO
SELECT *,
XMLValue.value('/double[1]', 'varchar(50)') AS Currency
FROM #Sample;
GO
DROP TABLE #Sample
NOte that however much you try, ID one is not going to have a value of 0.7952. That precision isn't in your XML, so has already been lost. You can't gain back lost precision.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 2, 2017 at 3:32 am
Hi THom A, above given information is helpful but my source data some times comes with table some times data came with text files.
as per given information is helpfully with using of table.I need same logic using expression in derived column because of some time source data comes text files. please help me how to write expression to resolve this issue in ssis.
February 2, 2017 at 3:42 am
If you have to do it with an expression, this should work:SUBSTRING( @[User::XML], FINDSTRING( @[User::XML],"<double>", 1) + 8, FINDSTRING( @[User::XML],"</double>", 1) - (FINDSTRING( @[User::XML],"<double>", 1) + 8))
Where @[User::XML] is the value of your XML string.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply