Avatar of jann3891
jann3891
Flag for United States of America 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,[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?
Microsoft AccessXMLVB Script

Avatar of undefined
Last Comment
jann3891

8/22/2022 - Mon
Jeffrey Coachman

Perhaps an XML expert can help more.

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...
Patrick Matthews

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.
jann3891

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Patrick Matthews

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

Open in new window

jann3891

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.xml'. Line 23, Position...

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
Patrick Matthews

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
jann3891

ASKER
AWESOME !! Thank you very much matthewspatrick
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.