troubleshooting Question

Export MSAccess (2003) table to XML file

Avatar of jann3891
jann3891Flag for United States of America asked on
Microsoft AccessXMLVB Script
7 Comments1 Solution564 ViewsLast Modified:
Hello Experts,
I have one table in Access that I need to export into an XML file. I did export it and sent it to our vendor, but they came back and said that the format is wrong. They needed a specific format. This is the format they needed.

<List xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" VendorNumber =”V15605” >
  <Location Name="BoethingMaster">
    <Item ItemNumber="ABELGR-B-15" Description="ABELIA grandiflora" Quantity="1" Price="0" SpecData = “11 x 15” />
    <Item ItemNumber="ABUTWH-B-05" Description="ABUTILON White" Quantity="3" Price="0" SpecData = “11 x 15” />
    <Item ItemNumber="ACACAN-L-36" Description="ACACIA aneura L/B" Quantity="2" Price="0" SpecData = “11 x 15” />
    <Item ItemNumber="ACACBA-T-05" Description="ACACIA baileyana Std" Quantity="2" Price="0" SpecData = “11 x 15” />
    <Item ItemNumber="ACACCR-M-24" Description="ACACIA craspedocarpa" Quantity="1" Price="0" SpecData = “11 x 15” />
    <Item ItemNumber="ACACCU-B-15" Description="ACACIA cultriformis" Quantity="15" Price="0" SpecData = “11 x 15” />
    <Item ItemNumber="ACACCU-B-24" Description="ACACIA cultriformis" Quantity="11" Price="0" SpecData = “11 x 15” />
    <Item ItemNumber="ACACST-T-24" Description="ACACIA stenophylla Std" Quantity="3" Price="0" SpecData = “11 x 15” />
    <Item ItemNumber="ACERAT-M-15" Description="ACER palmatum Atropurpureum" Quantity="86" Price="0" SpecData = “11 x 15” />
    </Location>
</List>  


they also gave me a sample query on how to make this format.


create table #Locations (Location varchar(25))
insert #Locations (Location) values ('BoethingMaster')

create table #MyList (PartNumber varchar(50), [description] varchar(100), Quantity int, Cost decimal(19,3))
insert into #MyList(PartNumber,[description], Quantity,Cost)
values ('abcdef','a Great plant',10,5.25),('MyPart1','a Great plant',40,99.99),('MyPart2','a Great plant',1,2.25),('MyPart3','a Great plant',100,15.35)

select
'V15605' as '@VendorNumber',
(
      select
      l.location as '@Name',
(    
select  
      ml.PartNumber as '@ItemNumber',
      ml.[Description] as '@Description',
      ml.Quantity   as '@Quantity',
      ml.Cost as '@Price'
from #MyList ml for xml path ('Item'),type
)

from #Locations l for xml path ('Location'),type
)for xml path ('List'), type , elements xsinil



drop table #MyList,#Locations


This is my first time working with XML file and I do not even know where to begin.
where do I enter the sample query?
can I do this in MSAccess?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros