Convert XML to CSV, then load to DB or Load XML to DB

• ankita.patel01 81294

  SSC-Addicted

  Points: 418

  More actions

  December 19, 2011 at 10:57 am

  #261929

  Hi,

  Does anyone know how this can be accomplished by ssis? I need to load an XML file to DB. I can transform xml to csv to DB or load directly xml to db. Does anyone have a sample example?

  Here's what my Xml file looks like.

  - <Package>

  - <PackageHeader>

  <PackageName>ABC</PackageName>

  <Verse>DE</Universe>

  <AsOfDate>2011-12-19</AsOfDate>

  <Version>2.2</Version>

  </PackageHeader>

  - <PackageBody>

  - <text _Id="ABCDEFGH01" _typeid="ABCDEFGH02">

  - <ListType>

  - <List>

  <Date>2011-12-10</Date>

  - <ListBreakdown _Post="L">

  - <Breakdown>

  <BreakdownValue Text="10">0.50</BreakdownValue>

  <BreakdownValue Type="15">25.00</BreakdownValue>

  </Breakdown>

  </ListBreakdown>

  </List>

  </ListType>

  </Text>

• GSquared

  SSC Guru

  Points: 260824

  More actions

  December 19, 2011 at 12:15 pm

  #1423266

  Have you tried setting up an XML connection in SSIS? Does that not do what you need?

  (Personally, I generally import XML files by using OpenRowset's Bulk CLOB options, and then using XQuery to shred the XML. But SSIS can do that too.)

  - 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

• ankita.patel01 81294

  SSC-Addicted

  Points: 418

  More actions

  December 19, 2011 at 12:37 pm

  #1423276

  i am trying to use XML task editor with these options:

  operation type = XSLT

  SaveOperationresult = true

  second operand type = direct input

  second operand =

  <xsl:stylesheet version="1.0"

  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:output match="text" />

  <xsl:variable name="Package_var" />

  <xsl:variable name="Packagename_Var" />

  <xsl:variable name="verse_Var" />

  <xsl:variable name="AsofDate_Var" />

  <xsl:variable name="Packagebody_var" />

  <xsl:template match="Package">

  <xsl:variable name="Package_Var"

  select="normalize-space(child::text())" />

  <xsl:for-each select="Packagename">

  <xsl:variable name="Packagename_Var"

  select="normalize-space(child::text())" />

  <xsl:for-each select="verse">

  <xsl:variable name="verse_Var"

  select="normalize-space(child::text())" />

  <xsl:for-each select="AsofDate">

  <xsl:variable name="AsofDate_Var"

  select="normalize-space(child::text())" />

  </xsl:for-each>

  </xsl:for-each>

  </xsl:for-each>

  </xsl:template>

  </xsl:stylesheet>

  but i keep getting xslt compile error. I'm not sure what else it needs.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  December 19, 2011 at 1:23 pm

  #1423295

  Please post the error message, if there is one.

  ---