how t-sql query to xml dump file?

can anyone help about dumping xml file using t-sql?

more ▼

asked Nov 28, 2011 at 03:21 AM in Default

avatar image

3 1 1 2

You might want to explain in a bit more detail what you are trying to do, and what steps you have already attempted

Nov 28, 2011 at 03:23 AM Kev Riley ♦♦

i want to make a xml file using t-sql..

i want to send xml data using tmp dump and my database is ms sql.. im using store procedure to get data from two tables.. i have my store proc but i dont know how to dump the data into xml file.

Nov 28, 2011 at 03:30 AM jhunkhan
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You can use the FOR XML clause to retrieve results as XML.

Try using this to get the data in the format you need, and then think about putting this out to a file

more ▼

answered Nov 28, 2011 at 04:04 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

i've done that.. the thing is i want to make a file like 'floorinv.xml', using this query 'select pkitemid,rtrim(itemdesc) as ItemDesc,itemprice,rtrim(researved3c2) as UnitNo,researvedn4 as FloorArea, Status from vitemfloor where fkitemtype='FloorInv' for xml RAW ('FLOORINV'), ROOT ('XMLRoot'),ELEMENTS, XMLSCHEMA;', how can i dump it to make a file like floorinv.xml in C:\temp or d:\temp

Nov 28, 2011 at 06:00 PM jhunkhan

OK if this is a one-off, then in SSMS go to Query -> Results To -> Results to File, and when you execute you will be prompted for the file name. If this is to be a regular task, then either look at a SSIS package to export the results to a file, or a job that sends output to a file.

Another alternative id to use 'bcp out'.

There are lots of ways depending on your circumstances.

Nov 29, 2011 at 12:23 AM Kev Riley ♦♦

thanks Rev

Nov 29, 2011 at 12:47 AM jhunkhan

Yeah, nice one Rev !

Nov 29, 2011 at 12:50 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 28, 2011 at 03:21 AM

Seen: 2080 times

Last Updated: Nov 28, 2011 at 03:21 AM

Copyright 2018 Redgate Software. Privacy Policy