Transforming XML->XML using DOM 1

[MatsHulten]

Hi! Here's what I'm trying to do:
=====================================================================
Convert
<rs>
<z:row MenuItem=&quot;19&quot; MenuRefItem=&quot;2194&quot; MenuName=&quot;User administration&quot; MenuLink=&quot;&quot; />
<z:row MenuItem=&quot;20&quot; MenuRefItem=&quot;2194&quot; MenuName=&quot;Pictures and background&quot; MenuLink=&quot;&quot; />
</rs>

Into this
<Menu>
<MenuItem ID=&quot;19&quot; Parent=&quot;2194&quot; MenuLink=&quot;&quot;>User Administration</MenuItem>
<MenuItem ID=&quot;20&quot; Parent=&quot;2194&quot; MenuLink=&quot;&quot;>Pictures and background</MenuItem>
</Menu>

The first XML-string is given when you save an ADO recordset to XML, the second is what I want to return from my COM-object.

Can someone help me get started?
-Mats Hulten

 

[MatsHulten]

Anyone?

-Mats

 

[JJR]

Set a reference to Microsoft XML version 2.0

In the following example I assume you allready have the xml from the recordset in a string called m_sInput. The output xml will be written to a variable m_sOutput.

I've tested it and it worked.

Dim oXMLRSDocument As MSXML.DOMDocument
Dim oXMLOutputDocument As MSXML.DOMDocument
Dim oXMLRSDataNode As MSXML.IXMLDOMNode
Dim oXMLRSNode As MSXML.IXMLDOMElement
Dim oXMLOutputNode As MSXML.IXMLDOMElement
Dim lNodeCount As Long
Dim lNC As Long
Dim lAttributesCount
Dim lAC As Long

Set oXMLOutputDocument = New MSXML.DOMDocument
Set oXMLRSDocument = New MSXML.DOMDocument

'Load the xml string created by ADODB
oXMLRSDocument.loadXML m_sInput

'Now select the data node
'In your data this node is called "rs", but when I
'wrote a recordset to xml it was called rs:data
Set oXMLRSDataNode = oXMLRSDocument.documentElement.selectSingleNode("rs:data")

'Get the recordcount
lNodeCount = oXMLRSDataNode.childNodes.length - 1

'Now iterate through the childnodes, select the attributes 
'and create the new xml

'First create the top-level node for the outputxml (there can be only one... 
'(where did I here that phrase before...)
   Set oXMLOutputDocument.documentElement = oXMLOutputDocument.createElement("Menu")

For lNC = 0 To lNodeCount
   'Selects the elements (records)
   Set oXMLRSNode = oXMLRSDataNode.childNodes(lNC)
   lAttributesCount = oXMLRSNode.Attributes.length - 1
   For lAC = 0 To lAttributesCount
      Select Case lAC
         Case 0
            'The first attribute contains the element name
            Set oXMLOutputNode = oXMLOutputDocument.documentElement.appendChild(oXMLOutputDocument.createElement(oXMLRSNode.Attributes(lAC).nodeName))
            'Now create the first attribute
            oXMLOutputNode.setAttribute "ID", oXMLRSNode.Attributes(lAC).nodeValue
         Case 1
            'menurefitem
            oXMLOutputNode.setAttribute "Parent", oXMLRSNode.Attributes(lAC).nodeValue
         Case 2
            Call oXMLOutputNode.appendChild(oXMLOutputNode.ownerDocument.createTextNode(oXMLRSNode.Attributes(lAC).nodeValue))
         Case 3
            'Menulink
            oXMLOutputNode.setAttribute oXMLRSNode.Attributes(lAC).nodeName, oXMLRSNode.Attributes(lAC).nodeValue
      End Select
   Next lAC
Next lNC

m_sOutput = oXMLOutputDocument.xml

Set oXMLOutputNode = Nothing
Set oXMLRSDocument = Nothing
Set oXMLOutputDocument = Nothing
Set oXMLRSDataNode = Nothing
Set oXMLRSNode = Nothing

End Sub

I hope this will be helpfull

Jordi Reineman

 

[MatsHulten]

Thanx a lot Jordi! I already knew that I could build the DOM from scratch using the data in the old file, but I was actually looking for a more general way to do it, IE XSLT. (I know I was a bit vague about that in the question...

Anyway, I came up with a solution myself...

File: Rs2Menu.xsl
=====================================================================
<?xml version=&quot;1.0&quot;?>
<xsl:stylesheet xmlns:xsl=&quot;[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform&quot;[/URL] version=&quot;1.0&quot; xmlns:s=&quot;uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882&quot; xmlns:dt=&quot;uuid:C2F41010-65B3-11d1-A29F-00AA00C14882&quot; xmlns:rs=&quot;urn:schemas-microsoft-com:rowset&quot; xmlns:z=&quot;#RowsetSchema&quot;>
<xsl:output method=&quot;xml&quot;/>
<xsl:template match=&quot;/&quot;>

<xsl:element name=&quot;Menu&quot;>
   <xsl:for-each select=&quot;//rs:data/z:row&quot;>
      <xsl:element name=&quot;MenuItem&quot;>
         <xsl:attribute name=&quot;ID&quot;><xsl:value-of select=&quot;@MenuItem&quot;/></xsl:attribute>
         <xsl:attribute name=&quot;Parent&quot;><xsl:value-of select=&quot;@MenuRefItem&quot;/></xsl:attribute>
         <xsl:attribute name=&quot;MenuLink&quot;><xsl:value-of select=&quot;@MenuLink&quot;/></xsl:attribute>
         <xsl:value-of select=&quot;@MenuName&quot;/>
      </xsl:element>
   </xsl:for-each>
</xsl:element>

</xsl:template>
</xsl:stylesheet>

Code:
=====================================================================
 Const path As String = &quot;C:\XSLT&quot;
 
 Dim Source As New Msxml2.DOMDocument
 Dim stylesheet As New Msxml2.DOMDocument
 Dim result As New Msxml2.DOMDocument
 
 ' Load the saved recordset.
 Source.async = False
 Source.Load path & &quot;\menu.xml&quot;

 ' Load style sheet.
 stylesheet.async = False
 stylesheet.validateOnParse = False
 stylesheet.Load path & &quot;\Rs2Menu.xsl&quot;

 ' Set up the resulting document.
 result.async = False
 result.validateOnParse = True

 ' Parse results into a result DOM Document.
 Source.transformNodeToObject stylesheet, result
 result.Save path & &quot;\result.xml&quot;