Json_To_Xml

Converts a JSON string to XML

Free Function
SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017



Syntax:

SELECT SQLHTTP.net.Json_To_Xml ( @Json, @NodeStyle )



Arguments:

Name	Type	Description
@Json	nvarchar(MAX)	Json string
@NodeStyle	tinyint	One of these three values. See examples below. Simple conversion of the JSON Objects, Arrays, etc. using names and types as XML nodes and attributes. Very readable, but not reversible back to the original JSON. Uses the Json’s Objects, Arrays, etc. names, when available, as the newly created XML’s node names. Reversible back to the original JSON. Uses the Json’s types as the newly created XML’s node names. Reversible back to the original JSON

Name

Type

Description

@Json

nvarchar(MAX)

Json string

@NodeStyle

tinyint

One of these three values. See examples below.

Simple conversion of the JSON Objects, Arrays, etc. using names and types as XML nodes and attributes. Very readable, but not reversible back to the original JSON.
Uses the Json’s Objects, Arrays, etc. names, when available, as the newly created XML’s node names. Reversible back to the original JSON.
Uses the Json’s types as the newly created XML’s node names. Reversible back to the original JSON



Return Type:

xml



Remarks:

The JsonDefaultNoNameNode field in the Defaults table can be updated to assign a different default node name for Json objects or arrays that are not named. Default value: NoName



Sample Usage:

The following JSON string is used for the examples below:


DECLARE @Json nvarchar(MAX)

SET @Json = '{
  "firstName": "John",
  "lastName": "Smith",
  "isAlive": true,
  "age": 25,
  "address": {
    "streetAddress": "21 2nd Street",
    "city": "New York",
    "state": "NY",
    "postalCode": "10021-3100"
  },
  "phoneNumbers": [
    {
      "type": "home",
      "number": "212 555-1234"
    },
    {
      "type": "office",
      "number": "646 555-4567"
    },
    {
      "type": "mobile",
      "number": "123 456-7890"
    }
  ],
  "children": [],
  "spouse": null
}'

DECLARE @Json nvarchar(MAX)

SET @Json = '{

"firstName": "John",

"lastName": "Smith",

"isAlive": true,

"age": 25,

"address": {

"streetAddress": "21 2nd Street",

"city": "New York",

"state": "NY",

"postalCode": "10021-3100"

"phoneNumbers": [

{

"type": "home",

"number": "212 555-1234"

{

"type": "office",

"number": "646 555-4567"

{

"type": "mobile",

"number": "123 456-7890"

}

"children": [],

"spouse": null

Using @NodeStyle = 0:


SELECT SQLHTTP.net.Json_To_Xml(@Json, 0)

SELECT SQLHTTP.net.Json_To_Xml(@Json, 0)

</phoneNumbers>

</JsonObject>

Using @NodeStyle = 1:


SELECT SQLHTTP.net.Json_To_Xml(@Json, 1)

SELECT SQLHTTP.net.Json_To_Xml(@Json, 1)

<streetAddress JsonType="String">21 2nd Street</streetAddress>

</address>

<lastName JsonType="String">Smith</lastName>

</NoName>

<type JsonType="String">office</type>

</NoName>

<type JsonType="String">mobile</type>

</NoName>

</phoneNumbers>

</NoName>

Using @NodeStyle = 2:


SELECT SQLHTTP.net.Json_To_Xml(@Json, 2)

SELECT SQLHTTP.net.Json_To_Xml(@Json, 2)

<JsonString Name="streetAddress">21 2nd Street</JsonString>

</JsonObject>

<JsonString Name="lastName">Smith</JsonString>

</JsonObject>

<JsonString Name="type">office</JsonString>

</JsonObject>

<JsonString Name="type">mobile</JsonString>

</JsonObject>

</JsonArray>

</JsonObject>



Json_To_Xml

Featured API Wrapper Code / SQL Server