export access 2003 table to xml native format

[Guest]

i want to figure out on how to export access 2003 tables to XML files with a
basic format like bellow:-
<?xml version="1.0" encoding="UTF-8"?>
<Class>
<student name="Blondie Bush">
<id>1</id>
<height>172 cm</height>
<weight>55 kg</weight>
<hobby>playing guitar</hobby>
<quote>life to the fullest</quote>
<image>images/blondie.jpg</image>
</student>
</Class>

I'm exporting one of the table and the xml output as showing below;-

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlnsd="urn:schemas-microsoft-comfficedata">
<Class>
<student>
<id>1</id>
<height>172 cm</height>
<weight>55 kg</weight>
<hobby>playing guitar</hobby>
<quote>life to the fullest</quote>
<image>images/blondie.jpg</image>
</student>
</Class>
</dataroot>

as you might notice there is a differences between the two xml files. how to
get rid the <dataroot> and how to make it export the <student name="Blondie
Bush"> format instead of <student>?
my xml structure is strict becource it will be parsed into flash
application. so it would be compulsory to comply with those given xml
structure (i cant change/modify on how flash parsed xml). so the only option
is to make access export into the very similar structure. i dunno much about
access 2003 and am still learning..

Thanks

 

[Rick Brandt]

i want to figure out on how to export access 2003 tables to XML files
with a basic format like bellow:-
<?xml version="1.0" encoding="UTF-8"?>
<Class>
<student name="Blondie Bush">
<id>1</id>
<height>172 cm</height>
<weight>55 kg</weight>
<hobby>playing guitar</hobby>
<quote>life to the fullest</quote>
<image>images/blondie.jpg</image>
</student>
</Class>

I'm exporting one of the table and the xml output as showing below;-

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlnsd="urn:schemas-microsoft-comfficedata">
<Class>
<student>
<id>1</id>
<height>172 cm</height>
<weight>55 kg</weight>
<hobby>playing guitar</hobby>
<quote>life to the fullest</quote>
<image>images/blondie.jpg</image>
</student>
</Class>
</dataroot>

as you might notice there is a differences between the two xml files.
how to get rid the <dataroot> and how to make it export the <student
name="Blondie Bush"> format instead of <student>?
my xml structure is strict becource it will be parsed into flash
application. so it would be compulsory to comply with those given xml
structure (i cant change/modify on how flash parsed xml). so the only
option is to make access export into the very similar structure. i
dunno much about access 2003 and am still learning..

Thanks

Create a Recordset against your data and loop through it. You will need a loop
within a loop. The outer loop processes all the rows and the inner loop
processes the fields on each each row. Within the inner loop you use the file
i/o functions built into Access to write the data to an external file.

Using this strategy you have complete control over the output.

 

[Brendan Reynolds]

You can either write VBA code to loop through a recordset and write the XML
in the format you want it, as Rick suggests elsewhere in this thread, or you
can let Access export the XML and then use XSLT to transform the XML into
your required format.

 

[Amy Blankenship]

Except it seems the "blondie bush" info is completely missing from the
access native export, somehow...

-Amy

 

[Brendan Reynolds]

It's difficult to be sure, of course, without seeing the schema and the SQL,
but most likely all that is required to include the student name is to add a
field - or possibly a join and a field - to the query - or to create a
query, if the current export is based directly on the table. That would have
to be done regardless of which approach was adopted. But the problem of
getting the name into an attribute of the student element instead of into a
nested name element would remain, that's where either custom VBA code or an
XSLT transform would be required. At least I think one of them would be
required - I couldn't say for certain that there isn't some way to get the
built-in export to XML feature to do it, only that I am not aware of such.

 

[Amy Blankenship]

Wasn't arguing that...just saying that the data has to exist before it can
be modified via XSLT.

-Amy

 

[Guest]

Thanks for the reply. am tries to grasp the information although am must say
am i lilttle bit lost .

Thanks again.

 

[Brendan Reynolds]

Sorry for any confusion. I was just suggesting a possible alternative. I'm
not implying that the XSLT route is necessarily any better than using VBA to
write the XML, either one can work. If you have XML and XSLT experience, you
might find the XSLT route easier, if you have VBA experience but don't have
much XML and XSLT experience, you'd likely find the VBA route easier.

Perhaps an example might help. Here is some code from one of my own apps.
First it writes the XML declaration and writes some values from text boxes
on an open form into the XML ...

strFile = CurrentProject.Path & "\NEWB.xml"
intFile = FreeFile
Open strFile For Output As intFile
boolFileOpen = True
Print #intFile, "<?xml version='1.0' encoding ='ISO-8859-1'?>"
Print #intFile, "<NEWB_MESSAGE
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " & _
"xsi:noNamespaceSchemaLocation='http://www.schoolreturn.ie/xsd/newb_osad_edu.xsd'>"

Authentication stuff snipped ...

Print #intFile, "<SCHOOL>"
Print #intFile, Space$(4) & "<SCHOOL_NAME>" & Me!txtSchoolName &
"</SCHOOL_NAME>"
Print #intFile, Space$(4) & "<SCHOOL_NAME_GAEILGE>" & Me!txtIrishName &
"</SCHOOL_NAME_GAEILGE>"
Print #intFile, Space$(4) & "<ROLL_NUMBER>" & Me!txtRollNumber &
"</ROLL_NUMBER>"

Later, it loops through a recordset writing values from the recordset to
the XML ...

Set db = CurrentDb
Set qdfStudent = db.QueryDefs("qryStudentGuardian")
qdfStudent.Parameters(0) = dtmStartDate
qdfStudent.Parameters(1) = dtmEndDate
Set rstStudent = qdfStudent.OpenRecordset
boolStudentOpen = True
Do Until rstStudent.EOF
Print #intFile, Space$(8) & "<STUDENT>"
Print #intFile, Space$(12) & "<STUDENT_PPSN>" &
rstStudent.Fields("PPSN") & "</STUDENT_PPSN>"
Print #intFile, Space$(12) & "<STUDENT_FIRST_NAME>" &
rstStudent.Fields("FirstName") & "</STUDENT_FIRST_NAME>"
Print #intFile, Space$(12) & "<STUDENT_MIDDLE_NAME>" &
rstStudent.Fields("MiddleName") & "</STUDENT_MIDDLE_NAME>"