can anyone help about dumping xml file using t-sql?
Nov 28, 2011 at 03:21 AM
Oct 24, 2013 at 09:25 AM
You might want to explain in a bit more detail what you are trying to do, and what steps you have already attempted
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.
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
Nov 28, 2011 at 04:04 AM
Kev Riley ♦♦
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.
Yeah, nice one Rev !
Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.
Once you sign in you will be able to subscribe for any updates here
Answers and Comments
Need long-form SQL discussion? SQLserverCentral.com is the place.
asked: Nov 28, 2011 at 03:21 AM
Seen: 2034 times
Last Updated: Nov 28, 2011 at 03:21 AM
For XML Explicit question in sql server 2000
how to create order with multiple order lines with xml path?
SQL TO GET XML
Eliminating duplicates while utilizing string concantenation with FOR XML
Facing problem with xml auto in sql
sql xml format issue
XML fields to data set
Output SQL data as XML and export to XML file
Generate XML from SQL Server
Convert XML to resultset/table