Home
%3CLINGO-SUB%20id%3D%22lingo-sub-387365%22%20slang%3D%22en-US%22%3EXML%20Source%20-%20Making%20things%20easier%20with%20XSLT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-387365%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Dec%2015%2C%202007%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EAs%20I%20wrote%20in%20my%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fmattm%2Farchive%2F2007%2F12%2F11%2Fusing-xml-source.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20previous%20post%20%3C%2FA%3E%20%2C%20complex%20XML%20documents%20will%20produce%20multiple%20outputs%20when%20you're%20using%20the%20XML%20Source%20adapter.%20Most%20of%20the%20time%20it%20will%20be%20easier%20to%20pre-process%20your%20source%20file%20with%20XSLT%20to%20de-normalize%20it%20a%20bit.%20Reducing%20the%20number%20of%20outputs%20greatly%20simplifies%20your%20data%20flow.%3C%2FP%3E%0A%20%20%3CP%3ELet's%20take%20the%20same%20XML%20document%20I%20used%20in%20the%20last%20example%3A%3C%2FP%3E%0A%20%20%3CDIV%3E%0A%20%20%20%3CDIV%3E%0A%20%20%20%20%3CEXTRACT%20date%3D%22%26quot%3B2007-12-05%26quot%3B%22%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOUNTERS%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOUNTER%20category%3D%22%26quot%3Bdispatcher%26quot%3B%22%20name%3D%22%26quot%3Bserver1%26quot%3B%22%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CRUNTIME%3E6%3C%2FRUNTIME%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CQUEUE%3E3%3C%2FQUEUE%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CMAXREQUEST%3E8%3C%2FMAXREQUEST%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOLOR%3Eblue%3C%2FCOLOR%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CHOST%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CNAME%3Esvo2555%3C%2FNAME%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CPATH%3E%5C%5Cdispatcher%3C%2FPATH%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CLASTACCESSED%3E2007-02-03%3C%2FLASTACCESSED%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FHOST%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FCOUNTER%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOUNTER%20category%3D%22%26quot%3Bgateway%26quot%3B%22%20name%3D%22%26quot%3Bserver1%26quot%3B%22%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CRUNTIME%3E1%3C%2FRUNTIME%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CQUEUE%3E10%3C%2FQUEUE%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CMAXREQUEST%3E10%3C%2FMAXREQUEST%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOLOR%3Epurple%3C%2FCOLOR%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CHOST%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CNAME%3Esvo2555%3C%2FNAME%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CPATH%3E%5C%5Cgateway%3C%2FPATH%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CLASTACCESSED%3E2007-02-03%3C%2FLASTACCESSED%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FHOST%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FCOUNTER%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FCOUNTERS%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FEXTRACT%3E%20%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EWe%20want%20to%20flatten%20this%20out%20a%20bit%20using%20an%20XSL%20transform%20like%20this%20one%20(forgive%20my%20novice%20XSLT%20skills)%3A%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CBR%20%2F%3E%20%3CSTYLESHEET%20version%3D%22%26quot%3B1.0%26quot%3B%22%20xsl%3D%22%26quot%3B%26lt%3BA%22%20href%3D%22http%3A%2F%2Fwww.w3.org%2F1999%2FXSL%2FTransform%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3E%3CA%20href%3D%22http%3A%2F%2Fwww.w3.org%2F1999%2FXSL%2FTransform%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.w3.org%2F1999%2FXSL%2FTransform%3C%2FA%3E%22%26gt%3B%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3COUTPUT%20method%3D%22%26quot%3Bxml%26quot%3B%22%20indent%3D%22%26quot%3Byes%26quot%3B%2F%22%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CTEMPLATE%20match%3D%22%26quot%3B%2Fextract%26quot%3B%22%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CVARIABLE%20name%3D%22%26quot%3BextractDate%26quot%3B%22%20select%3D%22%26quot%3B%2Fextract%2F%40date%26quot%3B%22%3E%3C%2FVARIABLE%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOUNTERS%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CFOR-EACH%20select%3D%22%26quot%3Bcounters%2Fcounter%26quot%3B%22%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOUNTER%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CEXTRACTDATE%3E%3CVALUE-OF%20select%3D%22%26quot%3B%24extractDate%26quot%3B%2F%22%3E%3C%2FVALUE-OF%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCATEGORY%3E%3CVALUE-OF%20select%3D%22%26quot%3B%40category%26quot%3B%2F%22%3E%3C%2FVALUE-OF%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CNAME%3E%3CVALUE-OF%20select%3D%22%26quot%3B%40name%26quot%3B%2F%22%3E%3C%2FVALUE-OF%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CRUNTIME%3E%3CVALUE-OF%20select%3D%22%26quot%3Bruntime%26quot%3B%2F%22%3E%3C%2FVALUE-OF%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CQUEUE%3E%3CVALUE-OF%20select%3D%22%26quot%3Bqueue%26quot%3B%2F%22%3E%3C%2FVALUE-OF%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CMAXREQUEST%3E%3CVALUE-OF%20select%3D%22%26quot%3Bmaxrequest%26quot%3B%2F%22%3E%3C%2FVALUE-OF%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOLOR%3E%3CVALUE-OF%20select%3D%22%26quot%3Bcolor%26quot%3B%2F%22%3E%3C%2FVALUE-OF%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CHOSTNAME%3E%3CVALUE-OF%20select%3D%22%26quot%3Bhost%2Fname%26quot%3B%2F%22%3E%3C%2FVALUE-OF%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CPATH%3E%3CVALUE-OF%20select%3D%22%26quot%3Bhost%2Fpath%26quot%3B%2F%22%3E%3C%2FVALUE-OF%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CLASTACCESSED%3E%3CVALUE-OF%20select%3D%22%26quot%3Bhost%2Flastaccessed%26quot%3B%2F%22%3E%3C%2FVALUE-OF%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FLASTACCESSED%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FPATH%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FHOSTNAME%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FCOLOR%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FMAXREQUEST%3E%20%3CBR%20%2F%3E%3C%2FQUEUE%3E%3C%2FRUNTIME%3E%3C%2FNAME%3E%3C%2FCATEGORY%3E%3C%2FEXTRACTDATE%3E%3C%2FCOUNTER%3E%3C%2FFOR-EACH%3E%3C%2FCOUNTERS%3E%3C%2FTEMPLATE%3E%3C%2FOUTPUT%3E%3C%2FSTYLESHEET%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EWe'll%20apply%20the%20transform%20with%20an%20XML%20Task.%20Add%20one%20to%20your%20package%2C%20and%20open%20the%20editor.%20You'll%20want%20to%20change%20the%20Operation%20Type%20property%20to%20XSLT%2C%20set%20SaveOperationResult%20to%20true%2C%20and%20set%20all%20of%20the%20file%20connections.%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F99349iF2654263677D8F7B%22%20%2F%3E%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3ENote%2C%20the%20Source%20should%20be%20your%20XML%20source%20document%20and%20the%20SecondOperand%20is%20your%20XSLT%20document.%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EThe%20processed%20XML%20looks%20like%20this%3A%3C%2FP%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CDIV%3E%3CBR%20%2F%3E%3CDIV%3E%3CBR%20%2F%3E%20%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOUNTERS%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOUNTER%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CEXTRACTDATE%3E2007-12-05%3C%2FEXTRACTDATE%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCATEGORY%3Edispatcher%3C%2FCATEGORY%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CNAME%3Eserver1%3C%2FNAME%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CRUNTIME%3E6%3C%2FRUNTIME%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CQUEUE%3E3%3C%2FQUEUE%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CMAXREQUEST%3E8%3C%2FMAXREQUEST%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOLOR%3Eblue%3C%2FCOLOR%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CHOSTNAME%3Esvo2555%3C%2FHOSTNAME%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CPATH%3E%5C%5Cdispatcher%3C%2FPATH%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CLASTACCESSED%3E2007-02-03%3C%2FLASTACCESSED%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FCOUNTER%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOUNTER%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CEXTRACTDATE%3E2007-12-05%3C%2FEXTRACTDATE%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCATEGORY%3Egateway%3C%2FCATEGORY%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CNAME%3Eserver1%3C%2FNAME%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CRUNTIME%3E1%3C%2FRUNTIME%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CQUEUE%3E10%3C%2FQUEUE%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CMAXREQUEST%3E10%3C%2FMAXREQUEST%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CCOLOR%3Epurple%3C%2FCOLOR%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CHOSTNAME%3Esvo2555%3C%2FHOSTNAME%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CPATH%3E%5C%5Cgateway%3C%2FPATH%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CLASTACCESSED%3E2007-02-03%3C%2FLASTACCESSED%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FCOUNTER%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3C%2FCOUNTERS%3E%20%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3C%2FDIV%3E%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3EAdd%20a%20Data%20Flow%20Task%2C%20and%20setup%20your%20XML%20source%20to%20use%20the%20processed%20XML%20document.%20You'll%20need%20to%20update%2Fregenerate%20the%20schema%20for%20your%20document%20to%20account%20for%20the%20new%20format.%20Notice%20there%20is%20now%20only%20one%20output%20to%20deal%20with.%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-387365%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Dec%2015%2C%202007%20As%20I%20wrote%20in%20my%20previous%20post%2C%20complex%20XML%20documents%20will%20produce%20multiple%20outputs%20when%20you're%20using%20the%20XML%20Source%20adapter.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-387365%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EXML%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-517790%22%20slang%3D%22en-US%22%3ERe%3A%20XML%20Source%20-%20Making%20things%20easier%20with%20XSLT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-517790%22%20slang%3D%22en-US%22%3E%3CP%3Ehow%20were%20you%20able%20to%20generate%20xsl%20file%20%3F%20i%20have%20a%20monster%20xml%20and%20it's%20xsd.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-780019%22%20slang%3D%22en-US%22%3ERe%3A%20XML%20Source%20-%20Making%20things%20easier%20with%20XSLT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-780019%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20SqlSinner1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edid%20you%20find%20out%20how%20to%20generate%20a%20XSLT%20file%3F%20i%20have%20a%26nbsp%3B%20big%20XML%20File%20and%20its%20XSD.%26nbsp%3B%20it%20has%26nbsp%3B%20multiple%20root%20nodes.%20can%20you%20please%20help%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Not applicable
First published on MSDN on Dec 15, 2007

