Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Create XML File in SQL Server

Posted on 2001-07-29
Medium Priority
Last Modified: 2008-02-01
Can you create an XML File directly from a SQL Server only using the query analyzer or a single stored procedure.  For example, I want to run nightly batch jobs that will re-create several different .xml files that can reside in a folder from which I can later access the data via external entity calls from an HTML page.
Question by:Gcoopman
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1

Expert Comment

ID: 6334410
just doing it solely from query analyzer I'm not sure about.
Creating a stored procedure and then calling it from an app or a piece of script, Yes definitely.
What version of SQL Server are you using?
If it is 2000, it is fairly easy using the FOR XML clause on a query. I can help you with that if you need.
If it is SQL Server 7, I believe that you can get the same functionality with a tech preview from Microsoft, although It wouldn't be supported.
Anything older and you would have to convert your results to XML yourself (Not to hard, just wrap each returned field in the appropriate tags) but it is a pain.

Author Comment

ID: 6334509
SQL 2000.  I have used the "FOR XML" clause and see the output using Query Analyzer, but don't know how to store that output into an .XML file which could be independently stored in Windows Explorer and that I could view using a Text Editor.

Expert Comment

ID: 6334759
You could execute the query using VB/Vbscript (Or Java/Javascripr etc, whatever you are more comfortable with) and write the output out to a file.
How you would schedule this to run regularly I'm not sure, I don't think it should be tricky, but I have never done it myself. I could give you the code however (In VB/Vbscript anyway)

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 6334812

Author Comment

ID: 6340298
Don't send me the code because the intent of my initial question (and maybe my wording was ambiguous) was to create the text file from SQL Server stand-alone (no calls from VB, ASP, or anything else  ... just pure TSQL.)

Expert Comment

ID: 6343860
I'm not sure if it is possible, I had a similar problem a while back. If anyone does know a way I would be interested. In oracle there are packages such as UTL_FILE to accomplish such things, but T-SQL does not seem as 'feature rich' as PL/SQL.

Author Comment

ID: 6344577
So far, I have not seen any books or tapes that talk about this functionality, or lack of.  I entertained the idea of "Select * into tableX from tableY for xml raw", but this is not allowed in TSQL right now.  To accomplish what I want I will probably place the code into the an VB ADO type script, compile as an Exe and then schedule a SQL Job to run the Exe nightly.

Expert Comment

ID: 6790012
It's time to clean up this topic area and that means taking care of this question. Your options at this point are:

1. Award points to the Expert who provided an answer, or who helped you most. Do this by clicking on the "Accept Comment as Answer" button that lies above and to the right of the appropriate expert's name.

2. PAQ the question because the information might be useful to others, but was not useful to you. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

3.  Ask Community Support to help split points between participating experts.  Just comment here with details.

4.  Delete the question because it is of no value to you or to anyone else.  To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

If you elect for option 2, 3 or 4, just post comment with details here and I'll take it from there.  We also request that you review any other open questions you might have and update/close them.  Display all your question history from your Member Profile to view details.

---------->  Hi Experts:

In the event that the Asker does not respond, I would very much appreciate your opinions as to which Expert ought to receive points (if any) as a result of this question.  Likewise, you can also suggest that I PAQ or delete the question.

------------->  The ALL TOPICS link has been updated today to reflect all the new TAs.

Thank you everyone.

Moondancer :)
Community Support Moderator @ Experts Exchange
LVL 23

Expert Comment

ID: 6946827

PAQ and refund points to asker

any objection should be raised within 7 days



Accepted Solution

Moondancer earned 0 total points
ID: 6948137
50 points refunded, this item moved to our PAQ at zero points and closed.

Thanks for your help, Brandon.

Moondancer - EE Moderator

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question