Using JSON and XML Data Efficiently in SQL Server for APIs

1. Introduction

In today’s API-driven systems, backend databases are no longer just about tables and columns.
APIs often need to send and receive complex hierarchical data, which is naturally represented as JSON or XML.

SQL Server supports both JSON and XML formats natively, allowing developers to easily:

Parse API request data directly into tables.
Return structured responses without heavy transformation in the application layer.

In this article, we’ll explore how to use JSON and XML efficiently inside SQL Server to improve performance, reduce code complexity, and simplify your API integration — especially when using ASP.NET Core or Web API.

2. Why JSON and XML Matter for APIs

Most modern APIs use JSON (JavaScript Object Notation) for data exchange because it’s lightweight and human-readable.
However, many legacy systems and enterprise integrations still rely on XML for structured documents, invoices, and configurations.

SQL Server supports both formats, which means you can:

Store JSON/XML data directly in database columns.
Parse and query nested data.
Return dynamic API responses without looping in C#.

Here’s what it enables in real-world projects:

ASP.NET Core API receives JSON → stored procedure handles JSON directly.
Stored procedure returns JSON → API sends it back without re-serialization.

3. Technical Workflow Flowchart

Below is the technical workflow showing how data moves between an API and SQL Server when using JSON/XML efficiently:

   ┌─────────────────────────────┐
   │   Client (Angular / React)  │
   │   Sends JSON payload        │
   └──────────────┬──────────────┘
                  │
                  ▼
      ┌──────────────────────────┐
      │ ASP.NET Core Web API     │
      │ Receives JSON/XML input  │
      └──────────────┬───────────┘
                     │
                     ▼
        ┌─────────────────────────────┐
        │  SQL Server Stored Procedure│
        │  Accepts NVARCHAR(MAX) JSON │
        │  Parses using OPENJSON/XML  │
        └──────────────┬──────────────┘
                       │
                       ▼
           ┌──────────────────────────────┐
           │ Perform CRUD Operations       │
           │ using parsed structured data  │
           └──────────────┬───────────────┘
                          │
                          ▼
            ┌────────────────────────────┐
            │ Return data as JSON or XML │
            │ using FOR JSON / FOR XML   │
            └────────────────────────────┘
                          │
                          ▼
             ┌──────────────────────────┐
             │ Web API sends response   │
             │ back to client           │
             └──────────────────────────┘

4. Working with JSON in SQL Server

SQL Server (2016 and later) has built-in support for JSON operations.
Let’s start with the most useful functions and patterns.

4.1 Storing JSON Data

You can store JSON as plain text in an NVARCHAR(MAX) column:

CREATE TABLE CustomerOrders (
    OrderId INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    OrderData NVARCHAR(MAX) -- stores JSON
);

Example JSON data

{"OrderNumber": "ORD123","Items": [
    { "Product": "Keyboard", "Qty": 2, "Price": 450 },
    { "Product": "Mouse", "Qty": 1, "Price": 250 }],"Total": 1150}

Insert JSON into the table

INSERT INTO CustomerOrders (OrderId, CustomerName, OrderData)
VALUES (1, 'Rajesh Gami', 
N'{
  "OrderNumber": "ORD123",
  "Items": [
    {"Product": "Keyboard", "Qty": 2, "Price": 450},
    {"Product": "Mouse", "Qty": 1, "Price": 250}
  ],
  "Total": 1150
}');

4.2 Reading JSON Values

Use JSON_VALUE() to extract a scalar value.

SELECT 
  JSON_VALUE(OrderData, '$.OrderNumber') AS OrderNumber,
  JSON_VALUE(OrderData, '$.Total') AS TotalAmount
FROM CustomerOrders;

Output

OrderNumber | TotalAmount
------------|------------
ORD123      | 1150

4.3 Parsing Arrays with OPENJSON

Use OPENJSON() to split array elements into rows.

