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)


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.)
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.


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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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 …
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.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now