Ok, I was able to come up with a solution. It's not the best idea but it works. I would love to hear if anyone has a better way.
TempOpportunities:
LOAD no,
%Key_row_E3D0E1B2094648A7
FROM [my xml file here] (XmlSimple, Table is [response/result/Potentials/row]);
LEFT JOIN (TempOpportunities)
LOAD
val,
FL%Table,
%Key_row_E3D0E1B2094648A7
FROM [my xml file here] (XmlSimple, Table is [response/result/Potentials/row/FL]);
Opportunities:
LOAD DISTINCT
no,
FL%Table AS PotentialOwner
RESIDENT TempOpportunities
WHERE val='Potential Owner';
JOIN (Opportunities)
LOAD DISTINCT
no,
FL%Table AS Amount
RESIDENT TempOpportunities
WHERE val='Amount';
JOIN (Opportunities)
LOAD DISTINCT
no,
FL%Table AS PotentialName
RESIDENT TempOpportunities
WHERE val='Potential Name';
JOIN (Opportunities)
LOAD DISTINCT
no,
FL%Table AS ClosingDate
RESIDENT TempOpportunities
WHERE val='Closing Date';
JOIN (Opportunities)
LOAD DISTINCT
no,
FL%Table AS AccountName
RESIDENT TempOpportunities
WHERE val='Account Name';
JOIN (Opportunities)
LOAD DISTINCT
no,
FL%Table AS Stage
RESIDENT TempOpportunities
WHERE val='Stage';
JOIN (Opportunities)
LOAD DISTINCT
no,
FL%Table AS Probability
RESIDENT TempOpportunities
WHERE val='Probability';
JOIN (Opportunities)
LOAD DISTINCT
no,
FL%Table AS Description
RESIDENT TempOpportunities
WHERE val='Description';
JOIN (Opportunities)
LOAD DISTINCT
no,
FL%Table AS ContactName
RESIDENT TempOpportunities
WHERE val='Contact Name';
JOIN (Opportunities)
LOAD DISTINCT
no,
FL%Table AS ExpectedRevenue
RESIDENT TempOpportunities
WHERE val='Expected Revenue';
JOIN (Opportunities)
LOAD DISTINCT
no,
FL%Table AS USAPracticeEngaged
RESIDENT TempOpportunities
WHERE val='USA Practice Engaged';
JOIN (Opportunities)
LOAD DISTINCT
no,
FL%Table AS USACustomer
RESIDENT TempOpportunities
WHERE val='USA Customer';
DROP TABLE TempOpportunities;
