ASP Souce Code to read from an DB and write an XML file

[Almarton]

Hi all,

I am needing to learn how to read an MS Access DB
and output it to a XML file.

But I would like to have an output with a default type
just like what I get when I use the export to XML inside
Access, should output for a table be something of this kind(example table calle Element with 3 columns):

<?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?>
<dataroot xmlnsd=&quot;urn:schemas-microsoft-comfficedata&quot; xmlns:xsi=&quot;

http://www.w3.org/2000/10/XMLSchema-instance&quot;

xsi:noNamespaceSchemaLocation=&quot;Element.xsd&quot;>
<Element>
<Element_ref_no>11111-004BOSCH</Element_ref_no>
<Elem_Symb_Type>PUMP</Elem_Symb_Type>
<Element_Manufacturer>BOSCH</Element_Manufacturer>
</Element>
<Element>
<Element_ref_no>7363-CH</Element_ref_no>
<Elem_Symb_Type>ACTUATOR</Elem_Symb_Type>
<Element_Type>linear</Element_Type>
</Element>
</dataroot>


I have already some code but the output
(described below after the code) is of no
use because of the format... I think it
contains also schema...

Well if someone has the source the would yield
an output like above or know how to do it I would
be more than grateful it you could send it to me in
a reply or post here, I will grant it as a VERY
expertise help!

Thanks
Alex ( almarton@task.com.br )
Follows the (useless) code and the output I have:

<%
Dim objConn
Dim objRec

