Stack Exchange
Powershell - Exporting XML without closing tags - OFX format SGML?
I'm trying to create an OFX file for import into Sage300 using powershell. This is to provide automated bank reconciliation. Not too familiar with OFX structure but it appears to be SGML v1.02.
Input will be from a SQL query. The target OFX structure looks like this:
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:
NEWFILEUID:

<OFX>
  <SIGNONMSGSRSV1>
    <SONRS>
      <STATUS>
        <CODE>0
        <SEVERITY>INFO
        <MESSAGE>OK
      </STATUS>
      <DTSERVER>2007126895412
      <USERKEY>20075698742365
      <LANGUAGE>ENG
      <INTU.BID>00004
    </SONRS>
  </SIGNONMSGSRSV1>
  <CREDITCARDMSGSRSV1>
    <CCSTMTTRNRS>
      <TRNUID>12345678
      <STATUS>
        <CODE>0
        <SEVERITY>INFO
        <MESSAGE>OK
      </STATUS>
      <CCSTMTRS>
        <CURDEF>GBP
        <CCACCTFROM>
           <ACCTID>12345678
        </CCACCTFROM>
        <BANKTRANLIST>
          <DTSTART>20191201
          <DTEND>20201231
          <STMTTRN>
            <TRNTYPE>CREDIT
            <DTPOSTED>20200408
            <TRNAMT>11.98
            <REFNUM>000173160-0000810
            <FITID>000173160-0000810
            <NAME>FBAAMZUK
            <MEMO>CHK
            <CHECKNUM>000173160-0000810
          </STMTTRN>
          <STMTTRN>
            <TRNTYPE>CREDIT
            <DTPOSTED>20200408
            <TRNAMT>6.99
            <REFNUM>000173160-0000968
            <FITID>000173160-0000968
            <NAME>FBAAMZUK
            <MEMO>CHK
            <CHECKNUM>000173160-0000968
          </STMTTRN>
          <STMTTRN>
            <TRNTYPE>CREDIT
            <DTPOSTED>20200408
            <TRNAMT>50.00
            <REFNUM>0123-0123
            <FITID>0123-0123
            <NAME>FBAAMZUK
            <MEMO>TEST
          </STMTTRN>
        </BANKTRANLIST>
        <LEDGERBAL>
          <xBALAMT>0.00
          <BALAMT>18.97
          <DTASOF>20200414
        </LEDGERBAL>
        <AVAILBAL>
          <xBALAMT>0.00
          <BALAMT>-4323.81
          <DTASOF>20200414
        </AVAILBAL>
      </CCSTMTRS>
    </CCSTMTTRNRS>
  </CREDITCARDMSGSRSV1>
</OFX>

As you can see there are some elements with closing tags and some without. The above format has been tested on import and works.
So far I have been using the XmlTextWriter to generate a properly formatted XML doc.
This code works fine. However, as it's properly formatted it closes every element.
Is there a way to export this without closing tags? Or am I going to need to re-import the XML and do some string replacement? Any advice greatly appreciated.
    $Path = "C:\scripts\TEST.xml"

#SQL Connection
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = “Server=xxxxxx;Integrated Security=true;Initial Catalog=master”
$sqlConn.Open()

#SQL Command
$sqlcmd = $sqlConn.CreateCommand()
$sqlcmd = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.Connection = $sqlConn

#SQL query
$query = “SELECT 
payments.[order-id],
payments.[settlement-id],
replace(convert (date, payments.[posted-date],103),'-','') AS [posted-date],
payments.[transaction-type],
payments.[marketplace-name],

SUM (CAST(payments.amount AS money)) AS amount

FROM xxxxxx.dbo.PAYMENTS
where [amount-type] <> 'ItemFees' AND ([transaction-type] = 'Order' OR [transaction-type] = 'Refund')

GROUP BY [order-id], [settlement-id],[posted-date],[transaction-type],[marketplace-name]”

$sqlcmd.CommandText = $query

#SQL adapter
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd

#Get data and fill data set
$data = New-Object System.Data.DataSet
$adp.Fill($data) | Out-Null
$dataSet = $data.Tables[0]
$sqlConn.Close()

# get an XMLTextWriter to create the XML
$XmlWriter = New-Object System.XMl.XmlTextWriter($Path,$Null)

# XML formatting
$xmlWriter.Formatting = 'Indented'
$xmlWriter.Indentation = 1
$XmlWriter.IndentChar = "`t"

