generating XML from Access

[hoke]

How can I generate xml documents from an access database. Does Access have a feature or is there a serverobject I can use that makes my live easier. Or do I have to code it myself with Vbscript? I just want a simple facility that transforms a table (or a query) into a XML document

e.g
Table Persons (firstname,lastname) with data (Herman,Brood) (Bart,Chabott

should be turned int

<persons><persons><firstname
Herma
</firstname><lastname
Broo
</lastname></person><person><firstname
Bar
</firstname><lastname
Chabot
</lastname></person></persons>

[Bob Barrows [MVP]]

hoke wrote:

> How can I generate xml documents from an access database. Does Access
> have a feature

I don't know. Have you looked in online help? You'll get faster answers for
your Access questions by asking them on an Access newsgroup

> or is there a serverobject I can use that makes my
> live easier. Or do I have to code it myself with Vbscript? I just
> want a simple facility that transforms a table (or a query) into a
> XML document.

I've written my own code to do it. It's pretty trivial. Are you just looking
for a shortcut or do you have no idea where to start?

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[McKirahan]

"hoke" <anonymous@discussions.microsoft.com> wrote in message
news:5C569F05-846A-4437-BECC-EB1CC4CF3976@microsoft.com...

> How can I generate xml documents from an access database. Does Access have

a feature or is there a serverobject I can use that makes my live easier. Or
do I have to code it myself with Vbscript? I just want a simple facility
that transforms a table (or a query) into a XML document.

>
> e.g.
> Table Persons (firstname,lastname) with data (Herman,Brood) (Bart,Chabott)
>
> should be turned into
>
> <persons><persons><firstname>
> Herman
> </firstname><lastname>
> Brood
> </lastname></person><person><firstname>
> Bart
> </firstname><lastname>
> Chabott
> </lastname></person></persons>

How about this VBS file.
It will run standalone for testing.
It can be converted to an ASP page.
Watch for word-wrap.

'*
'* Read an MS-Access database Catalog and Table to generate an XML file.
'*
Option Explicit
'*
'* Declare Constants
'*
Const cVBS = "Persons.vbs"
Const cMDB = "Persons.mdb"
Const cTBL = "Persons"
'*
Const adOpenStatic = 3
'*
'* Declare Variables
'*
Dim strDSN
strDSN = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
Dim intFOR
Dim strREC
Dim intREX
Dim arrRST(99)
Dim strSQL
strSQL = "SELECT * FROM " & cTBL
Dim intTBL
intTBL = 0
Dim strXML
'*
'* Declare Objects
'*
Dim objADO
Set objADO = CreateObject("ADODB.Connection")
objADO.Open strDSN & cMDB
Dim objCAT
Set objCAT = CreateObject("ADOX.Catalog")
objCAT.ActiveConnection = strDSN & cMDB
Dim objCOL
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objTAB
Dim objXML
Set objXML = objFSO.OpenTextFile(cTBL & ".xml",2,true)
'*
'* Identify Columns
'*
For Each objTAB in objCAT.Tables
If objTAB.Type = "TABLE" _
And objTAB.Name = cTBL Then
For Each objCOL in objTAB.Columns
intTBL = intTBL + 1
arrRST(intTBL) = objCOL.Name
Next
End If
Next
'*
'* Count Records
'*
Dim objRST
Set objRST = CreateObject("ADODB.Recordset")
objRST.Open strSQL, strDSN & cMDB, adOpenStatic
intREX = objRST.RecordCount
objRST.Close
Set objRST = Nothing
'*
'* Write XML
'*
objXML.WriteLine("<?xml version='1.0' ?>" & vbCrLf)
If intREX = 0 Then
objXML.WriteLine("<" & cTBL & "_Table>" & vbCrLf)
objXML.WriteLine(" <" & cTBL & "_Entry>" & vbCrLf)
For intFOR = 1 To intTBL
objXML.WriteLine(" <" & arrRST(intFOR) & " />" & vbCrLf)
Next
objXML.WriteLine(" </" & cTBL & "_Entry>" & vbCrLf)
objXML.WriteLine("</" & cTBL & "_Table>" & vbCrLf)
Else
Set objRST = objADO.Execute(strSQL)
objXML.WriteLine("<" & cTBL & "_Table>" & vbCrLf)
Do While Not objRST.EOF
objXML.WriteLine(" <" & cTBL & "_Entry>" & vbCrLf)
For intFOR = 1 To intTBL
strREC = objRST(arrRST(intFOR))
If IsNull(strREC) Then strREC = ""
If strREC = "" Then
strXML = " />"
Else
strXML = ">" & strREC & "</" & arrRST(intFOR) & ">"
End If
objXML.WriteLine(" <" & arrRST(intFOR) & strXML & vbCrLf)
Next
objXML.WriteLine(" </" & cTBL & "_Entry>" & vbCrLf)
objRST.MoveNext
Loop
objXML.WriteLine("</" & cTBL & "_Table>" & vbCrLf)
Set objRST = Nothing
End If
'*
'* Destroy Objects
'*
Set objADO = Nothing
Set objCAT = Nothing
Set objFSO = Nothing
Set objXML = Nothing
'*
'* Finish Message
'*
MsgBox "'" & cTBL & ".xml' contains " & intREX & "
records",vbInformation,cVBS