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:
CREATE FUNCTION dbo.fn_parse_json2xml[ @json varchar[max] ] RETURNS xml AS BEGIN; DECLARE @output varchar[max], @key varchar[max], @value varchar[max], @recursion_counter int, @offset int, @nested bit, @array bit, @tab char[1]=CHAR[9], @cr char[1]=CHAR[13], @lf char[1]=CHAR[10]; --- Clean up the JSON syntax by removing line breaks and tabs and --- trimming the results of leading and trailing spaces: SET @json=LTRIM[RTRIM[ REPLACE[REPLACE[REPLACE[@json, @cr, ''], @lf, ''], @tab, '']]]; --- Sanity check: If this is not valid JSON syntax, exit here. IF [LEFT[@json, 1]!='{' OR RIGHT[@json, 1]!='}'] RETURN ''; --- Because the first and last characters will, by definition, be --- curly brackets, we can remove them here, and trim the result. SET @json=LTRIM[RTRIM[SUBSTRING[@json, 2, LEN[@json]-2]]]; SELECT @output=''; WHILE [@json!=''] BEGIN; --- Look for the first key which should start with a quote. IF [LEFT[@json, 1]!='"'] RETURN 'Expected quote [start of key name]. Found "'+ LEFT[@json, 1]+'"'; --- .. and end with the next quote [that isn't escaped with --- and backslash]. SET @key=SUBSTRING[@json, 2, PATINDEX['%[^\\]"%', SUBSTRING[@json, 2, LEN[@json]]+' "']]; --- Truncate @json with the length of the key. SET @json=LTRIM[SUBSTRING[@json, LEN[@key]+3, LEN[@json]]]; --- The next character should be a colon. IF [LEFT[@json, 1]!=':'] RETURN 'Expected ":" after key name, found "'+ LEFT[@json, 1]+'"!'; --- Truncate @json to skip past the colon: SET @json=LTRIM[SUBSTRING[@json, 2, LEN[@json]]]; --- If the next character is an angle bracket, this is an array. IF [LEFT[@json, 1]='['] SELECT @array=1, @json=LTRIM[SUBSTRING[@json, 2, LEN[@json]]]; IF [@array IS NULL] SET @array=0; WHILE [@array IS NOT NULL] BEGIN; SELECT @value=NULL, @nested=0; --- The first character of the remainder of @json indicates --- what type of value this is. --- Set @value, depending on what type of value we're looking at: --- --- 1. A new JSON object: --- To be sent recursively back into the parser: IF [@value IS NULL AND LEFT[@json, 1]='{'] BEGIN; SELECT @recursion_counter=1, @offset=1; WHILE [@recursion_counter!=0 AND @offset