Solved

Create XML File in SQL Server

Posted on 2001-07-29
10
180 Views
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.
0
Comment
Question by:Gcoopman
[X]
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
10 Comments
 
LVL 2

Expert Comment

by:Carlovski
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.
0
 

Author Comment

by:Gcoopman
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.
0
 
LVL 2

Expert Comment

by:Carlovski
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)

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Gcoopman
ID: 6334812
OK
0
 

Author Comment

by:Gcoopman
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.)
0
 
LVL 2

Expert Comment

by:Carlovski
ID: 6343860
O.K,
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.
0
 

Author Comment

by:Gcoopman
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.
0
 
LVL 1

Expert Comment

by:Moondancer
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.

PLEASE DO NOT AWARD THE POINTS TO ME.
____________________________________________
---------->  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
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 6946827
<review>

<recommendation>
PAQ and refund points to asker
</recommendation>

<objection>
any objection should be raised within 7 days
</objection>

<reviewer>
b1xml2
</reviewer>

</review>
0
 
LVL 1

Accepted Solution

by:
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
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP and Soap 3 70
Insert Powershell variable into XML 4 81
C# Formatting DateTime String Value 5 68
Create PowerShell 2-Dimensional Array From XML Configuration File 6 55
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…
The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 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