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 -
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?
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?