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.)
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.


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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Merge XML into DOM 5 38
What XAML would produce this? 3 72
Convert Oracle data into XML document 2 59
Online tools to transform xml to excel using xsl 1 32
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 …
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API ( has made its way into the popular lexicon of the English language.  A few years ago, …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

861 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

21 Experts available now in Live!

Get 1:1 Help Now