Exporting data as XML and JSON in SQL Server

Sometimes the need to export data from SQL Server to other departments may be correlated or analyzed, which is of course very simple for SSIS, but many times it takes a lot of time to simply export the data and create an SSIS package that makes a fuss, while the SQL The Import and Export tool for the server where the bugs are still pretty much, the simplest way is bcp.

Data export to XML

After SQL Server 2005, a FOR XML clause is provided that natively supports XML in a relational database. This command enables you to convert a two-dimensional relational result set to XML, which can be stored as XML by bcp.

For example, the following data:

We can export it as an XML file with the following bcp command (note that you cannot have a carriage return) and Save:

BCP "SelectTOP [bom_no],[levelfrom forpath, TYPE, ELEMENTS, ROOT (' regionsales ') "Queryout" d:\temp\test. XML "-c-t-t-s localhost

Review the Test.xml file as shown in when execution is complete. You can see that the file format is very clear and easy to import into other systems.

Data export to JSON

If you want to export data from SQL Server to JSON, although this operation already has a very mature method in the application, SQL Server does not natively support this way (Grapevine, the next version will support). I recommend the way to use this post: Http://jaminquimby.com/servers/95-sql/sql-2008/145-code-tsql-convert-query-to-json to do it. After the stored procedure provided by the post has been established, use the following bcp command:

When the execution is complete, the results are as follows:

Exporting data as XML and JSON in SQL Server