Methods for exporting data to XML and JSON in SQL Server _mssql

Sometimes it may be useful to associate or analyze data from SQL Server to other departments at once, a requirement that is certainly simple for SSIS, but a lot of times it makes a fuss to create a SSIS package just by exporting the data one at a time, while the SQL The Import Export tool for the server there are a lot of bugs, the easiest way is to bcp.

Data exported to XML

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

For example, the following data:

We can export this to an XML file by using the following bcp command (note that you cannot have a carriage return) and Save:

BCP select top [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 the execution is complete, as shown in the following figure. You can see that the file format is very clear and can easily be imported into other systems.

Data exported as JSON

If you want to export data from SQL Server to JSON, although this operation already has a very mature approach in your application, SQL Server does not natively support this approach (gossip, 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. After you have established the stored procedures provided by this post, use the following bcp command:

Upon completion of the execution, the results are shown below: