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?
You might want to investigate the options that are available when you export to xml.
Just play around with them and create all different versions of the XML file, then see if any match what the vendor wants...