How to export data in SQL Server to XML and Json

Editor to share with you how to export the data in SQL Server to XML and Json. I hope you will get something after reading this article. Let's discuss it together.

Data export to XML

After SQL Server 2005, a for xml clause is provided to natively support XML in relational databases. With this command, the result set of 2D relationships can be converted to XML, and the data can be saved as XML through BCP.

For example, the following data:

We can export it to a XML file with the following BCP command (note that there is no enter) 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

View the Test.XML file when the execution is complete, as shown in the following figure. You can see that the file format is so clear that it can be easily imported into other systems.

Data export to JSON

If you want to export data from SQL Server to Json, although there is a very mature way to do this in your application, SQL Server does not natively support this approach (grapevine, the next version will support it). I recommend using this post: jaminquimby.com/servers/95-sql/sql-2008/145-code-tsql-convert-query-to-json to do it. After the stored procedure provided by this post is established, use the following BCP command:

After the execution is completed, the result is as follows:

After reading this article, I believe you have a certain understanding of "how to export the data in SQL Server to XML and Json". If you want to know more about it, please follow the industry information channel. Thank you for reading!