Exporting a Query to an XML File

[Guest]

I am exporting a query I have made to an xml file. I am having problems
formatting it correctly. This will be used for a business, where they want
to export to an xml file the shipping and order information. It is
duplicating the Shipping Information at the moment.

This is how it is exporting.

<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlnsd="urn:schemas-microsoft-comfficedata"
generated="2007-07-17T11:44:05">
- <shipping_x0020_Query>
<AUX_PO_NO>BARB0001</AUX_PO_NO>
<recipientName>Art Vande</recipientName>
<addressInformation>123 Road</addressInformation>
<postalCode>12345</postalCode>
<city>City</city>
<state>State</state>
<country>USA</country>
<addressnumber>1234567</addressnumber>
<PO_HDR_DAT>2007-05-23T00:00:00</PO_HDR_DAT>
<PO_HDR_ORD_BY>BARBARA</PO_HDR_ORD_BY>
<PO_HDR_SHIP_VIA>TRUCK</PO_HDR_SHIP_VIA>
<PO_LIN_ITEM_NO>51644C</PO_LIN_ITEM_NO>
<PO_LIN_ITEM_DESC_1>HEWLETT-PACKARD CARTRIDGE</PO_LIN_ITEM_DESC_1>
<PO_LIN_ITEM_DESC_2>CYAN 51644C</PO_LIN_ITEM_DESC_2>
<PO_LIN_QTY_ORD>65</PO_LIN_QTY_ORD>
<PO_LIN_OUR_UNIT>EACH</PO_LIN_OUR_UNIT>
</shipping_x0020_Query>
- <shipping_x0020_Query>
<AUX_PO_NO>BARB0001</AUX_PO_NO>
<recipientName>Art Vande</recipientName>
<addressInformation>123 Road</addressInformation>
<postalCode>12345</postalCode>
<city>City</city>
<state>State</state>
<country>USA</country>
<addressnumber>1234567</addressnumber>
<PO_HDR_DAT>2007-05-23T00:00:00</PO_HDR_DAT>
<PO_HDR_ORD_BY>BARBARA</PO_HDR_ORD_BY>
<PO_HDR_SHIP_VIA>TRUCK</PO_HDR_SHIP_VIA>
<PO_LIN_ITEM_NO>ARCSERVE</PO_LIN_ITEM_NO>
<PO_LIN_ITEM_DESC_1>ARCSERITv.6.6x FOR WIN'NT</PO_LIN_ITEM_DESC_1>
<PO_LIN_ITEM_DESC_2>OPERATING SYSTEM SINGLE</PO_LIN_ITEM_DESC_2>
<PO_LIN_QTY_ORD>89</PO_LIN_QTY_ORD>
<PO_LIN_OUR_UNIT>EACH</PO_LIN_OUR_UNIT>
</shipping_x0020_Query>
</dataroot>


This is how I would like it formatted, so that only the line item(The item
ordered) Continues in the next part. Any suggestions?

<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlnsd="urn:schemas-microsoft-comfficedata"
generated="2007-07-17T11:44:05">
- <shipping_x0020_Query>
<AUX_PO_NO>BARB0001</AUX_PO_NO>
<recipientName>Art Vande</recipientName>
<addressInformation>123 Road</addressInformation>
<postalCode>12345</postalCode>
<city>City</city>
<state>State</state>
<country>USA</country>
<addressnumber>1234567</addressnumber>
<PO_HDR_DAT>2007-05-23T00:00:00</PO_HDR_DAT>
<PO_HDR_ORD_BY>BARBARA</PO_HDR_ORD_BY>
<PO_HDR_SHIP_VIA>TRUCK</PO_HDR_SHIP_VIA>
<PO_LIN_ITEM_NO>51644C</PO_LIN_ITEM_NO>
<PO_LIN_ITEM_DESC_1>HEWLETT-PACKARD CARTRIDGE</PO_LIN_ITEM_DESC_1>
<PO_LIN_ITEM_DESC_2>CYAN 51644C</PO_LIN_ITEM_DESC_2>
<PO_LIN_QTY_ORD>65</PO_LIN_QTY_ORD>
<PO_LIN_OUR_UNIT>EACH</PO_LIN_OUR_UNIT>
</shipping_x0020_Query>
- <shipping_x0020_Query>
<PO_LIN_ITEM_NO>ARCSERVE</PO_LIN_ITEM_NO>
<PO_LIN_ITEM_DESC_1>ARCSERITv.6.6x FOR WIN'NT</PO_LIN_ITEM_DESC_1>
<PO_LIN_ITEM_DESC_2>OPERATING SYSTEM SINGLE</PO_LIN_ITEM_DESC_2>
<PO_LIN_QTY_ORD>89</PO_LIN_QTY_ORD>
<PO_LIN_OUR_UNIT>EACH</PO_LIN_OUR_UNIT>
</shipping_x0020_Query>
</dataroot>

 

[Douglas J. Steele]

I don't believe it's possible to change what's exported as XML.

 

[Guest]

Anyone else have a suggestion.

 

[John Nurick]

Write VBA code to create the XML you need.

Anyone else have a suggestion.

 

[Hennie]

Go to the XML/XSL news group and they will be able to help you. You would
still be able to export it as an XML file using the Export as function. Let
them help you to set up the correct XSL (style sheet)file that would could
be used to export it correctly.

I had to go through the same procedure to export one table.

Hennie