Export Shipment Detail Data to XML

Question

I am looking to build a customization to export shipment header and line detail information to an XML file. I can do this easily within SQL using the XML writing shortcut, but I’m curious whether I can do something similar within Acumatica to generate the same file.

There are a few things I shortcut below, such as contact info is not in the header of the shipment, but this is close to the format I am looking to export.

SELECT ship.ShipmentNbr AS [@ShipmentNbr]
, ship.Customer As [@CustomerID]
, ship.CustomerName As [@CustomerName]
, ship.ShipmentDate As [@ShipDate]
, ship.CompanyName As [@ShipToName]
, ship.Phone1 As [@Phone1]
, ship.Fax As [@Fax]
, ship.Email As [@Email]
, ship.Location As [@Location]
, ship.LocationName As [@LocationName]
, ship.WarehouseID As [@WarehouseID]
, ship.WarehouseName As [@WarehouseName]
, ship.TotalShipmentQty As [@TotalShipmentQty]
, ship.ShippedWeight As [@ShippedWeight]
, ship.ShipVia As [@ShipVia]
, ship.AddressLine1 As [@AddressLine1]
, ship.AddressLine2 As [@AddressLine2]
, ship.City As [@City]
, ship.State As [@State]
, ship.PostalCode As [@PostalCode]
, ship.Country As [@Country]
   ,(
SELECT line.InventoryID AS [@InventoryID]
       ,line.ItemDescription AS [@ItemDescription]
       ,line.LineQty AS [@ShippedQty]
       ,line.UOM AS [@UOM]
FROM dbo.SOShipLine AS line
INNER JOIN dbo.SOShipment AS shipment ON line.ShipmentNbr=shipment.ShipmentNbr
WHERE ship.ShipmentNbr=shipment.ShipmentNbr
FOR XML PATH('InventoryID'),TYPE
) AS Items
FROM dbo.SOShipment AS ship
FOR XML PATH('ShipmentNbr'),ROOT('Shipment')

icon

Best answer by Django 7 February 2023, 20:23

View original

Django · Accepted Answer

You only get a flat structure, unfortunately, with the XML provider.

This page shows the very basics on how to create your own provider.

https://asiablog.acumatica.com/2016/09/custom-integration-services-data.html

The blog post talks about creating a CSV file but you can create anything that .Net can create - you have 100% control over the generation of the file.

The good work happens in

protected override byte[] InternalExport(string objectName, PXSYTable table)

and, in my case, I build up the contents of my file in a List<string> object and then turn the contents of the list into the array of bytes:

data = FileLines.SelectMany(s => System.Text.Encoding.UTF8.GetBytes(s)).ToArray();

return data;

You can do the same to build the XML file however you want to build it and then turn it into an array of types at the end and you’ll have your XML file.

Let me know if you have further questions. I can share my code but it really is mostly a derivative of the blog post along with some help that I received from @Gabriel Michaud .

Django · Answer

You could look into creating a custom data provider to generate the XML file. That allows you to use an Export scenario with parameters so you can control what shipment you want to generate the file for.

Export Shipment Detail Data to XML

3 replies

Reply

Reply

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded