can anyone help about dumping xml file using t-sql?
Nov 28 '11 at 03:21 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 '11 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
asked: Nov 28 '11 at 03:21 AM
Seen: 823 times
Last Updated: Nov 28 '11 at 03:21 AM
SQL Procedure working when run manually, not running from sql server agent
group tags for unknow depth in xpath
XML Format using the xml datatype in sql server2008
SQL 2000 XML Query
Updating .XML through SQL server
Html Table in Email
Form XML from table joins
How to calculate Row count?
What happens if I kill a cursor half way through?
Parameterized stored sproc in OLEDB Source /Error:Cannot find tables
Copyright © 2002-2012 Simple Talk Publishing. All Rights Reserved. If you have any queries, please contact the site administrators.Ask SQL Server Central is a community service provided by Red Gate.