Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Export XML (2003)

    Hello All,

    I'm attempting to work with the IT dept. of my company so that I can export queries from my Access database into a sql server. The way they set it up I have to export my query as a .xml file, then attach it to a web-based form for importing.

    When we try this we get an error message that the server doesn't recognize the field database_id. Looking at the .xml file, I can see that Access has added tags for the query name like this <qryName> ... </qryName>, which apparently is the field in question.

    I've been annoyed in the past that similarly whenever I export .htm files Access adds the query name above the table.

    Is there a way to turn this feature off?

    Thanks,

    Kevin

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Export XML (2003)

    When you do an export to XML, there is an Export XML Dialog box that has a More Options... button. If you click that you can turn off the schema export which may solve your problem. However, when I export a query to XML, I get the query name as a tab before each record. If that's the issue, I don't know of a way to turn off the prefix tag - it appears to be used as a record delimiter.

    Now I'm curious why you are exporting to XML - rather than using the Import capabilities of SQL Server? Is your SQL Server located on a web hosting server so you don't have direct connectivity to the Access database?
    Wendell

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export XML (2003)

    Wendell

    I was involved in a project that involved authentication from data we had sitting on a system here in <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18>. The data extracts are to be held on a server ; for my uploads we used csv files. We had BizTalk as middleware that extracted the data and sent it to a remote server in XML packets, returning it as a file allowing us to upload it to SQL with specific additional fields, these fields contained unique references for a citizen.

    This may be one of the reasons for this type of action. My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth.
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Export XML (2003)

    The XML stream is being written exactly to exactly reproduce the query data, including the name of the top node, which is the name of the query. If you exported a table, you would see the same thing, the table name tags delimiting each record in the table. If you export a query, you get the queryname delimiting each record in each table. If you want to lose the query name, first use a maketable query and create a temporary table, then export that to XML. The only problem is that it can't have the same name as the original table.
    Charlotte

  5. #5
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Export XML (2003)

    Thanks for your reply, luckily my company's IT dept figured out how to get the xml files to import correctly with the default structure so this problem is solved.

    My new problem is that I created a query to pull out the records that I want to export into xml. When I open the query in Access it works correctly, but when I export it into xml there is no data (only two header lines). I've tried several other queries and they all export into xml without problem.

    Are there special conditions that queries must meet before they will export data into xml format correctly?

    Thanks,

    Kevin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •