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?

2
  • Can the file contain many headers? If so, can you provide an example please?
    – wBob
    Nov 14, 2016 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, 2016 at 23:38

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.