How to export Lotus Notes documents (both parent and response forms) to XML

Question

Anybody here knows how to export the parent and response forms to xml file? I got this code wherein it gets all the objects in the parent form and exports it to xml but it doesn't access the response records.

What I will be needing is to export records from Lotus Notes to xml file where I can choose which fields should be included from both Parent and Response forms.

Option Public
Option Declare
%INCLUDE "lsconst.lss" 


Sub Initialize

'This function creates XML files from Notes documents. The name of each XML file is the RepID of the database,
'plus the NoteID of the document. Therfore, we can find the XML file later for each doc.

'Constants
Const XML_FILE_FIELD = "XmlDocRenderFile"
Const XML_OUTPUT_DIR = "C:\Users\Administrator\Documents\Archive\"
Const XML_OUTPUT_ROOT1 = "Rep_"
Const XML_OUTPUT_ROOT2 = "_Note_"
Const XML_OUTPUT_SUFFIX = ".xml"
Const ERR_GENERAL = 1001

'Variables
Dim Sess As NotesSession
Dim Stream As NotesStream
Dim Exporter As NotesDXLExporter
Dim ThisDb As NotesDatabase
Dim SelectedDocs As NotesDocumentCollection
Dim OneDoc As NotesDocument
Dim XmlFilePath As String, NoteID As String, RepID As String

'Set up generic error handler.
On Error Goto ErrorReturn

'Get a Notes session, which we will use throughout this code. 
Set Sess = New NotesSession

 'Get the current database and its replica ID.
Set ThisDb = Sess.CurrentDatabase
RepID = ThisDb.ReplicaID

'Get the collection of documents that were selected by the user when this agent is invoked.
Set SelectedDocs = ThisDb.UnprocessedDocuments

'Create an XML exporter tool.
Set Exporter = Sess.CreateDxlExporter

 'Create an output stream that will receive XML. 
Set Stream = Sess.CreateStream

'Attach the stream as the output of the XML exporter.
Call Exporter.SetOutput (Stream)

'Create a loop that will process all the selected documents.
Set OneDoc = SelectedDocs.GetFirstDocument
While Not OneDoc Is Nothing


    'Get the Note ID of this document
    NoteID = OneDoc.NoteID

    'Make this document the input to the XML exporter.
    Call Exporter.SetInput (OneDoc)

    'Create the name of the XML output file.
    XmlFilePath = XML_OUTPUT_DIR + XML_OUTPUT_ROOT1 + RepID + XML_OUTPUT_ROOT2 + NoteID+ XML_OUTPUT_SUFFIX

    'Associate the XML output stream with the output file.
    Call Stream.Open(XmlFilePath)

    'Translate the doc into XML.
    Call Exporter.Process

    'Close the output file.
    Call Stream.Close

    'Write the name of the XML file into the document.
    Call OneDoc.ReplaceItemValue (XML_FILE_FIELD, XmlFilePath)

    'Save this document to svae our changes.
    Call OneDoc.Save(True, True, False)  

      'Get the next selected document.
  NextDoc:
    Set OneDoc = SelectedDocs.GetNextDocument(OneDoc)

  'End of loop on all selected documents.
  Wend

  NormalReturn:
  Exit Sub

  ErrorReturn:
   Msgbox "Problem.  Error message is: " & Error$, MB_ICONSTOP, "Error"
   Resume ErrorReturn2
  ErrorReturn2:
   Exit Sub

  End Sub

Anyone willing to help will be much appreciated. Thank you!

Answer 1

If you want to export a document and all of its descendants, I recommend making a subroutine that, given a document and a collection, adds the document's children to the collection and then loops through each of it's children and calls said function recursively... or if they aren't parent/response documents, whatever other logic that says "here's the starter document, add related documents." Then you can export the collection.

There isn't a way to get only specific fields from the exporter.

However, if you learn XSL, you can do quite a bit.

