Need help with XML output to file

I'm using SQL Server 2005 / 9.0.3042

I'm not new to sql server, but making my first experience with xml in sql server 2005.

I have a query like this (based on <Table> with neccessary data):

SELECT TAG, PARENT, <columns...>

FROM <Table>

FOR XML EXPLICIT

This query creates a xml file exactly as i need it when i execute it in Management Studio. Well, with one exception. It does not write the <xml...> tag at the beginning of the xml file. But i'm sure i get that in there somewho else. What i need to do now is get that output to a file on disk. And that's where my problem starts.

I tried SQLCMD within Management Studio, but that doesn't accept the ':XML ON' tag and ignores it. the resulting file written is not usable, as it also contains query summary information.

Any direction would be greatly appreciated!

Have you given DTS/SSIS a try. If you are having to do this procedure often, I would go with one of those.

|||

I could not find any way to choose xml as the destination for ssis. Could you give me a start on how to go on?

|||Hi Danny,
As you said that you are facing this problem in sql2005, so can u please tell me the querry by which i can generate a xml file through table in sql2000,
My basic question to you is,
HOW TO GENERATE AN XML FILE USING A SQL QUERY IN QUERY ANALYZER.?
is it possible.|||

Hi Prashant,

There are various ways to create a xml file. In general, u create a select statement and use "FOR XML xxxxx" at the end. Please have a look in Books Online for the possible <xxxxxx>. I would say it depends on purpose u want to achieve, you would choose the appropriate <xxxxxx> method. For each method u need to have its own data base.

I for my case needed to choose the FOR XML EXPLICIT method, as i need to reproduce a specific xml file dynamically, based on certain data. Running the query will create a xml file and give it as the result, so i can open it, and if i need copy the content. If "FOR XML EXPLICIT" is your choice, here is a simple example. I haven't done much on the other <xxxxxx> methods, so i'm sorry i won't be much of help. Method FOR XML EXPLICIT is the most time consuming way, but it gives almost every control to produce exactly the xml file needed.

OK: Here the sample for FOR XML EXPLICIT:

Let's suppose we need a xml file like this:

<Order>

<OrderItem Title="book1" Price="250.00"/>

<OrderItem Title="book2" Price="15.75" Discount=5.00/>

</Order>

For this we need to create a table holding the data to create the xml file using FOR XML EXPLICIT. This table must look like this: In the vertical it will have 1 row for ea line in the xml file. in the horizontal it needs the sum of all possible attributes. Enter a value will print the value in the xml file, enter empty string will print empty string in xml file, enter NULL as value will remove the attribute in the xml file. The table also needs the informatione to tell FOR XML EXPLICIT how the hierachy of the xml file must be. That is done using the TAG and PARENT attribut. The columns in the table must exactly match the names of the elements and attributes in the xml file, plus the level (number between - see blow).

Ok, here is the table:

TAG PARENT [Order!1] [OrderItem!2!Title] [OrderItem!2!Prive] [OrderItem!2!Discount]

-

1 NULL '' NULL NULL NULL

2 1 NULL book1 250.00 NULL

3 1 NULL book2 15.75 5.00

-

This is only a very simple example of FOR XML EXPLICIT, but i hope it makes clear on how it works. I used this way to generate our xml files dynamically. It was much work to build the system, but gives me much flexibility to construct all the various different xml files. Last but not least, it's only useful if the structure of the xml file don't change so often.