The method of exporting data to XML and JSON in SQL Server

Time:2021-5-9

Sometimes it is necessary to export the data in SQL server to other departments at one time for correlation or analysis. Of course, this requirement is very simple for SSIS, but it is a big deal to establish a SSIS package just by exporting the data at one time. There are many bugs in the import and export tools of SQL server, and the simplest way is BCP.

 

Export data to XML

After SQL Server 2005, a for XML clause is provided to support XML natively in relational database. With this command, the result set of two-dimensional relationship can be transformed into XML, and the data can be saved as XML through BCP.

For example, the following data:

image

 

We can export it as an XML file and save it with the following BCP command (note that there is no carriage return)

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

 

    image

 

After execution, 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 to other systems.

image

 

Export data to JSON

 

If you want to export the data in SQL server as JSON, although there is a very mature method for this operation in the application, SQL server does not natively support this method (grapevine, which will be supported in the next version). I recommend using this post: http://jaminquimby.com/servers/95-sql/sql-2008/145-code-tsql-convert-query-to-json Let’s do it. After setting up the stored procedure provided by this post, use the following BCP command:

image

 

After execution, the results are as follows:

image