June 1, 2011 at 12:40 pm
Actual input:
<INPUT>
<<tag name="DUEDATE">1/25/2011</tag>
<<tag name="FORMTYPE">Single Family URAR, FNMA 2005 Form 1004</tag>
<<tag name="LOANTYPE">Market Value</tag>
</INPUT>
Required output:
<OUTPUT>
<DUEDATE>1/25/2011< DUEDATE>
<FORMTYPE> Single Family URAR, FNMA 2005 Form 1004</ FORMTYPE>
<LOANTYPE > Market Value </LOANTYPE >
</OUTPUT>
I want to get this output using SSIS(DTS). Will any one help or give me suggestion to use which transformations.
Thank you
June 1, 2011 at 12:56 pm
You won't get there in SSIS components alone.
You'll either need to feed it down to a SQL query, use XQuery, and then reform it or you'll need to pass it to a script component and then use the .NET XML components to reform it.
It's neither quick nor simple.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 1, 2011 at 1:05 pm
That would require some very complex XQuery, including the XQuery XML modification operators. Not quick, not easy.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2011 at 2:44 pm
XSLT might be a good option for you. It can do transformation work (the 'T' in XSLT) and can be easily integrated into an SSIS process using the XML Task (lookup operation type XSLT).
Here is a good primer on where to start from an SSIS perspective:
http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx
If transforming the XML file is the only task needed XSLT can be utilized outside of SSIS using MSXML. Using XSLT with MSXML:
http://msdn.microsoft.com/en-us/library/ms767600(v=VS.85).aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 2, 2011 at 3:02 pm
I'm not sure how I'd do it in SSIS, but in T-SQL, it would look something like:
DECLARE @XML XML = '<INPUT>
<tag name="DUEDATE">1/25/2011</tag>
<tag name="FORMTYPE">Single Family URAR, FNMA 2005 Form 1004</tag>
<tag name="LOANTYPE">Market Value</tag>
</INPUT>';
SELECT '<' + I.T.query('.').value('(/tag/@name)[1]','varchar(1000)') + '>' +
I.T.query('.').value('(/tag/text())[1]','varchar(1000)') +
'</' + I.T.query('.').value('(/tag/@name)[1]','varchar(1000)') + '>'
FROM @XML.nodes('INPUT/tag') AS I(T);
If you know what the potential tags are before-hand, you could actually have the query generate XML instead of a string, by using the PIVOT operator, and FOR XML. If you want that, but have variable tags, you'll need to use one or another method of dynamic pivoting.
.NET languages have some powerful XML tools. A script in the SSIS package might be a better way to do this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply