well formed XML from Access 2000

  • Thread starter Thread starter herr_johan
  • Start date Start date
H

herr_johan


Hello!

I have a question regarding how to export well formed XML from a query that I have created. The query is callde "Family" and results in following table:

| ParantName | ChildName | ChildAge | LastName |
|------------|-----------|----------|----------|
| James      | Carl      |       12 | Sky      |
| James      | Lisa      |        9 | Sky      |
| James      | Eric      |       11 | Sky      |
------------------------------------------------

When I choose to export it as XML, I get the following result:

<Family>
&nbsp;&nbsp;<ParantName>James</ParantName>&nbsp;
&nbsp;&nbsp;<ChildName>Carl</ChildName>
&nbsp;&nbsp;<ChildAge>12</ChildAge>
&nbsp;&nbsp;<LastName>Sky</LastName>
</Family>
<Family>
&nbsp;&nbsp;<ParantName>James</ParantName>&nbsp;
&nbsp;&nbsp;<ChildName>Lisa</ChildName>
&nbsp;&nbsp;<ChildAge>9</ChildAge>
&nbsp;&nbsp;<LastName>Sky</LastName>
</Family>
<Family>
&nbsp;&nbsp;<ParantName>James</ParantName>&nbsp;
&nbsp;&nbsp;<ChildName>Eric</ChildName>
&nbsp;&nbsp;<ChildAge>11</ChildAge>
&nbsp;&nbsp;<LastName>Sky</LastName>
</Family>

But&nbsp;I&nbsp;would&nbsp;like&nbsp;it&nbsp;to&nbsp;be&nbsp;more&nbsp;like:

<Family>
&nbsp;&nbsp;<ParantName>James</ParantName>&nbsp;
&nbsp;&nbsp;<LastName>Sky</LastName>
&nbsp;&nbsp;<Children>
&nbsp;&nbsp;&nbsp;&nbsp;<Child>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<ChildName>Carl</ChildName>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<ChildAge>12</ChildAge>
&nbsp;&nbsp;&nbsp;&nbsp;</Child>
&nbsp;&nbsp;&nbsp;&nbsp;<Child>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<ChildName>Lisa</ChildName>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<ChildAge>9</ChildAge>
&nbsp;&nbsp;&nbsp;&nbsp;</Child>
&nbsp;&nbsp;&nbsp;&nbsp;<Child>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<ChildName>Eric</ChildName>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<ChildAge>11</ChildAge>
&nbsp;&nbsp;&nbsp;&nbsp;</Child>
</Family>

How&nbsp;do&nbsp;I&nbsp;make&nbsp;settings&nbsp;for&nbsp;this&nbsp;in&nbsp;Access&nbsp;2003?&nbsp;
 
Is this what you meant to write? Your news poster scrambled
the text with html tags, making it hard to understand. Once
cleaned, I understand your problem, but I think you will need
to write code to make this work.

Function WriteXMLfoo()


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim curr As String
Dim out As String

Set dbs = CurrentDb
sql = "select * from Family order by LastName"
Set rst = dbs.OpenRecordset(sql, dbOpenSnapshot)

out = "<? xml ... yadda, yadda, yadda>" & vbCrLf
out = out & "<family>" & vbCrLf
out = out & " </children>" & vbCrLf
If Not rst.BOF Then curr = rst!LastName
Do Until rst.EOF
If curr <> rst!LastName Then
curr = rst!LastName
out = out & "</family>" & vbCrLf
out = out & "<family>" & vbCrLf
out = out & " <children>" & vbCrLf
End If
out = out & " <child>" & vbCrLf
out = out & " <childname>" & rst!ChildName & "</childname>" & vbCrLf
out = out & " <childage>" & rst!ChildAge & "</childage>" & vbCrLf
out = out & " </child>" & vbCrLf

rst.MoveNext
Loop
out = out & " </children>" & vbCrLf
out = out & "</family>" & vbCrLf
out = out & "</xml>"

Debug.Print out

End Function




-- original message ... cleaned up --

I have a question regarding how to export well formed XML
from a query that I have created. The query is callde "Family"
and results in following table:

| ParantName | ChildName | ChildAge | LastName |
|------------|-----------|----------|----------|
| James | Carl | 12 | Sky |
| James | Lisa | 9 | Sky |
| James | Eric | 11 | Sky |
------------------------------------------------

When I choose to export it as XML, I get the following result:

<Family>
<ParantName>James</ParantName>
<ChildName>Carl</ChildName>
<ChildAge>12</ChildAge>
<LastName>Sky</LastName>
</Family>
<Family>
<ParantName>James</ParantName>
<ChildName>Lisa</ChildName>
<ChildAge>9</ChildAge>
<LastName>Sky</LastName>
</Family>
<Family>
<ParantName>James</ParantName>
<ChildName>Eric</ChildName>
<ChildAge>11</ChildAge>
<LastName>Sky</LastName>
</Family>

But I would like it to be more like:

<Family>
<ParantName>James</ParantName>
<LastName>Sky</LastName>
<Children>
<Child>
<ChildName>Carl</ChildName>
<ChildAge>12</ChildAge>
</Child>
<Child>
<ChildName>Lisa</ChildName>
<ChildAge>9</ChildAge>
</Child>
<Child>
<ChildName>Eric</ChildName>
<ChildAge>11</ChildAge>
</Child>
</Family>

How do I make settings for this in Access 2003?
 
Ooops! I had a couple things out of place in the code there.
Here's the correct function, with it's output below.

Function WriteXMLfoo() As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim curr As String
Dim out As String

Set dbs = CurrentDb
sql = "select * from Family order by LastName"
Set rst = dbs.OpenRecordset(sql, dbOpenSnapshot)

out = "<? xml ... yadda, yadda, yadda>" & vbCrLf
out = out & "<family>" & vbCrLf
out = out & " <parentname>" & rst!ParentName & "</parentname>" & vbCrLf
out = out & " <lastname>" & rst!LastName & "</lastname>" & vbCrLf
out = out & " <children>" & vbCrLf

If Not rst.BOF Then curr = rst!LastName
Do Until rst.EOF
If curr <> rst!LastName Then
curr = rst!LastName
out = out & " </children>" & vbCrLf
out = out & "</family>" & vbCrLf
out = out & "<family>" & vbCrLf
out = out & " <parentname>" & rst!ParentName & "</parentname>" & vbCrLf
out = out & " <lastname>" & rst!LastName & "</lastname>" & vbCrLf
out = out & " <children>" & vbCrLf
End If
out = out & " <child>" & vbCrLf
out = out & " <childname>" & rst!ChildName & "</childname>" & vbCrLf
out = out & " <childage>" & rst!ChildAge & "</childage>" & vbCrLf
out = out & " </child>" & vbCrLf

rst.MoveNext
Loop
out = out & " </children>" & vbCrLf
out = out & "</family>" & vbCrLf
out = out & "</xml>"

WriteXMLfoo = out

End Function

.... produces this xml output ...


<? xml ... yadda, yadda, yadda>
<family>
<parentname>Danny</parentname>
<lastname>Lesandrini</lastname>
<children>
<child>
<childname>Sidney</childname>
<childage>1</childage>
</child>
<child>
<childname>Tasha</childname>
<childage>6</childage>
</child>
<child>
<childname>Masha</childname>
<childage>6</childage>
</child>
</children>
</family>
<family>
<parentname>James</parentname>
<lastname>Sky</lastname>
<children>
<child>
<childname>Eric</childname>
<childage>11</childage>
</child>
<child>
<childname>Lisa</childname>
<childage>9</childage>
</child>
<child>
<childname>Karl</childname>
<childage>12</childage>
</child>
</children>
</family>
</xml>
 
Back
Top