Export data in SQL Server to XML and Json method analysis

Sometimes it is necessary to export the data in SQL Server to other departments at one time for correlation or analysis. This requirement is of course very simple for SSIS, but many times it is only necessary to export the data at one time. Building an SSIS package seems like a fuss, and there are still quite a few bugs in SQL Server's import and export tools. The simplest method is BCP.

Data export asXML

After SQL Server 2005, a for xml clause is provided to natively support XML in relational databases. This command can be used to convert the two-dimensional relational result set into XML, and the data can be saved as XML through BCP.

For example, the following data:

# We can export it as XML through the following BCP command (note that there is no carriage return) 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 completed, view the Test.XML file, as shown in the figure below. You can see that the file format is very clear and can be easily imported into other systems.

Export data as JSON

If you want to export the data in SQL Server Export to Json. Although this operation already has a very mature method in the application, SQL Server does not natively support this method (rumour, it will be supported in the next version). I recommend using the method from this post: jaminquimby.com/servers/95-sql/sql-2008/145-code-tsql-convert-query-to-json. After the stored procedure provided in this post is created, use the following BCP command:

After the execution is completed, the result will be as shown below:

The above is the detailed content of Export data in SQL Server to XML and Json method analysis. For more information, please follow other related articles on the PHP Chinese website!