SQL Server exports database records as XML and Json

Export data to XML

After SQL Server 2005, a for xml clause is provided to support XML in relational databases. You can use this command to convert the result set of a two-dimensional link to XML, and use BCP to save the data as XML.

For example, the following data:

You can use the following BCP command (note that you cannot press enter) to export it as an XML file and save it:

BCP "select top 30 [bom_no], [LEVEL] FROM [sqladmin]. [dbo]. [bom] for xml path, TYPE, ELEMENTS, ROOT ('regionsales') "QUERYOUT" d: \ temp \ test. XML "-c-t-T-S localhost

After the execution is complete, view the Test. XML file, as shown in the following figure. You can see that the file format is very clear and can be easily imported to other systems.

Export data as JSON

If you want to export data in SQL Server as Json, although this operation has been very mature in the application, SQL Server does not actually support this method (gossip, supported in the next version ). I recommend using this post: Success. Run the following BCP command after the stored procedure provided in this post is created:

The result is as follows: