Converting JSON data to XML using a T-SQL function

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 it’s fairly common in web service applications. If you’ve managed to land JSON data on your server and need to convert it to XML, here’s an idea how to.

I’ve 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, I’ll let the inline comments speak for themselves. Enjoy the ride:

CREATE FUNCTION dbo.fn_parse_json2xml(
    @json    varchar(max)

    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<LEN(@json)) BEGIN;
                    SET @offset=@offset+
                        PATINDEX('%[{}]%', SUBSTRING(@json, @offset+1,
                    SET @recursion_counter=@recursion_counter+
                        (CASE SUBSTRING(@json, @offset, 1)
                            WHEN '{' THEN 1
                            WHEN '}' THEN -1 END);

                SET @value=CAST(
                    dbo.fn_parse_json2xml(LEFT(@json, @offset))
                        AS varchar(max));
                SET @json=SUBSTRING(@json, @offset+1, LEN(@json));
                SET @nested=1;

            --- 2a. Blank text (quoted)
            IF (@value IS NULL AND LEFT(@json, 2)='""')
                SELECT @value='', @json=LTRIM(SUBSTRING(@json, 3,

            --- 2b. Other text (quoted, but not blank)
            IF (@value IS NULL AND LEFT(@json, 1)='"') BEGIN;
                SET @value=SUBSTRING(@json, 2,
                        SUBSTRING(@json, 2, LEN(@json))+' "'));
                SET @json=LTRIM(
                    SUBSTRING(@json, LEN(@value)+3, LEN(@json)));

            --- 3. Blank (not quoted)
            IF (@value IS NULL AND LEFT(@json, 1)=',')
                SET @value='';

            --- 4. Or unescaped numbers or text.
            IF (@value IS NULL) BEGIN;
                SET @value=LEFT(@json,
                    PATINDEX('%[,}]%', REPLACE(@json, ']', '}')+'}')-1);
                SET @json=SUBSTRING(@json, LEN(@value)+1, LEN(@json));

            --- Append @key and @value to @output:
            SET @output=@output+@lf+@cr+
                REPLICATE(@tab, @@NESTLEVEL-1)+
                        REPLACE(@value, '\"', '"'), '\\', '\'), '')+
                    (CASE WHEN @nested=1
                        THEN @lf+@cr+REPLICATE(@tab, @@NESTLEVEL-1)
                        ELSE ''

            --- And again, error checks:
            --- 1. If these are multiple values, the next character
            ---    should be a comma:
            IF (@array=0 AND @json!='' AND LEFT(@json, 1)!=',')
                RETURN @output+'Expected "," after value, found "'+
                    LEFT(@json, 1)+'"!';

            --- 2. .. or, if this is an array, the next character
            --- should be a comma or a closing angle bracket:
            IF (@array=1 AND LEFT(@json, 1) NOT IN (',', ']'))
                RETURN @output+'In array, expected "]" or "," after '+
                    'value, found "'+LEFT(@json, 1)+'"!';

            --- If this is where the array is closed (i.e. if it's a
            --- closing angle bracket)..
            IF (@array=1 AND LEFT(@json, 1)=']') BEGIN;
                SET @array=NULL;
                SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)));

                --- After a closed array, there should be a comma:
                IF (LEFT(@json, 1) NOT IN ('', ',')) BEGIN
                    RETURN 'Closed array, expected ","!';

            SET @json=LTRIM(SUBSTRING(@json, 2, LEN(@json)+1));
            IF (@array=0) SET @array=NULL;


    --- Return the output:
    RETURN CAST(@output AS xml);

And here’s 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",
        "PhoneNumbers": {
            "home":"212 555-1234",
            "fax":"646 555-4567"

SELECT dbo.fn_parse_json2xml(@json);

13 thoughts on “Converting JSON data to XML using a T-SQL function

  1. Pretty cool code… shouldn’t it encompass arrays in a parent node or somesuch though?
    i.e. something like “age”: [25, 26, 27],

    Should be


    I just wonder if pulling this data back out with 252627 would be actually the equivalent document wise.

  2. Just for kicks, I passed the entire function definition as JSON to the function. That is, I wrapped the function like this:
    declare @json varchar(max) = ‘


    I also escaped the quotation marks (double quotes) according to JSON rules. Then I ran the function with @json as the lone parameter. It errored out here:

    Msg 9455, Level 16, State 1, Line 158
    XML parsing: line 3, character 2498, illegal qualified name character

    I think there may be because the conversion to XML doesn’t escape ” in the resulting XML

    I wonder about this bit:

    — 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<LEN(@json)) BEGIN;
    SET @offset=@offset+
    PATINDEX('%[{}]%', SUBSTRING(@json, @offset+1,

    I tried it with json like this:

    declare @json varchar(max) = '
    {"oops":[{"one":"one quote}"}]}
    select dbo.fn_parse_json2xml(@json);

    Unfortunately, this errored out:

    <one>one quote</one>

    In array, expected “]” or “,” after value, found “””!

    I think the function may not account for braces inside a value (or key name for that matter — i know it’s a bad idea but not against the rules)

  3. Great piece of code! I have some big JSONs where I just need some parameters from. You just made my day.

  4. Thanks for sharing this Daniel, it works great.

    I had to make one change to work for me as I encounter Json Keys that have spaces in them, like this:
    “some key”: “some value”

    This was causing an error until I changed the line that extracts the key to this:
    SET @key=replace( SUBSTRING(@json, 2, PATINDEX(‘%[^\\]”%’, SUBSTRING(@json, 2, LEN(@json))+’ “‘)), ‘ ‘, ‘_’ );

    Thanks again!

  5. The LEN function should be replaced with the DATALENGTH function when checking for the value in section 2b. This would allow for trailing spaces inside a value.

    Select dbo.fn_parse_json2xml(‘{“Label”:”123 “}’)

  6. XML parsing: line 5, character 9, illegal qualified name character

    “publisherwf”: {
    “wfcreator”: “test2”,
    “wfstatus”: “initiated”,
    “wfrefurl”: “/12333”,
    “wfref”: “12233”
    “publisher”: “Test”,
    “subeventname”: “None”,
    “createdby”: “Orchestrator”,
    “eventtype”: “Manual”,
    “createdate”: “2017-09-01”,
    “eventname”: “SQA Event”,
    “eventstatus”: “published”,
    “parentref”: null,
    “eventrefid”: “5d7a8c01-5063-440f-92fb-0b3d1b421d5c”,
    “payload”: {
    “supplier”: “FINSAR”,
    “wfcase#”: “55555”,
    “MPN_TO_CPN”: [
    “CPN”: “16-3717-01”,
    “serialto”: “S020”,
    “DTCODE”: “”,
    “serialfrom”: “S010”,
    “QTY”: “200”,
    “LTCODE”: “”,
    “serialnos”: [
    “serialprefix”: “S0”
    “CPN”: “10-1246-02”,
    “serialto”: “S020”,
    “MPN”: “PS9821-2-AX”,
    “DTCODE”: “”,
    “serialfrom”: “S010”,
    “QTY”: “10000”,
    “LTCODE”: “”,
    “serialnos”: [
    “serialprefix”: “S0”

Let me hear your thoughts!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s