XML import into SQL Server 2005 using SSIS

• smacumber 77733

  Ten Centuries

  Points: 1288

  More actions

  September 15, 2010 at 2:47 pm

  #143453

  Here is a sample of the data I have, I created XML Source that draws this in via C:\Test\xml_data.xml

  Then I create the OLE DB Destination, 1 table that has all these fields but when I connect XML Source

  to the OLE DB Destination I have to select an input,

  Order, Originator, Customer, ServiceAddress, OrderDetail, or OrderDetailList - I choose Order.

  It imports to the table but obviously only imports the data OrderId="12313" OrderDate="20080820" OrderTime="13:34:56".

  How do I get it bring in all this data as 1 line in a table.

  - <OrderList>

  - <Order OrderId="12313" OrderDate="20080820" OrderTime="13:34:56">

  - <Originator>

  <PromoId>1234560</PromoId>

  <Source>TestSource1</Source>

  <Campaign>TestCampaign1</Campaign>

  <Option1>TestOption1</Option1>

  <Option2>TestOption2</Option2>

  <Option3>TestOption3</Option3>

  <Option4>TestOption4</Option4>

  <Option5>TestOption5</Option5>

  <ReferalURL>http://www.google.com/test/test/test/123.html</ReferalURL&gt;

  <IPAddress>192.23.313.123</IPAddress>

  </Originator>

  - <Customer>

  <FirstName>FirstName</FirstName>

  <LastName>LastName</LastName>

  <EmailAddress>bvides@acceller.com</EmailAddress>

  <ServicePhone>7036072212</ServicePhone>

  - <ServiceAddress>

  <Address1>123 Main St</Address1>

  <Address2>Apt 4</Address2>

  <Address3>Unit 2</Address3>

  <City>Miami</City>

  <State>FL</State>

  <PostalCode>33134</PostalCode>

  <PostalCodeExtension>1234</PostalCodeExtension>

  </ServiceAddress>

  </Customer>

  - <OrderDetailList>

  - <OrderDetail OrderDetailId="2321231231" ServiceType="VoiceCopper" OfferCode="ABCTER1" ConfirmationCode="21342324424234">

  <ProviderName>Comcast</ProviderName>

  <OfferName>Premier HighSpeed Internet Plus 8MB</OfferName>

  </OrderDetail>

  </OrderDetailList>

  </Order>

  </OrderList>