Your needs might be completely different from mine, but my goal was to take a database of Company, Product, and Factory documents and get a data structure like:

    <ExportGrouped daddy="JSmart523">
        <COMPANY      CREATED_TIMESTAMP="somedate" LAST_UPDATE_TIMESTAMP="somedate" DOMINOUNID="12345678901234567890123456789012"
                        COMPANYNAME="Acme R Us" OLD_SYSTEM_ID="42" OTHERATTRIBUTES="...">
            <PRODUCT  CREATED_TIMESTAMP="somedate" LAST_UPDATE_TIMESTAMP="somedate" DOMINOUNID="12345678901234567890123456789012"
                        PRODUCTNAME="..." OTHERATTRIBUTES="..."/>
            <PRODUCT  CREATED_TIMESTAMP="somedate" LAST_UPDATE_TIMESTAMP="somedate" DOMINOUNID="12345678901234567890123456789012"
                        PRODUCTNAME="..." OTHERATTRIBUTES="..."/>
            <FACILITY CREATED_TIMESTAMP="somedate" LAST_UPDATE_TIMESTAMP="somedate" DOMINOUNID="12345678901234567890123456789012"
                        NAME="..." OTHERATTRIBUTES="..."/>
            <FACILITY CREATED_TIMESTAMP="somedate" LAST_UPDATE_TIMESTAMP="somedate" DOMINOUNID="12345678901234567890123456789012"
                        NAME="..." OTHERATTRIBUTES="..."/>
        </COMPANY>
    </ExportGrouped>

