Convert JSON to XML in SQL server 2014
Depending on your line of work, you may some time stumble over JSON data. JSON is a data format specifically designed to work well with Javascripts on web pages, so its fairly common in web service applications. If youve managed to land JSON data on your server and need to convert it to XML, heres an idea how to. Ive opted to write this as a scalar function that accepts a varchar(max) variable with the JSON code, and returns XML data. Because of the recursive nature of JSON (and XML for that matter), the function will also call itself in a recursive pattern. In this case, instead of breaking down the entire function into pieces and describing them each, Ill let the inline comments speak for themselves. Enjoy the ride: And heres how to test the query: DECLARE @json varchar(max); SET @json='{ "Person": { "firstName": "John", "lastName": "Smith", "age": [25, 26, 27], "Address": { "streetAddress":"21, 2nd Street", "city" :"New York", "state":"NY", "postalCode":"10021" }, "PhoneNumbers": { "home":"212 555-1234", "fax":"646 555-4567" } } }'; SELECT dbo.fn_parse_json2xml(@json);Share:Print |