CODEWITHSUNDEEP
xmljsont-sqlsql-server-2005xml-parsing
Artemination
Artemination

Reputation: 723

In TSQL converting JSON to XML?

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

Upvotes: 4

Views: 17370

Answers (4)

Wiaan van Aswegen
Wiaan van Aswegen

Reputation: 71

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://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

Upvotes: 1

gary
gary

Reputation: 150

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

Upvotes: 1

Karanko
Karanko

Reputation: 96

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

https://learn.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

Upvotes: 8

Konstantin Taranov
Konstantin Taranov

Reputation: 629

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

Upvotes: 4

Related Questions