asked on Export MSAccess (2003) table to XML file
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
values ('abcdef','a Great plant',10,5.25),('MyPart1'
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?
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
values ('abcdef','a Great plant',10,5.25),('MyPart1'
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?
Microsoft AccessXMLVB Script
Last Comment
jann3891
The sample query they provided you uses syntax for SQL Server 2005 or later. You cannot run that in Access. The closest you could get would be to run it as a pass-through query. Maybe.
Please post a sample database. Be sure to first remove or obfuscate sensitive data.
Please post a sample database. Be sure to first remove or obfuscate sensitive data.
ASKER
The SQL table is linked to my MSAccess program. Table name is 'TableExport'.
I attached a sample database with the table.
I did try to run the sample query they provided on my SQL server 2005, but got this error.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near 'xml'.
BTW, it does not matter to me whether I use SQL or MSAccess, whatever gets the job done.
Thanks
SAMPLE.mdb
I attached a sample database with the table.
I did try to run the sample query they provided on my SQL server 2005, but got this error.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 19
Line 19: Incorrect syntax near 'xml'.
BTW, it does not matter to me whether I use SQL or MSAccess, whatever gets the job done.
Thanks
SAMPLE.mdb
Create a form with a command button, and have the command button run this code:
Sub MakeXML()
Dim rs As DAO.Recordset
Dim fso As Object, ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile("c:\Test\Results.xml")
Set rs = CurrentDb.OpenRecordset("TableExport")
ts.WriteLine "<List xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" VendorNumber =""V15605"">"
ts.WriteLine "<Location Name=""BoethingMaster"">"
Do Until rs.EOF
ts.WriteLine "<Item ItemNumber=""" & rs!ItemNumber & """ Description=""" & rs!Description & """ " & _
"Quantity=""" & rs!Quantity & """ Price=""" & rs!Price & """ SpecData = ""11 x 15"" />"
rs.MoveNext
Loop
ts.WriteLine "</Location>"
ts.WriteLine "</List>"
rs.Close
Set rs = Nothing
ts.Close
Set ts = Nothing
Set fso = Nothing
MsgBox "Done"
End Sub
ASKER
matthewspatrick, your code works great on my sample data.
just realized that my actual data contains characters. So sorry...
which is giving me errors whey I try to view the XML file.
An invalid character was found in text content. Error processing resource 'file:///C:/Test/Results.x
I changed the data on my sample mdb attached to include the ones with characters.
Is there a way to make this code work with the characters in my data?
SAMPLE.mdb
just realized that my actual data contains characters. So sorry...
which is giving me errors whey I try to view the XML file.
An invalid character was found in text content. Error processing resource 'file:///C:/Test/Results.x
I changed the data on my sample mdb attached to include the ones with characters.
Is there a way to make this code work with the characters in my data?
SAMPLE.mdb
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
AWESOME !! Thank you very much matthewspatrick
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...