asked
how t-sql query to xml dump file?
can anyone help about dumping xml file using t-sql?
You might want to explain in a bit more detail what you are trying to do, and what steps you have already attempted
You can use the [FOR XML][1] 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 [1]:
http://msdn.microsoft.com/en-us/library/ms178107.aspx
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
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.
