Skip to content

09 XML export

The .xml renderer turns SRS results into an XML document without a template. Use it for file imports that expect a fixed XML structure, for example courier shipment files.

Execute

HTTP
1
GET /api/srs/{id}/{name}.xml

The response has content type application/xml and UTF-8 encoding. Use file_name to download it as a file:

HTTP
1
GET /api/srs/{id}/export.xml?file_name=shipments.xml

Output structure

  • root element - SRS label, used as-is (falls back to the file name when label is missing)
  • one element per result row, named after the command
  • one child element per column, named after the column
  • null values produce an empty element, for example <GROSS_WEIGHT />

Values are written in a culture-independent format:

  • numbers use . as the decimal separator
  • dates without time: yyyy-MM-dd
  • dates with time: yyyy-MM-ddTHH:mm:ss
  • bit columns: true / false

Skipped from the output:

  • columns and commands with ex="false"
  • commands with opts="server"
  • commands that return no rows

Tip

Element names are taken directly from the label, command and column names. Characters that are not allowed in XML names are escaped, for example a space becomes _x0020_. Use names such as DHL-SHIPMENT-IMPORT or INV_ITEM.

Nested elements

Use / in a column alias to create nested elements, the same way as SQL Server FOR XML PATH. Consecutive columns with the same parent path are written into one parent element.

XML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
<srs label="DHL-SHIPMENT-IMPORT">
  <def>
    <itm name="SHIPMENT">
      select s.id                 as [ID],
             i.description        as [INV_ITEM/INV_DESCRIPTION],
             i.commodity          as [INV_ITEM/COMMODITY],
             i.quantity           as [INV_ITEM/QUANTITY],
             'EA'                 as [INV_ITEM/QTY_UOM],
             i.value              as [INV_ITEM/ITEM_VALUE],
             'EUR'                as [INV_ITEM/ITEM_VALUE_CURRENCY],
             i.net_weight         as [INV_ITEM/NET_WEIGHT],
             i.gross_weight       as [INV_ITEM/GROSS_WEIGHT],
             i.country_origin     as [INV_ITEM/COUNTRY_ORIGIN],
             'SON'                as [INV_ITEM/REFERENCE_TYPE],
             s.reference          as [INV_ITEM/REFERENCE_NUMBER],
             'N'                  as [INV_ITEM/VAT_PAID]
      from shipment s
      join shipment_item i on i.shipment_id = s.id
    </itm>
  </def>
</srs>
XML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
<?xml version="1.0" encoding="utf-8"?>
<DHL-SHIPMENT-IMPORT>
  <SHIPMENT>
    <ID>1</ID>
    <INV_ITEM>
      <INV_DESCRIPTION>Women's dress made of cotton</INV_DESCRIPTION>
      <COMMODITY>1234.12.1234</COMMODITY>
      <QUANTITY>20</QUANTITY>
      <QTY_UOM>EA</QTY_UOM>
      <ITEM_VALUE>50</ITEM_VALUE>
      <ITEM_VALUE_CURRENCY>EUR</ITEM_VALUE_CURRENCY>
      <NET_WEIGHT>2</NET_WEIGHT>
      <GROSS_WEIGHT />
      <COUNTRY_ORIGIN>CN</COUNTRY_ORIGIN>
      <REFERENCE_TYPE>SON</REFERENCE_TYPE>
      <REFERENCE_NUMBER>12AB</REFERENCE_NUMBER>
      <VAT_PAID>N</VAT_PAID>
    </INV_ITEM>
  </SHIPMENT>
</DHL-SHIPMENT-IMPORT>

With a join, every item becomes its own SHIPMENT element. To group several items under one shipment, use a txml column.

Repeated child elements

Return the child elements as XML from a subquery and declare the column with type="txml". The value is inserted as-is, without an element named after the column.

XML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
<srs label="DHL-SHIPMENT-IMPORT">
  <def>
    <itm name="SHIPMENT">
      select s.id as ID,
             (select i.description as INV_DESCRIPTION,
                     i.quantity    as QUANTITY
                from shipment_item i
               where i.shipment_id = s.id
                 for xml path('INV_ITEM'), type) as ITEMS
      from shipment s
    </itm>
    <itm model="column" name="ITEMS" type="txml"/>
  </def>
</srs>
XML
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
<?xml version="1.0" encoding="utf-8"?>
<DHL-SHIPMENT-IMPORT>
  <SHIPMENT>
    <ID>2</ID>
    <INV_ITEM>
      <INV_DESCRIPTION>Women's dress made of cotton</INV_DESCRIPTION>
      <QUANTITY>1</QUANTITY>
    </INV_ITEM>
    <INV_ITEM>
      <INV_DESCRIPTION>Men's shirt</INV_DESCRIPTION>
      <QUANTITY>2</QUANTITY>
    </INV_ITEM>
  </SHIPMENT>
</DHL-SHIPMENT-IMPORT>

If a txml value is not valid XML, it is written as escaped text inside an element named after the column, so the document stays valid.

Multiple commands

Each command adds its rows to the same root element, in command order. Use separate commands to mix different element types in one file, for example a header and its lines.

Restrict the format

To allow only the XML export for an SRS, add a target:

XML
1
<itm model="target" name="xml" label="XML"/>

See target.