Need help exporting Epicor data in a structured xml file with parent / child nodes.
Specifically trying to export ShipHead and ShipDtl records in xml where the ShipHead elements are listed first, then child nodes follow, one for each line on the pack.
Have tried exporting BAQs, and API calls to the business objects.
Best answer by ayrin.hilbert
View originalAgainst the Training database you if you run this SQL
SELECT ShipHead.PackNum as ShipperID, ShipHead.ShipDate, ShipDtl.PackLine, ShipDtl.PartNum
FROM erp.ShipHead ShipHead inner join erp.ShipDtl ShipDtl on ShipHead.Company=ShipDtl.Company and ShipHead.PackNum = ShipDtl.PackNum
WHERE ShipHead.Company = 'EPIC03' and ShipHead.PackNum = 103 FOR XML AUTO, ELEMENTS
You get:
<ShipHead>
<ShipperID>103</ShipperID>
<ShipDate>2018-01-10</ShipDate>
<ShipDtl>
<PackLine>1</PackLine>
<PartNum>ML-1698-A36</PartNum>
</ShipDtl>
<ShipDtl>
<PackLine>2</PackLine>
<PartNum>ML-1698-A36</PartNum>
</ShipDtl>
...other rows I deleted…..
<ShipDtl>
<PackLine>8</PackLine>
<PartNum>ML-1698-A36</PartNum>
</ShipDtl>
</ShipHead>
You could add a button to the ship entry screen to run this SQL and save it out to file. Or there is a way to do it right in SQL, but you have to enable some options on the SQL server.
Hope this helps,
Ayrin
Thanks, Ayrin.
I can probably put that in a function. Do you know if that concept works in a LYNQ query?
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.