In TSQL converting JSON to XML?

Question

I have a development to execute dinamyc queries stored at column table, all was programmed to manipulating parameters in XML, but there is some other apps that send the parameters in a json, so I would like to know if some one have made something to convert a Json to XML in T-SQL

Solution 1

Just to update this, You can now use OPENJSON to convert to a table and then translate the result to a XML document.

https://docs.microsoft.com/en-us/sql/relational-databases/json/convert-json-data-to-rows-and-columns-with-openjson-sql-server

SELECT * 
FROM  OPENJSON('{"title": "Sample Konfabulator Widget","name": "main_window" }') as DATA
FOR XML RAW, ELEMENTS

Solution 2

This function works for my tasks: http://sqlsunday.com/2013/05/12/converting-json-data-to-xml-using-a-t-sql-function/

Solution 3

how to assign this SELECT statement to a local variable

for ex:

DECLARE @data XML 
@data = SELECT * 
FROM OPENJSON('{"title": "Sample Konfabulator Widget","name": "main_window" }') as DATA
FOR XML RAW, ELEMENTS

Solution 4

Research from this thread (To find a faster solution) lead me to discover the following built-in SQL Server functions added from 2016 onwards..

ISJSON (Transact-SQL) tests whether a string contains valid JSON.
JSON_VALUE (Transact-SQL) extracts a scalar value from a JSON string.
JSON_QUERY (Transact-SQL) extracts an object or an array from a JSON string.
JSON_MODIFY (Transact-SQL) changes a value in a JSON string.

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

View more solutions