Set objConn = Server.CreateObject (&quot;ADODB.Connection&quot
Set objRec = Server.CreateObject (&quot;ADODB.Recordset&quot

objConn.Open &quot;DSN=FMMLProjects&quot;


'PROJECT TABLE
objRec.Open &quot;Project&quot;, objConn, 0, 1, 2

Response.Write &quot;<H3> PROJECT TABLE </H3><TABLE BORDER='1'>&quot;
Response.Write &quot;<TR bgcolor=&quot;&quot;#999999&quot;&quot;><TH>Project_Ref_No</TH><TH>Project_Title</TH><TH>PDate</TH>&quot; _
& &quot;<TH>Author</TH><TH>Company</TH><TH>Project_Description</TH>&quot;
While Not objRec.EOF
Response.Write &quot;<TR>&quot;
Response.Write &quot;<TD>&quot; & objRec(&quot;Project_Number&quot & &quot;</TD>&quot;
Response.Write &quot;<TD>&quot; & objRec(&quot;Project_Title&quot & &quot;</TD>&quot;
Response.Write &quot;<TD>&quot; & objRec(&quot;PDate&quot & &quot;</TD>&quot;
Response.Write &quot;<TD>&quot; & objRec(&quot;Author&quot & &quot;</TD>&quot;
Response.Write &quot;<TD>&quot; & objRec(&quot;Company&quot & &quot;</TD>&quot;
Response.Write &quot;<TD>&quot; & objRec(&quot;Project_Description&quot & &quot;</TD>&quot;
objRec.MoveNext
Response.Write &quot;</TR>&quot;
Wend
Response.Write &quot;</TABLE>&quot;
objRec.Close

objConn.Close
Set objRec = Nothing
Set objConn = Nothing

%>

*********************************************

AND THE OUTPUT:

**********************************************

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:AttributeType name='Element_ref_no' rs:number='1' rs:nullable='true' rs:write='true'>
<s:datatype dt:type='string' dt:maxLength='50'/>
</s:AttributeType>
<s:AttributeType name='Elem_Symb_Type' rs:number='2' rs:nullable='true' rs:write='true'>
<s:datatype dt:type='string' dt:maxLength='50'/>
</s:AttributeType>
<s:AttributeType name='Element_Manufacturer' rs:number='3' rs:nullable='true' rs:write='true'>
<s:datatype dt:type='string' dt:maxLength='50'/>
</s:AttributeType>
<s:AttributeType name='Element_Type' rs:number='4' rs:nullable='true' rs:write='true'>

<rs:data>
<z:row Element_ref_no='0111-004BOSCH' Elem_Symb_Type='PUMP' Element_Manufacturer='BOSCH' Element_Type='piston'
Flow='0.0007' Displacement='fixed' Element_OpProperty='air eletric'/>
<z:row Element_ref_no='0233-GF' Elem_Symb_Type='FILTER' Element_Manufacturer='GERF' Element_Type='plastic mesh'
Units='UBASE' Element_OpProperty='oil type determined by part number'/>
<z:row Element_ref_no='1000-CVBOSCH' Elem_Symb_Type='CONTROL_VALVE' Element_Manufacturer='BOSCH' Element_Type='by-pass'
Element_Subtype='right-left' Units='UBASE'/>
<z:row Element_ref_no='11111-004BOSCH' Elem_Symb_Type='PUMP' Element_Manufacturer='BOSCH' Element_Type='gear'
Units='UBASE' Flow='0.0005' Displacement='fixed' Element_OpProperty='eletric'/>
<z:row Element_ref_no='1637-CVBOSCH' Elem_Symb_Type='CONTROL_VALVE' Element_Manufacturer='BOSCH' Element_Type='relief'
Action='spring' Units='UBASE' Pressure='20000000' Element_OpProperty='Kg/m*s^2'/>
<z:row Element_ref_no='364673-DMV' Elem_Symb_Type='CONTROL_VALVE' Element_Manufacturer='SYSCO' Element_Type='directional'
Actuation1='air-spring' Actuation2='air-spring' Ports='4' Position='3' Units='UBASE' Element_OpProperty='normal closed'/>
<z:row Element_ref_no='7363-CH' Elem_Symb_Type='ACTUATOR' Element_Type='linear' Element_Subtype='double action'
Units='UBASE' Displacement='.9' DB='.159' DR='.1128' Element_OpProperty='oil'/>
<z:row Element_ref_no='9637-CVBOSCH' Elem_Symb_Type='CONTROL_VALVE' Element_Manufacturer='BOSCH' Element_Type='pressure'
Element_Subtype='ajustable' Units='UBASE' Pressure='20000000' Element_OpProperty='0 to 40MPa'/>
</rs:data>
</xml>


Alexandre @lmarton Marton
almarton@task.com.br

 

[CodeFish]

Hi Almarton,

Try:-

Public Function CreateXMLFromRS(ByVal rsRecordset As ADODB.Recordset, Optional bFormatXML As Boolean = True) As MSXML2.DOMDocument30
'----------------------------------------------------------
' Author: Codefish
'
' Date: 17/8/2001
'
' History:
'
' Purpose: - Creates XML DOM Object From ADO Recordset
'
' Notes: Variable Format Description
' -------------------------------------------
' ADORecordset ADO Recordset Source for XML
' bFormatXML Boolean Format XML instead
' of Default RAW Mode
'
'----------------------------------------------------------

'Trap Any Errors
On Error GoTo errhandler

'Declare XML DOM Objects
Dim oXMLDOM As MSXML2.DOMDocument30
Dim oXMLDOM2 As MSXML2.DOMDocument30
Dim dataNodeList As MSXML2.IXMLDOMNodeList
Dim dataNode As MSXML2.IXMLDOMNode
Dim rowNode As MSXML2.IXMLDOMNode
Dim newNode As MSXML2.IXMLDOMNode
Dim newRoot As MSXML2.IXMLDOMNode
Dim newNode2 As MSXML2.IXMLDOMNode

'Declare Variables
Dim i As Integer
Dim j As Integer

'Set XML DOM Objects
Set oXMLDOM = New MSXML2.DOMDocument30
Set oXMLDOM2 = New MSXML2.DOMDocument30

'Save Recordset directly into a DOM tree.
rsRecordset.Save oXMLDOM, adPersistXML

'Set DOM Objects
Set dataNodeList = oXMLDOM.getElementsByTagName(&quot;rs:data&quot
Set dataNode = dataNodeList.Item(0)

'Is Raw XML Required to be Formatted
If bFormatXML Then
'Create a new Node under new DOM.
Set newRoot = oXMLDOM2.createNode(NODE_ELEMENT, msRootName, &quot;&quot

'Append root node.
Set newRoot = oXMLDOM2.appendChild(newRoot)

i = 0
'Loop through all z:row nodes.
While i < dataNode.childNodes.length
'Get to the z:row node.
Set rowNode = dataNode.childNodes.Item(i)

Set newNode = oXMLDOM2.createNode(NODE_ELEMENT, msRowName, &quot;&quot

'Append row node under new root.
Set newNode = newRoot.appendChild(newNode)

j = 0
'Loop through all attributes of z:row.
While j < rowNode.Attributes.length

'Create new element for column name.
Set newNode2 = oXMLDOM2.createNode(NODE_ELEMENT, rowNode.Attributes.Item(j).baseName, &quot;&quot

'Assign column value to new node value.
newNode2.Text = rowNode.Attributes.Item(j).Text

'Append new column node under new node.
Set newNode2 = newNode.appendChild(newNode2)

j = j + 1
Wend

i = i + 1
Wend

'Return XML DOM Object
Set CreateXMLFromRS = oXMLDOM2
Else
'Return XML DOM Object
Set CreateXMLFromRS = oXMLDOM
End If

ExitFn:
'Release References
Set oXMLDOM = Nothing
Set oXMLDOM2 = Nothing
Set dataNodeList = Nothing
Set dataNode = Nothing
Set rowNode = Nothing
Set newNode = Nothing
Set newRoot = Nothing
Set newNode2 = Nothing

Exit Function

errhandler:
'Error
Resume ExitFn

End Function


HTH - Sorry about the formatting,

Codefish