As I wrote in my previous post , complex XML documents will produce multiple outputs when you're using the XML Source adapter. Most of the time it will be easier to pre-process your source file with XSLT to de-normalize it a bit. Reducing the number of outputs greatly simplifies your data flow.

Let's take the same XML document I used in the last example:

<extract date="2007-12-05">

<counters>

<counter category="dispatcher" name="server1">

<runtime>6</runtime>

<queue>3</queue>

<maxrequest>8</maxrequest>

<color>blue</color>

<host>

<name>svo2555</name>

<path>\\dispatcher</path>

<lastaccessed>2007-02-03</lastaccessed>

</host>

</counter>

<counter category="gateway" name="server1">

<runtime>1</runtime>

<queue>10</queue>

<maxrequest>10</maxrequest>

<color>purple</color>

<host>

<name>svo2555</name>

<path>\\gateway</path>

<lastaccessed>2007-02-03</lastaccessed>

</host>

</counter>

</counters>

</extract>



We want to flatten this out a bit using an XSL transform like this one (forgive my novice XSLT skills):





<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:output method="xml" indent="yes"/>

<xsl:template match="/extract">

<xsl:variable name="extractDate" select="/extract/@date" />

<counters>

<xsl:for-each select="counters/counter">

<counter>

<extractDate><xsl:value-of select="$extractDate"/></extractDate>

