Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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
Avatar of Patrick Matthews
Commented:
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answers