Converting JSON data to XML using a T-SQL function

2013-05-122013-04-18 / Daniel Hutmacher

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

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

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

            --- 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));
            END;

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

            --- 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 ","!';
                END;
            END;

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

        END;
    END;

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

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",
        "state":"NY",
        "postalCode":"10021"
    },
        "PhoneNumbers": {
            "home":"212 555-1234",
            "fax":"646 555-4567"
        }
    }
}';

SELECT dbo.fn_parse_json2xml(@json);

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

SutoCom

2013-05-18 at 00:08

Reblogged this on Sutoprise Avenue, A SutoCom Source.

Reply
rezakhan2810

2013-07-23 at 09:26

what a great routine , many thanks !

Reply
rezakhan2810

2013-07-23 at 09:27

what a great piece of code , many thanks.

Reply
Mark Tassin

2014-02-03 at 23:10

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

25
26
27

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

Reply
- Mark Tassin
  
  2014-02-03 at 23:11
  
  Ok…
  <age type=”array”>
  <0>25<0>
  <0>26<0>
  <0>27<0>
  </age gt;
  
  Reply
gerald britton

2014-05-13 at 13:49

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) = ‘
{“sql”:”

“}
‘
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,
LEN(@json)));

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)

Reply
Paul Sinell

2014-11-28 at 11:51

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

Reply
Todd

2016-07-29 at 23:25

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!

Reply
cesar castano

2016-11-15 at 19:44

very good routine, specially for pre SQL Serve r2016. I had to replace in the json data, :” ” to :””, it wa s breaking with this

Reply
- Daniel Hutmacher
  
  2016-11-15 at 19:45
  
  Thanks!
  
  Reply
Michael Trexel

2017-08-09 at 20:05

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 “}’)

Reply
- Daniel Hutmacher
  
  2017-08-09 at 20:06
  
  Thanks, good point!
  
  Reply
Sai Kiran

2017-11-23 at 16:07

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”,
“MPN”: “SSFD4GHYBAIP2Q0A1”,
“DTCODE”: “”,
“serialfrom”: “S010”,
“QTY”: “200”,
“LTCODE”: “”,
“COMPONENT_MANUFACTURER”: “celtha”,
“serialnos”: [
“S001”,
“S002”
],
“serialprefix”: “S0”
},
{
“CPN”: “10-1246-02”,
“serialto”: “S020”,
“MPN”: “PS9821-2-AX”,
“DTCODE”: “”,
“serialfrom”: “S010”,
“QTY”: “10000”,
“LTCODE”: “”,
“COMPONENT_MANUFACTURER”: “NEC”,
“serialnos”: [
“S001”,
“S002”
],
“serialprefix”: “S0”
}
]
}
}’

Reply
- Juan Hernandez
  
  2019-07-27 at 03:23
  
  Hi Sai. I got the same error message. Were you able to make changes to the function to make it work?
  
  Reply
Vibs2012 (@vibs2012)

2018-02-01 at 07:00

Thanks a lot. It worked very nicely.

Reply
papil

2018-06-01 at 21:32

how to pass column to this function instead of @json?

Reply
- Daniel Hutmacher
  
  2018-06-02 at 00:04
  
  You could look into CROSS APPLY for that!
  
  Reply
Pingback: Processing JSON in MS SQL 2014 / 2012 - Blog programistyczny Gruchy
Eric

2019-05-17 at 13:49

Hi Daniel! Thank you for the effort. I have a question: Do you know or do you have a t-sql function to convert XML to JSON? It would be great. Thank you!

Reply
- Daniel Hutmacher
  
  2019-05-17 at 13:56
  
  Not that I know of, but there should be a couple of them on the Internet.
  
  Honestly, though, these types of conversions are probably better (and easier!) to do in a rich procedural language, perhaps inside an SSIS package as part of your ETL job.
  
  Reply
  - Eric
    
    2019-05-20 at 09:54
    
    Thank you for the answer. In fact what we are trying is using SQL-script to convert a xml format field to json. The xml structure is different for each value of this field, and the idea is to take any kind of xml field and transform it into a new field but in json.
    
    Reply
Johny

2019-06-26 at 02:12

Excelente, nos queda a nosotros mejorar tu rutina.