SELECT 
  JSON_VALUE(OrderData, '$.OrderNumber') AS OrderNumber,
  Item.value('Product', 'nvarchar(50)') AS ProductName,
  Item.value('Qty', 'int') AS Quantity,
  Item.value('Price', 'decimal(10,2)') AS UnitPrice
FROM CustomerOrders
CROSS APPLY OPENJSON(OrderData, '$.Items')
WITH (
    Product NVARCHAR(50) '$.Product',
    Qty INT '$.Qty',
    Price DECIMAL(10,2) '$.Price'
) AS Item;

This query expands nested arrays into tabular data — ideal for APIs that send product line items.

4.4 Returning JSON from SQL Server

Instead of letting your ASP.NET Core app serialize the data, you can return it as JSON directly:

SELECT 
  OrderId,
  CustomerName,
  OrderData
FROM CustomerOrders
FOR JSON PATH, ROOT('Orders');

Output

{"Orders": [
    {
      "OrderId": 1,
      "CustomerName": "Rajesh Gami",
      "OrderData": {
        "OrderNumber": "ORD123",
        "Items": [
          { "Product": "Keyboard", "Qty": 2, "Price": 450 },
          { "Product": "Mouse", "Qty": 1, "Price": 250 }
        ],
        "Total": 1150
      }
    }]}

This JSON can be sent directly to your API response — saving time and CPU cycles in .NET.

5. Working with XML in SQL Server

XML support in SQL Server has been around since 2005.
It’s still widely used in enterprise-level systems or integrations (e.g., financial, logistics, or government APIs).

5.1 Storing XML Data

CREATE TABLE VendorInvoices (
    InvoiceId INT PRIMARY KEY,
    VendorName NVARCHAR(100),
    InvoiceData XML
);

Example XML

<Invoice>
  <Number>INV-999</Number>
  <Date>2025-11-05</Date>
  <Items>
    <Item>
      <Name>SSD Drive</Name>
      <Qty>2</Qty>
      <Price>3200</Price>
    </Item>
    <Item>
      <Name>RAM 16GB</Name>
      <Qty>1</Qty>
      <Price>5500</Price>
    </Item>
  </Items>
</Invoice>

Insert XML

