2

I am using an XML document as a source in SSIS as below

<?xml version="1.0" encoding="utf-8"?>
<Doc>
  <Header>
    <ShipmentCode>ABC-EFG-123</ShipmentCode>
    <TruckCode>TRUCK-01</TruckCode>
    <TotalNumberOfPallets>3</TotalNumberOfPallets>
  </Header>  
  <Pallet>
    <SSCC>0012345</SSCC>
    <ScannedDate>1/1/2017</ScannedDate>
  </Pallet>
  <Pallet>
    <SSCC>0012346</SSCC>
    <ScannedDate>1/1/2017</ScannedDate>
  </Pallet>
  <Pallet>
    <SSCC>0012347</SSCC>
    <ScannedDate>1/1/2017</ScannedDate>
  </Pallet>
</Doc>

My XML source has two outputs - Header and Pallet and there is one Header record to many Pallet records, as you can see in the above XML file.

I actually want to insert records to a SQL table, but include some of the Header information along with the Pallet information as I insert. So the insert might include:

ShipmentCode: ABC-EFG-123, SSCC: 0012345 - Insert record one ShipmentCode: ABC-EFG-123, SSCC: 001234n - Insert record n

I've seen people using a Merge Join - but then I must take care of sorting? Is this example basic enough that wouldn't be a problem?

OR

I've seen people talking about using an XML task to transform the XML using XSLT. Is that the correct way to do this? If yes, how do I figure out the XSLT syntax?

  • Can the file contain many headers? If so, can you provide an example please? – wBob Nov 14 '16 at 21:47
  • At the moment it seems that there is no way to connect a Pallet to a Header in the source XML that you supplied. Perhaps when you created this sample XML you didn't accurately recreate some details? – mendosi Nov 14 '16 at 23:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.