Reply
- Juan Hernandez
  
  2019-07-27 at 03:18
  
  Hola Johny.
  
  Qué tal empezar a mejorarla con esto?
  
  Mira, tengo estos datos JSON que necesito convertir a XML:
  
  {
  “rates”: {
  “2019-07-23”: {
  “USD”: 0.7605336601
  },
  “2019-07-25”: {
  “USD”: 0.7617709547
  },
  “2019-07-26”: {
  “USD”: 0.758719346
  },
  “2019-07-24”: {
  “USD”: 0.7616052506
  },
  “2019-07-22”: {
  “USD”: 0.7647459939
  }
  },
  “start_at”: “2019-07-20”,
  “base”: “CAD”,
  “end_at”: “2019-07-26”
  }
  
  Utilizé la funcion, pero me da este error:
  
  Message 9455, Level 16, State 1, Line 29
  XML parsing: line 3, character 3, illegal qualified name character
  
  Alguna idea de como mejorar la rutina para hacer que convierta estos datos JSON a XML?
  
  Gracias!
  
  Reply
  - Daniel Hutmacher
    
    2019-07-27 at 08:42
    
    Just a thought: have you tried replacing the ”-” characters in the dates with something else? I’m thinking would probably not be valid xml.
    
    Reply
    - Juan Hernandez
      
      2019-08-03 at 01:02
      
      Hi Daniel.
      
      Thank you very much for your suggestion. You are right, it would not be vallid XML.
      
      The way I got around this was by adding the following line of code:
      
      IF @key LIKE ‘[0-9]%’ SET @key=’date’+@key
      
      right before this line:
      
      — Append @key and @value to @output:
      
      And it worked very well.
      
      Thank you again for this great function and all your help!
      
      Reply
Juan Hernandez

2019-07-13 at 02:42

Hi Daniel. Thank you very much for this great function.

I tried it with JSON data having only one set of values:

https://api.exchangeratesapi.io/latest?base=CAD

and it worked wonderfully.

Now a have JSON data with multiple values:

https://api.exchangeratesapi.io/history?start_at=1999-01-01&end_at=2019-07-12&base=CAD

I tried again your great great function, but for some reason I’m getting the following message:

Msg 9455, Niveau 16, État 1, Procédure sp_extractForexApiData, Ligne 62 [Ligne de départ du lot 2]
XML parsing: line 3, character 4, illegal qualified name character

The first line of the error message is obviously in French ( as my SQL Server is in French), and it translates to:

Message 9455, Level 16, State 1, Procedure sp_extractForexApiData, Line 62 [Starting line of lot 2]

Any help would be greatly appreciated.

Thank you!

Reply
- Daniel Hutmacher
  
  2019-07-13 at 09:15
  
  Hi! So glad you liked the post!
  I’m unfortunately unable to provide support for my posts at this time because of other time constraints. However, I’ve noticed a number of other helpful comments, maybe you can find something to help you out there?
  
  Reply
  - Juan Hernandez
    
    2019-07-22 at 02:52
    
    Hi Daniel. I understand. You have helped a lot already by sharing this great function. Thanks anyway.
    
    Reply
Pingback: Parse json data to comma separated string in sql server | Jingyang Li
Chris Gallo

2020-12-08 at 14:37

Thanks! This is very helpful.

Reply
Rick Root

2021-01-15 at 15:58

I guess this doesn’t work with JSON Arrays since it requires that the first character be a {

Isn’t this a legit json object?

[ {“name”: “Rick”}, {“name”: “Rob”}, {“name”: “Jarrod”} ]

Reply
Francois GREGOIRE

2021-07-12 at 15:41

Great code Daniel, very clear and “clean”. Thanks a lot, saved me a lot of time !

Reply
Albert

2021-07-15 at 16:14

In principle there are 2 “problems” with this fine function:
1. xml restricts the names for nodes different than JSON. Something like is invalid in xml, while {“16×16”, “qwertz”} is valid. Similar additional cases might exist.
2. if the value itself contains reserved characters like &, the code
” + @value +”
produces invalid xml. @value must be correctly escaped.
When it’s tested (and I find again this site) I can post my version….

Reply
- Albert
  
  2021-07-15 at 16:15
  
  And the website kills > < in comments…..
  
  Reply
Albert

2021-07-15 at 16:16

test alert(‘hello’);

Reply
JPAULO

2021-08-23 at 22:57

This is a good function, but it’s in error:

begin
declare @json LONG VARCHAR;
set @json='{
“Comunity”: [{
“Person”: {
“firstName”: “Joao Paulo”
}
},{
“Person”: {
“firstName”: “Carlos”
}
}]
}’;
select fn_JSON_to_xml(@json);
end;

The result of converting the above JSON command is incorrect:

Joao Paulo

Carlos

I believe this would be the correct result:

Joao Paulo

Carlos

Please fix the function and answer, I really liked it and I will use it, thank you very much.

Reply
- JPAULO
  
  2021-08-23 at 23:00
  
  The result of converting the above JSON command is incorrect:
  Comunity
  Person
  firstName Joao Paulo /firstName
  /Person
  /Comunity
  Comunity
  Person
  firstName Carlos /firstName
  /Person
  /Comunity
  
  I believe this would be the correct result:
  
  Comunity
  Person
  firstName Joao Paulo /firstName
  /Person
  Person
  firstName Carlos /firstName
  /Person
  /Comunity
  
  Reply