INSERT INTO VendorInvoices (InvoiceId, VendorName, InvoiceData)
VALUES (1, 'TechVendor Pvt Ltd',
N'<Invoice>
    <Number>INV-999</Number>
    <Date>2025-11-05</Date>
    <Items>
      <Item><Name>SSD Drive</Name><Qty>2</Qty><Price>3200</Price></Item>
      <Item><Name>RAM 16GB</Name><Qty>1</Qty><Price>5500</Price></Item>
    </Items>
</Invoice>');

5.2 Querying XML with XQuery

You can extract elements using .value() and .nodes().

SELECT 
  InvoiceData.value('(/Invoice/Number)[1]', 'nvarchar(50)') AS InvoiceNumber,
  InvoiceData.value('(/Invoice/Date)[1]', 'date') AS InvoiceDate
FROM VendorInvoices;

Parsing nested XML arrays:

SELECT 
  I.InvoiceId,
  Items.value('(Name)[1]', 'nvarchar(50)') AS ProductName,
  Items.value('(Qty)[1]', 'int') AS Quantity,
  Items.value('(Price)[1]', 'decimal(10,2)') AS Price
FROM VendorInvoices I
CROSS APPLY InvoiceData.nodes('/Invoice/Items/Item') AS T(Items);

5.3 Returning XML Responses

SELECT 
  InvoiceId,
  VendorName,
  InvoiceData
FROM VendorInvoices
FOR XML PATH('Invoice'), ROOT('Invoices');

Output

<Invoices>
  <Invoice>
    <InvoiceId>1</InvoiceId>
    <VendorName>TechVendor Pvt Ltd</VendorName>
    <InvoiceData>
      <Invoice>
        <Number>INV-999</Number>
        ...
      </Invoice>
    </InvoiceData>
  </Invoice>
</Invoices>

6. JSON vs XML — Which to Choose?

Feature	JSON	XML
Format	Lightweight, human-readable	Verbose but structured
Best For	Web APIs, REST, mobile apps	Legacy systems, SOAP, enterprise
SQL Server Support	From 2016+	Since 2005
Parsing Functions	`OPENJSON`, `JSON_VALUE`, `JSON_QUERY`	`.nodes()`, `.value()`, `.exist()`
Schema Validation	No	Yes (XSD support)
Performance	Faster for APIs	Slightly slower due to verbosity

Recommendation

For modern APIs → Prefer JSON.
For legacy or B2B integrations → Use XML.
SQL Server can handle both — even in the same stored procedure.

7. Combining JSON + ASP.NET Core API

Here’s an example of a simple Web API endpoint calling a SQL stored procedure that accepts JSON.

Stored Procedure

CREATE PROCEDURE usp_SaveOrder
    @OrderJson NVARCHAR(MAX)
ASBEGIN
    INSERT INTO CustomerOrders (CustomerName, OrderData)
    SELECT JSON_VALUE(@OrderJson, '$.CustomerName'),
           @OrderJson;
END

ASP.NET Core Controller

[HttpPost("api/orders")]
public async Task<IActionResult> SaveOrder([FromBody] JsonElement orderJson)
{
    string jsonData = orderJson.ToString();

    using var conn = new SqlConnection(_config.GetConnectionString("Default"));
    using var cmd = new SqlCommand("usp_SaveOrder", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@OrderJson", jsonData);

    await conn.OpenAsync();
    await cmd.ExecuteNonQueryAsync();
    return Ok("Order saved successfully");
}

Result

Frontend sends JSON → stored procedure parses it → no mapping issues or downtime.

8. Performance Tips for JSON/XML in SQL Server

Use NVARCHAR(MAX) for JSON columns — it performs better than TEXT or NTEXT.
Validate JSON using ISJSON() before processing
```
WHERE ISJSON(OrderData) = 1
```

Create computed columns from JSON for indexing

ALTER TABLE CustomerOrders
ADD OrderNumber AS JSON_VALUE(OrderData, '$.OrderNumber') PERSISTED;
CREATE INDEX IX_OrderNumber ON CustomerOrders(OrderNumber);

For XML, use typed XML with XSD schema for faster querying.
Avoid over-storing massive documents — keep only necessary data portions.
Return compact results using FOR JSON PATH, WITHOUT_ARRAY_WRAPPER when the response is a single object.

9. Real-World Use Case

A logistics company integrated multiple systems (fleet tracking, billing, and customer portal).
They used ASP.NET Core + SQL Server to unify data exchange.

Earlier, the application layer transformed large JSON payloads before inserting them into SQL tables — wasting time.
After moving the parsing logic into SQL stored procedures using OPENJSON,

API response time reduced by 30%.
CPU usage on app servers dropped.
Integration complexity decreased.

10. Best Practices Summary

Area	Best Practice
Storage	Use NVARCHAR(MAX) for JSON, XML type for XML
Validation	Always validate JSON/XML before processing
Performance	Use computed columns for indexing JSON fields
Security	Sanitize input data before dynamic SQL usage
API Integration	Let SQL handle JSON serialization using `FOR JSON`
Maintainability	Keep JSON structures consistent with DTO models

11. Summary and Conclusion

SQL Server’s built-in JSON and XML features let you build cleaner, faster APIs with less code.

By using:

OPENJSON() and FOR JSON for modern REST APIs
.nodes() and FOR XML for structured enterprise data
you can directly work with hierarchical data inside SQL Server without extra transformations in C# or middleware.

This approach ensures:
✅ Faster API responses
✅ Reduced serialization overhead
✅ Cleaner, maintainable backend logic

In short

If your APIs and database speak the same language — JSON or XML — your system becomes leaner, faster, and easier to evolve.