which meant one node per document, field names as attributes (with exceptions I've omitted), and having response documents' nodes within their parent documents' nodes.

I tried to do it in just one XSL file but then every main document required a second pass through the database to find the related documents, which meant that the more data I had, the length of time it took to process the XSL grew exponentially! Instead I broke it up into two xsl files: Pass 1 got the data I wanted, then pass 2 organized it in the order I wanted.

However, field names aren't case sensitive in Lotus Notes and that smacked me in the head, so, in addition to pass 1 and pass 2, ...

Pass 0: Translate Item Names to lower case

While field names are not case sensitive in Notes, XSL is case sensitive. For any given document, field name FieldName might be fieldname or FIELDNAME. At first I tried to handle this within my first XSL file but it made the code messy and slow, so I added a "zeroth" pass to my process. I also took the opportunity to remove nodes I didn't want, such as config documents or any bitmaps since the destination wasn't going to store them. The input XML file was always the entire Notes database (default NotesDXLExporter options so design wasn't included) or, when just debugging something, a single document.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:dxl="http://www.lotus.com/dxl" version="1.0">
  <xsl:output indent="no"/>
  <!-- Thanks to http://stackoverflow.com/questions/586231/how-can-i-convert-a-string-to-upper-or-lower-case-with-xslt for this method of using translate() to convert to lower case within xsl 1.0 -->
  <xsl:variable name="smallCase" select="'abcdefghijklmnopqrstuvwxyz'"/>
  <xsl:variable name="upperCase" select="'ABCDEFGHIJKLMNOPQRSTUVWXYZ'"/>
  <xsl:template match="*|text()|@*">
    <xsl:copy>
      <xsl:apply-templates select="*|text()|@*"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="dxl:item/@name">
    <xsl:attribute name="name">
      <xsl:value-of select="translate(.,$upperCase,$smallCase)"/>
    </xsl:attribute>
  </xsl:template>

  <!-- easily identifiable nodes we will not need -->
  <xsl:template match="dxl:document[@form='Configuration']"/>
  <xsl:template match="dxl:document[@form='Counter']"/>
  <xsl:template match="dxl:document[@form='Preference']"/>
  <xsl:template match="dxl:document[@form='Void']"/>

  <xsl:template match="dxl:databaseinfo|dxl:updatedby|dxl:revisions|dxl:notesbitmap|dxl:compositedata|dxl:launchsettings|dxl:item[dxl:rawitemdata]|dxl:embeddedcontrol"/>
</xsl:stylesheet>

Pass 2: All But Structure

My second file did most of the work, creating the COMPANY, PRODUCT, AND FACILITY nodes, but not rearranging them - they were still siblings, direct children of the root node. (Caveat: I didn't have to worry about responses to responses. Your code might differ.)

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:dxl="http://www.lotus.com/dxl" version="1.0" exclude-result-prefixes="dxl">
  <xsl:output indent="no"/>

  <!-- / -->
  <xsl:template match="/">
    <xsl:element name="ExportUngrouped" namespace="">
      <xsl:apply-templates select="*"/>
    </xsl:element>
  </xsl:template>

 <!--
    /*
   Uncaught root element!
   If this template matches then we have a problem. The root node this XSL is meant to process is /dxl:database or /dxl:document
  -->
  <xsl:template match="/*" priority="0">
    <xsl:element name="badelement">
      <xsl:attribute name="Expected">/database or /document</xsl:attribute>
      <xsl:attribute name="ExpectedNamespace">http://www.lotus.com/dxl</xsl:attribute>
      <xsl:attribute name="ActualNodeName">
        <xsl:call-template name="XPathOfCurrentNode"/>
      </xsl:attribute>
      <xsl:attribute name="ActualNamespace">
        <xsl:value-of select="namespace-uri()"/>
      </xsl:attribute>
      <xsl:copy>
        <xsl:copy-of select="@*"/>
      </xsl:copy>
    </xsl:element>
  </xsl:template>

  <!-- dxl:database -->
  <xsl:template match="dxl:database">
    <xsl:attribute name="replicaid">
      <xsl:value-of select="@replicaid"/>
    </xsl:attribute>
    <!-- other stuff I wanted at the root database node -->
      <!--This is just an example, btw! -->
      <xsl:variable name="daddy" select="dxl:document[@form='FAQ']/dxl:item[@name='DocTitle'][. = 'WhosYourDaddy'][1]/../dxl:item[@name='DaddyName']"/>
    <xsl:if test="$daddy">
      <xsl:attribute name="daddy"><xsl:value-of select="$daddy"/></xsl:attribute>
    </xsl:if>
    <xsl:apply-templates select="dxl:document"/>
  </xsl:template>

  <!-- dxl:document nodes I want. I recommend making a spreadsheet that documents the forms you are looking for and generates the desired XSL. In my real XSL file, this wasn't pretty and indented, it was a copy & paste of one line per xsl:template tag. -->
  <xsl:template match="dxl:document[@form='Company']">
    <xsl:element name="COMPANY">
        <xsl:call-template name="DocumentElementContents"/>
    </xsl:element>
  </xsl:template>

  <xsl:template match="dxl:document[@form='Product']">
    <xsl:element name="PRODUCT">
        <xsl:call-template name="DocumentElementContents"/>
    </xsl:element>
  </xsl:template>

  <xsl:template match="dxl:document[@form='Factory']">
    <xsl:element name="FACILITY">
        <xsl:call-template name="DocumentElementContents"/>
    </xsl:element>
  </xsl:template>

  <!-- dxl:document nodes that we somehow missed -->
  <xsl:template match="dxl:document">
    <xsl:element name="uncaughtdocument">
      <xsl:attribute name="form">
        <xsl:value-of select="@form"/>
      </xsl:attribute>
      <xsl:call-template name="DocumentElementContents"/>
    </xsl:element>
  </xsl:template>

  <!--
    *************************************************
    Templates for AttributeItems mode
    Called by named template DocumentElementContents 
    AttributeItems mode adds attributes to the element that is created for a given dxl:document.
    Where possible, data is copied here.
    *************************************************
  -->

  <!-- AttributeItems dxl:noteinfo -->
  <xsl:template mode="AttributeItems" match="dxl:noteinfo">
    <xsl:attribute name="CREATED_TIMESTAMP">
      <xsl:value-of select="dxl:created/dxl:datetime"/>
    </xsl:attribute>
    <xsl:attribute name="LAST_UPDATE_TIMESTAMP">
      <xsl:value-of select="dxl:modified/dxl:datetime"/>
    </xsl:attribute>
    <xsl:attribute name="DOMINOUNID">
      <xsl:value-of select="@unid"/>
    </xsl:attribute>
  </xsl:template>

  <!--

    Called by DocumentElementContents.
    Describes what to do with an item when we are looking for items to include as attributes.
    I recommend making an Excel spreadsheet to generate this part because then your project documentation will be your code generator.
  -->
  <xsl:template mode="AttributeItems" match="dxl:document[@form='Company']/dxl:item[@name='CompanyName']">
    <xsl:attribute name="COMPANYNAME">
      <xsl:call-template name="AttributeSafeValueOfItem"/>
    </xsl:attribute>
  </xsl:template>

  <xsl:template mode="AttributeItems" match="dxl:document[@form='Company']/dxl:item[@name='CompanyID']">
    <xsl:attribute name="OLD_SYSTEM_ID">
      <xsl:call-template name="AttributeSafeValueOfItem"/>
    </xsl:attribute>
  </xsl:template>

  <!-- etc, etc -->

  <!-- If an item is not caught above while we are in AttributeItems mode, ignore it. -->
  <xsl:template mode="AttributeItems" match="dxl:item"/>

  <!--
    *************************************************
    Templates for ElementItems mode
    *************************************************
  -->

  <!--
    Called by DocumentElementContents.
    Describes what to do with an item when we are looking through the items to see what element nodes should be added
  -->
  <!-- Your code goes here for each element node to be created within the node created for that document. I omitted this part because it didn't help answer (my interpretation of) your question. -->

  <!-- If an item is not caught above while we are in ElementItems mode, ignore it. -->
  <xsl:template mode="ElementItems" match="dxl:item"/>

  <!--
    "DocumentElementContents"
    generic code to be called for each NotesDocument we are exporting.
  -->
  <xsl:template name="DocumentElementContents">
    <xsl:choose>
      <xsl:when test="@parent">
        <xsl:attribute name="MainDoc">false</xsl:attribute>
        <xsl:attribute name="ParentUNID">
          <xsl:value-of select="@parent"/>
        </xsl:attribute>
      </xsl:when>
      <xsl:otherwise>
        <xsl:attribute name="MainDoc">true</xsl:attribute>
      </xsl:otherwise>
    </xsl:choose>
    <xsl:apply-templates mode="AttributeItems" select="dxl:item|dxl:noteinfo"/>
    <xsl:apply-templates mode="ElementItems" select="dxl:item"/>
  </xsl:template>

  <!-- 
    AttributeSafeValueOfItem
    Outputs the values of the item, delimited by commas if there are multiple values.
    Current node expected to be an item, but will work with any node where we want to export the content of all descendants that do not have the string 'list' in them. (e.g. item/textlist will be excluded but values within item/textlist/test will be exported)
  -->
  <xsl:template name="AttributeSafeValueOfItem">
    <xsl:for-each select=".//*[not(contains(local-name(),'list'))]">
      <xsl:value-of select="."/>
      <xsl:if test="position() != last()">, </xsl:if>
    </xsl:for-each>
  </xsl:template>

  <!--
    XPathOfCurrentNode
    For debugging. Outputs a string that is the XPath of the current node.
  -->
  <xsl:template name="XPathOfCurrentNode">
    <xsl:for-each select="ancestor-or-self::*">
      <xsl:call-template name="XPathOfCurrentNode_NodeLevel"/>
    </xsl:for-each>
  </xsl:template>

  <!--
    XPathOfCurrentNode_NodeLevel
    For debugging. Called by XPathOfCurrentNode for each ancestor-or-self::.
  -->
  <xsl:template name="XPathOfCurrentNode_NodeLevel">
    <xsl:variable name="precedingCount" select="count(preceding-sibling::*[name(.)=name(current())])"/>
    <xsl:text>/</xsl:text>
    <!--<xsl:value-of select="namespace-uri()"/>-->
    <xsl:value-of select="name(.)"/>
    <xsl:if test="$precedingCount or count(following-sibling::*[name(.)=name(current())])">
      <xsl:text>[</xsl:text>
      <xsl:value-of select="$precedingCount+1"/>
      <xsl:text>]</xsl:text>
    </xsl:if>
  </xsl:template>
</xsl:stylesheet>

Note the exclude-result-prefixes attribute in the xsl:stylesheet tag so that dxl: isn't peppered throughout the output.

Pass 3: Reorg!

At this point I had everything I needed except it was in the wrong order. Your final structure may differ, but for me, all I needed to do at this point was to move each child node within the parent node so that each COMPANY node contained each relevant PRODUCT and FACILITY node.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<!--
After the previous xsl file has processed the code, we have all we need but it's not organized. It's in the order of documents received.
We want to group all of the nodes related to a case within the main doc node itself.
-->
  <xsl:output indent="yes"/>
  <xsl:key name="kSupportingDocsByParent" match="/ExportUngrouped/*[@ParentUNID and @MainDoc='false']" use="@ParentUNID"/>

  <xsl:template match="/ExportUngrouped" priority="1">
    <ExportGrouped>
      <xsl:copy-of select="@*"/>
      <xsl:apply-templates select="*[@MainDoc='true']">
        <xsl:sort select="@ParentUNID"/>
      </xsl:apply-templates>
    </ExportGrouped>
  </xsl:template>

  <xsl:template match="/ExportUngrouped/*[@MainDoc='true']" priority="1">
    <xsl:copy>
      <xsl:apply-templates select="@*|*"/>
      <xsl:for-each select="key('kSupportingDocsByParent',@DOMINOUNID)">
        <xsl:copy>
          <xsl:apply-templates select="node()|@*"/>
        </xsl:copy>
      </xsl:for-each>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="@MainDoc|@OtherAttributesTheFinalOutputShouldNotHave" priority="1"/>

  <xsl:template match="/ExportUngrouped/*/node()|@*" priority="0">
    <xsl:copy-of select="."/>
  </xsl:template>
</xsl:stylesheet>

Done!

This is definitely a beefier answer than you had probably wanted. I hope it helps you or someone else!

Answer 2

You need to get the response documents for each parent document and loop through them, exporting them the same way. You may even have response-to-response documents, so you may need to write a recursive function.