Exporting XML with predefined hierarchy and attributes

K

Krasno

New member
Local time
Today, 18:02
Joined
Feb 1, 2007
Messages
2
Hello All,

I am trying to figure out the best way to accomplish exporting an XML file. My knowledge of VBA is limited, as is my knowledge of XLST, so please bare with me.


I am currently running a query in Access to spit out an XML file. The problem is that I want quite a bit more structure and to add the use of attributes, counting and some calculating in the exported file (so that it is structured more like a report). While Access will indeed export all of the info I need, it displays it horribly.

I am not sure how to get the document the way I would like, or even how to go about it, whether it should be done in Access, or through the use of XSLT once out of the database. I have seen examples both ways, but have no idea where to begin for either, or even which to use. The end XML file will be further parsed through PHP and used on a web site.

Here is an example of what Access exports:

Code: 
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2007-01-31T16:31:18">
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CLHU 2493660</ContainerNumber>
<BundleID>G0194</BundleID>
<Slab>160-0901</Slab>
<Dimen>126 x 71</Dimen>
<Size>3cm</Size>
<SqFt>62.13</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CLHU 2493660</ContainerNumber>
<BundleID>G0194</BundleID>
<Slab>160-0902</Slab>
<Dimen>126 x 71</Dimen>
<Size>3cm</Size>
<SqFt>62.13</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CLHU 2493660</ContainerNumber>
<BundleID>G0194</BundleID>
<Slab>160-0903</Slab>
<Dimen>126 x 71</Dimen>
<Size>3cm</Size>
<SqFt>62.13</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CLHU 2493660</ContainerNumber>
<BundleID>G0194</BundleID>
<Slab>160-0904</Slab>
<Dimen>126 x 71</Dimen>
<Size>3cm</Size>
<SqFt>62.13</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CLHU 2493660</ContainerNumber>
<BundleID>G0194</BundleID>
<Slab>160-0905</Slab>
<Dimen>126 x 71</Dimen>
<Size>3cm</Size>
<SqFt>62.13</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CLHU 2493660</ContainerNumber>
<BundleID>G0194</BundleID>
<Slab>160-0906</Slab>
<Dimen>126 x 71</Dimen>
<Size>3cm</Size>
<SqFt>62.13</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CLHU 2493660</ContainerNumber>
<BundleID>G0194</BundleID>
<Slab>160-0907</Slab>
<Dimen>126 x 71</Dimen>
<Size>3cm</Size>
<SqFt>62.13</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CLHU 2493660</ContainerNumber>
<BundleID>G0194</BundleID>
<Slab>160-0908</Slab>
<Dimen>126 x 71</Dimen>
<Size>3cm</Size>
<SqFt>62.13</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CRXU 1293241</ContainerNumber>
<BundleID>C0187</BundleID>
<Slab>160-0836</Slab>
<Dimen>116 x 68</Dimen>
<Size>3cm</Size>
<SqFt>54.78</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CRXU 1293241</ContainerNumber>
<BundleID>C0187</BundleID>
<Slab>160-0837</Slab>
<Dimen>116 x 68</Dimen>
<Size>3cm</Size>
<SqFt>54.78</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CRXU 1293241</ContainerNumber>
<BundleID>C0187</BundleID>
<Slab>160-0838</Slab>
<Dimen>116 x 68</Dimen>
<Size>3cm</Size>
<SqFt>54.78</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CRXU 1293241</ContainerNumber>
<BundleID>C0187</BundleID>
<Slab>160-0839</Slab>
<Dimen>116 x 68</Dimen>
<Size>3cm</Size>
<SqFt>54.78</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CRXU 1293241</ContainerNumber>
<BundleID>C0187</BundleID>
<Slab>160-0840</Slab>
<Dimen>116 x 68</Dimen>
<Size>3cm</Size>
<SqFt>54.78</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CRXU 1293241</ContainerNumber>
<BundleID>C0187</BundleID>
<Slab>160-0841</Slab>
<Dimen>116 x 68</Dimen>
<Size>3cm</Size>
<SqFt>54.78</SqFt>
</qryXMLOutput>
<qryXMLOutput>
<Stone_x0020_Name>Tropic Brown</Stone_x0020_Name>
<ContainerNumber>CRXU 1293241</ContainerNumber>
<BundleID>C0187</BundleID>
<Slab>160-0842</Slab>
<Dimen>116 x 68</Dimen>
<Size>3cm</Size>
<SqFt>54.78</SqFt>
</qryXMLOutput>
<qryXMLOutput>
</dataroot>


