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:
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:
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.