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,[descri
ption], 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?
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.