CODEWITHSUNDEEP
xmlvbams-access
wayfarer
wayfarer

Reputation: 790

MS Access VBA handle line feed characters from XML

I'm updating a small xml file. The input has line breaks within an attribute, as shown here:

 <foo>
  <add name="bar"
    attr1="aaaa;
    bbbbb;
    cccc"/>
 </foo>

The output; however, looks like this (with line feeds)

<foo>
    <add name="bar" attr1="aaaa;&#xA;   bbbbb;&#xA; cccc"/>
</foo>

I've tried several ways to locate and remove the line feeds.

A workaround is to not have the line feeds in the input. However, I'd like to leave the line feeds in the input, for readability. I'd prefer to leave them in the output also, however, that's not necessary.

How might I control the line feeds in the output, either by having them be "real" line feeds, or deleting them?

Below is the test routine which replicates the problem; it does no data update, like the real routine does. The real routine works fine except for the line feeds.

Sub Test()
    On Error GoTo ErrTrap:
    Dim XMLDoc As New MSXML2.DOMDocument60
    
    Dim wrkInFolder As String
    Dim wrkInFullPath As String
    Dim wrkOutFolder As String
    Dim wrkOutFullPath As String
                  
    Dim wrkCSNode As IXMLDOMNode
    Dim wrkDataCS As IXMLDOMNode
    Dim wrkAttr As IXMLDOMAttribute
    
    Dim wrkInVal As String
    Dim wrkOutVal As String
    
    '---- set up the I/O Paths
    wrkInFolder = "D:\mypath\"
    wrkInFullPath = wrkInFolder & "xmltest.config"
    wrkOutFolder = wrkInFolder
    wrkOutFullPath = wrkOutFolder & "xmltest-out.config"
    
    '---- get the doc
    XMLDoc.Load (wrkInFullPath)

    '--- get conn str node
    Set wrkCSNode = XMLDoc.SelectSingleNode("foo")
    '--- first child node (base 0)
     Set wrkDataCS = wrkCSNode.ChildNodes(0)
     '--- first attribute (base 0)
     Set wrkAttr = wrkDataCS.Attributes(0)
     
     '---- get the value of the attribute
     wrkInVal = wrkAttr.NodeValue
    
     wrkOutVal = Replace(wrkInVal, "&#xA;", "")
     p1 = InStr(wrkOutVal, "&#xA;")
     If p1 <> 0 Then
          MsgBox ("Found line feed")
     End If
         
     wrkAttr.NodeValue = wrkOutVal
    
     XMLDoc.Save (wrkOutFullPath)
  
     On Error GoTo 0
     Exit Sub

ErrTrap:
    Dim exmsg As String
    exmsg = Err.Description
    Dim wrkStop As String
    wrkStop = ""
    On Error GoTo 0

End Sub

Upvotes: 0

Views: 203

Answers (1)

Parfait
Parfait

Reputation: 107687

What you are referring to is actually whitespace, not linefeeds. You can restyle the XML using XSLT. Then, the whitespace within an attribute value an be handled with XSLT's normalize-space() function.

As background, XSLT is a special-purpose declarative language to transform/style XML to any restructured format. In VBA (like many other programming languages), you can load XSLT externally (just like your XML file) or as an inline string. Below is the external file route:

XSLT (almost an identity transform to clone structure but removing white space in attribute value)

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
<xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>

<xsl:template match="node()"> 
     <xsl:copy> 
         <xsl:apply-templates select="@* | node()"/> 
     </xsl:copy> 
</xsl:template>

<xsl:template match="@*"> 
     <xsl:attribute name="{name()}"> 
         <xsl:value-of select="normalize-space()"/> 
     </xsl:attribute> 
</xsl:template> 

</xsl:stylesheet>

VBA (loads both .config xml and .xsl file in wrkInFolder and transforms into outputted .config file)

Dim XMLDoc As New MSXML2.DOMDocument60
Dim XSLDoc As New MSXML2.DOMDocument60
Dim OutputXML As New MSXML2.DOMDocument60

Dim wrkInFolder As String, wrkInFullPath As String 
Dim wrkOutFolder As String, wrkOutFullPath As String

'---- Set up the I/O Paths 
wrkInFolder = "D:\mypath\" 
wrkInFullPath = wrkInFolder & "xmltest.config"
wrkOutFolder = wrkInFolder 
wrkOutFullPath = wrkOutFolder & "xmltest-out.config"

'---- Load xml data
XMLDoc.async = False
XMLDoc.Load wrkInFullPath

'---- Load xslt stylesheet
XSLDoc.async = False
XSLDoc.Load wrkInFolder & "xslfile.xsl"     ' SAVE ABOVE EXTERNALLY IN .xsl FORMAT

'---- Do the transform
XMLDoc.transformNodeToObject XSLDoc, OutputXML
OutputXML.Save wrkOutFullPath

Upvotes: 1

Related Questions