Link to home
Create AccountLog in
Avatar of jann3891
jann3891Flag 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?
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

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...
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.
Avatar of 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
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

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
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
AWESOME !! Thank you very much matthewspatrick