Share data exported as XML and Json in SQL Server, xmljson
Sometimes it is necessary to export data from SQL Server to other departments at a time for association or analysis. This requirement is of course very simple for SSIS, however, in many cases, you only need to export the data at a time and create an SSIS package. SQL Server has many bugs in the import and export tool. The simplest way is BCP.
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:
Copy codeThe Code is as follows:
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
View the Test. XML file after execution, as shown in. 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 at http://www.bkjia.com/article/61462.htm. Run the following BCP command after the stored procedure provided in this post is created:
The result is as follows: