Create XML File in SQL Server

Posted on 2001-07-29
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
  • 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)

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.


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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

The Problem How to write an Xquery that works like a SQL outer join, providing placeholders for absent data on the outer side?  I give a bit more background at the end. The situation expressed as relational data Let’s work through this.  I’ve …
Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 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