...and here is how I would like it to look:


Code: 
<?xml version="1.0" encoding="UTF-8"?>
<Inventory>
<Color Name="Tropic Brown" NoSlabs="24" NoBundles="3.4">
		<Container Number="CLHU 2493660" NoSlabs="8">
			<Bundle ID="G0194" NoSlabs="8">
				<Slab Num="160-0901" Dim="126x71" Size="3cm" SqFt="62.13"></Slab>
				<Slab Num="160-0902" Dim="126x71" Size="3cm" SqFt="62.13"></Slab>
				<Slab Num="160-0903" Dim="126x71" Size="3cm" SqFt="62.13"></Slab>
				<Slab Num="160-0904" Dim="126x71" Size="3cm" SqFt="62.13"></Slab>
				<Slab Num="160-0905" Dim="126x71" Size="3cm" SqFt="62.13"></Slab>
				<Slab Num="160-0906" Dim="126x71" Size="3cm" SqFt="62.13"></Slab>
				<Slab Num="160-0907" Dim="126x71" Size="3cm" SqFt="62.13"></Slab>
				<Slab Num="160-0908" Dim="126x71" Size="3cm" SqFt="62.13"></Slab>
			</Bundle>
		</Container>
		<Container Number="CRXU 1293241" NoSlabs="15">
			<Bundle ID="C0187" NoSlabs="7">
				<Slab Num="160-0836" Dim="116x68" Size="3cm" SqFt="54.78"></Slab>
				<Slab Num="160-0837" Dim="116x68" Size="3cm" SqFt="54.78"></Slab>
				<Slab Num="160-0838" Dim="116x68" Size="3cm" SqFt="54.78"></Slab>
				<Slab Num="160-0839" Dim="116x68" Size="3cm" SqFt="54.78"></Slab>
				<Slab Num="160-0840" Dim="116x68" Size="3cm" SqFt="54.78"></Slab>
				<Slab Num="160-0841" Dim="116x68" Size="3cm" SqFt="54.78"></Slab>
				<Slab Num="160-0842" Dim="116x68" Size="3cm" SqFt="54.78"></Slab>
			</Bundle>
			<Bundle ID="D0187" NoSlabs="8">
				<Slab Num="160-0843" Dim="116x67" Size="3cm" SqFt="53.97"></Slab>
				<Slab Num="160-0844" Dim="116x67" Size="3cm" SqFt="53.97"></Slab>
				<Slab Num="160-0845" Dim="116x67" Size="3cm" SqFt="53.97"></Slab>
				<Slab Num="160-0846" Dim="116x67" Size="3cm" SqFt="53.97"></Slab>
				<Slab Num="160-0847" Dim="116x67" Size="3cm" SqFt="53.97"></Slab>
				<Slab Num="160-0848" Dim="116x67" Size="3cm" SqFt="53.97"></Slab>
				<Slab Num="160-0849" Dim="116x67" Size="3cm" SqFt="53.97"></Slab>
				<Slab Num="160-0850" Dim="116x67" Size="3cm" SqFt="53.97"></Slab>
			</Bundle>
		</Container>
	</Color>
</Inventory>

There will be multiple colors, but for ease of reading this, I have used just one.

Thanks for any help anyone can give!!

Steve
 
You could try using the print #1 command and programmically create and then add the text to the file. When you create it give it a .xml file extension.

Open "FileName.xml" For Output As #1 ' creates the file
Print #1 "your text" ' for each line you want to add
close #1 ' closes the file

hth
John
 
John,

Thanks for the reply.

I don't even know where to begin. My knowledge to VBA is limited..that is to say, that I can probably manipulate something already written to do what I need if I look at it long enough, but making something from scratch is beyond me.

Sorry for my ignorance!!
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom