JSON and XML Data in SQL Server

For decades, relational databases (SQL) and hierarchical data (like XML or JSON) were enemies.

If you wanted to save a JSON in SQL Server, you would put it as plain text in a VARCHAR field (and prayed you wouldn’t have to search inside it).

Nowadays, the world is hybrid. Web APIs speak JSON. Configuration and electronic invoicing systems speak XML. SQL Server has evolved to understand both languages natively.

In this article, we will see how to “break” these documents to turn them into tables and vice versa.

JSON in SQL Server

Since SQL Server 2016, JSON support is native. However, there is no specific “JSON” data type. JSON data is stored in NVARCHAR columns, but the engine includes functions to validate and query it.

Read JSON (Parsing)

Imagine you receive this JSON from an API:

{
  "Cliente": "Ana",
  "Edad": 30,
  "Direccion": {
    "Ciudad": "Madrid",
    "CP": 28001
  }
}

To extract individual values, we use JSON_VALUE (for text/numbers) and JSON_QUERY (for objects/arrays).

DECLARE @json NVARCHAR(MAX) = '... (the json above) ...';

SELECT 
    JSON_VALUE(@json, '$.Cliente') AS Nombre, -- Returns "Ana"
    JSON_VALUE(@json, '$.Direccion.Ciudad') AS Ciudad, -- Navigates and returns "Madrid"
    JSON_QUERY(@json, '$.Direccion') AS ObjetoDireccion -- Returns the entire JSON fragment

From JSON to Table (OPENJSON)

This function allows us to convert a JSON array into rows of a relational table. It’s ideal for importing bulk data coming from a web source.

DECLARE @jsonArray NVARCHAR(MAX) = '[
    {"ID": 1, "Prod": "Bici", "Precio": 100},
    {"ID": 2, "Prod": "Casco", "Precio": 20}
]';

SELECT *
FROM OPENJSON(@jsonArray)
WITH (
    ID INT '$.ID',
    Producto VARCHAR(50) '$.Prod',
    Precio DECIMAL(10,2) '$.Precio'
);

Result:

From Table to JSON (FOR JSON)

If you want to send your table data to a web application, you can have SQL Server format the output directly as JSON by adding FOR JSON PATH to the end of your query.

SELECT ClienteID, Nombre, Email
FROM Clientes
FOR JSON PATH, ROOT('ListaClientes');

XML in SQL Server

XML is the “big brother” of JSON. It’s more verbose and strict, but it remains the standard in many environments (banking, medical, and invoicing).

Unlike JSON, SQL Server does have a specific data type called XML. This allows validating that the content is well-formed before saving it.

Read XML (Nodes and Value)

The syntax is a bit more complex (XQuery).

DECLARE @xml XML = '
<Pedidos>
    <Pedido ID="100">
        <Producto>Teclado</Producto>
        <Cantidad>5</Cantidad>
    </Pedido>
    <Pedido ID="101">
        <Producto>Ratón</Producto>
        <Cantidad>2</Cantidad>
    </Pedido>
</Pedidos>';

-- Break down the XML into rows
SELECT 
    T.C.value('@ID', 'INT') AS PedidoID, -- Attribute uses @
    T.C.value('(Producto)[1]', 'VARCHAR(50)') AS Producto, -- Element
    T.C.value('(Cantidad)[1]', 'INT') AS Cantidad
FROM @xml.nodes('/Pedidos/Pedido') AS T(C);

From Table to XML (FOR XML)

Just like with JSON, we can export results in XML format.

SELECT ClienteID as "@ID", -- Generates an attribute
       Nombre as "Datos/Nombre", -- Generates sub-elements
       Email as "Datos/Email"
FROM Clientes
FOR XML PATH('Cliente'), ROOT('Empresa');