<category><xsl:value-of select="@category"/></category>

<name><xsl:value-of select="@name"/></name>

<runtime><xsl:value-of select="runtime"/></runtime>

<queue><xsl:value-of select="queue"/></queue>

<maxrequest><xsl:value-of select="maxrequest"/></maxrequest>

<color><xsl:value-of select="color"/></color>

<hostName><xsl:value-of select="host/name"/></hostName>

<path><xsl:value-of select="host/path"/></path>

<lastaccessed><xsl:value-of select="host/lastaccessed"/></lastaccessed>

</counter>

</xsl:for-each>

</counters>

</xsl:template>

</xsl:stylesheet>



We'll apply the transform with an XML Task. Add one to your package, and open the editor. You'll want to change the Operation Type property to XSLT, set SaveOperationResult to true, and set all of the file connections.





Note, the Source should be your XML source document and the SecondOperand is your XSLT document.



The processed XML looks like this:





<?xml version="1.0" encoding="utf-8"?>

<counters>

<counter>

<extractDate>2007-12-05</extractDate>

<category>dispatcher</category>

<name>server1</name>

<runtime>6</runtime>

<queue>3</queue>

<maxrequest>8</maxrequest>

<color>blue</color>

<hostName>svo2555</hostName>

<path>\\dispatcher</path>

<lastaccessed>2007-02-03</lastaccessed>

</counter>

<counter>

<extractDate>2007-12-05</extractDate>

<category>gateway</category>

<name>server1</name>

<runtime>1</runtime>

<queue>10</queue>

<maxrequest>10</maxrequest>

<color>purple</color>

<hostName>svo2555</hostName>

<path>\\gateway</path>

<lastaccessed>2007-02-03</lastaccessed>

</counter>

</counters>



Add a Data Flow Task, and setup your XML source to use the processed XML document. You'll need to update/regenerate the schema for your document to account for the new format. Notice there is now only one output to deal with.

2 Comments
Occasional Visitor

how were you able to generate xsl file ? i have a monster xml and it's xsd.

Occasional Visitor

Hello SqlSinner1

 

did you find out how to generate a XSLT file? i have a  big XML File and its XSD.  it has  multiple root nodes. can you please help