# write the header
$xmlWriter.WriteStartDocument()

# set XSL statements
$xmlWriter.WriteProcessingInstruction("xml-stylesheet", "type='text/xsl' href='style.xsl'")

# create root element "OFX" and add some attributes to it

$xmlWriter.WriteStartElement('OFX')

$xmlWriter.WriteStartElement('SIGNONMSGSRSV1')

$xmlWriter.WriteStartElement('SONRS')

$xmlWriter.WriteStartElement('STATUS')
$XmlWriter.WriteElementString('CODE', '0')
$XmlWriter.WriteElementString('SEVERITY', 'INFO')
$XmlWriter.WriteElementString('MESSAGE', 'OK')
$xmlWriter.WriteEndElement()

$XmlWriter.WriteElementString('DTSERVER', '2007126895412')
$XmlWriter.WriteElementString('USERKEY', '20075698742365')
$XmlWriter.WriteElementString('LANGUAGE', 'ENG')
$XmlWriter.WriteElementString('INTU.BID', '00004')

$xmlWriter.WriteEndElement()

$xmlWriter.WriteEndElement()

$xmlWriter.WriteStartElement('CREDITCARDMSGSRSV1')

$xmlWriter.WriteStartElement('CCSTMTTRNRS')
$XmlWriter.WriteElementString('TRNUID', '12345678')

$xmlWriter.WriteStartElement('STATUS')
$XmlWriter.WriteElementString('CODE', '0')
$XmlWriter.WriteElementString('SEVERITY', 'INFO')
$XmlWriter.WriteElementString('MESSAGE', 'OK')
$xmlWriter.WriteEndElement()

$xmlWriter.WriteStartElement('CCSTMTRS')
$XmlWriter.WriteElementString('CURDEF', 'GBP')

$xmlWriter.WriteStartElement('CCACCTFROM')
$XmlWriter.WriteElementString('ACCTID', '12345678')
$xmlWriter.WriteEndElement()

$xmlWriter.WriteStartElement('BANKTRANLIST')
$XmlWriter.WriteElementString('DTSTART', '20191201')
$XmlWriter.WriteElementString('DTEND', '20201231')

foreach ($row in $dataSet)
{ 

$order_id = $row.'order-id'
$amt = $row.amount
$dateP = $row.'posted-date'

$xmlWriter.WriteStartElement('STMTTRN')
$XmlWriter.WriteElementString('TRNTYPE', 'CREDIT')
$XmlWriter.WriteElementString('DTPOSTED', $dateP)
$XmlWriter.WriteElementString('TRNAMT', $amt)
$XmlWriter.WriteElementString('REFNUM', $order_id)
$XmlWriter.WriteElementString('FITID', '000173160-0000810')
$XmlWriter.WriteElementString('NAME', 'FBAAMZUK')
$XmlWriter.WriteElementString('MEMO', 'CHK')
$XmlWriter.WriteElementString('CHECKNUM', '000173160-0000810')
$xmlWriter.WriteEndElement()
}

$xmlWriter.WriteEndElement()

$xmlWriter.WriteStartElement('LEDGERBAL')
$XmlWriter.WriteElementString('xBALAMT', '0.00')
$XmlWriter.WriteElementString('BALAMT', '18.97')
$XmlWriter.WriteElementString('DTASOF', '20200414')
$xmlWriter.WriteEndElement()

$xmlWriter.WriteStartElement('AVAILBAL')
$XmlWriter.WriteElementString('xBALAMT', '0.00')
$XmlWriter.WriteElementString('BALAMT', '-4323.81')
$XmlWriter.WriteElementString('DTASOF', '20200414')
$xmlWriter.WriteEndElement()


$xmlWriter.WriteEndElement()

$xmlWriter.WriteEndElement()

$xmlWriter.WriteEndElement()

$xmlWriter.WriteEndElement()

$xmlWriter.WriteEndDocument()
$xmlWriter.Flush()
$xmlWriter.Close()


Have you found a solution? I'm looking into the identical issue and so far haven't found any .net libraries that provide the ability to create OFX files.

What's wrong with using closing tags? The DTD makes closing tags optional, but that doesn't mean you cannot include them.

https://github.com/libofx/libofx/blob/9070c729c839a458a541f0f53d19899190e1d1e2/dtd/ofx160.dtd
