how t-sql query to xml dump file?

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

asked Nov 28 '11 at 03:21 AM in Default

jhunkhan
3 ● 1 ● 1 ● 1

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 '11 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 '11 at 03:30 AM jhunkhan

(comments are locked)

2 answers:

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

answered Nov 28 '11 at 04:04 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

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 '11 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 '11 at 12:23 AM Kev Riley ♦♦

thanks Rev

Nov 29 '11 at 12:47 AM jhunkhan

Yeah, nice one Rev !

Nov 29 '11 at 12:50 AM Fatherjack ♦♦

(comments are locked)