If you started on the root node then you should stop when you get into a leaf node. When to stop concatenating depends on where you started concatenating. However for nested objects it gets a little trickier, as the issue of when to stop concatenating becomes determinant. Getting a simple (without nested object) JSON object from a XML node is quite easy, just throw in the Xml Data Type methods: local-name(), text(), do some type conversions and escaping, concatenate everything into a NVARCHAR and you are done. However going to JSON is a different problem, as there is no direct JSON support, the only option left is to produce a NVARCHAR with the desired data. In SQL Server conversion from SQL to XML is straight forward, as is directly supported by SQL Server. It follows the path laid out by Phil Factor at but going for a different (and I believe simpler) approach. This article describes how to output data from SQL Server as a JSON string.
0 kommentar(er)
