Backend databases are more than just tables and columns in today's API-driven applications. Complex hierarchical data, which is typically represented as JSON or XML, must be sent and received by APIs.

Because SQL Server natively supports both JSON and XML formats, developers can:

• Data from API requests can be easily parsed into tables.
• Provide structured answers without requiring extensive application-layer change.

In order to enhance efficiency, lower code complexity, and streamline your API integration—particularly when utilizing ASP.NET Core or Web API—we'll look at how to effectively integrate JSON and XML inside SQL Server.

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.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, 'Peter',
N'{
  "OrderNumber": "ORD123",
  "Items": [
    {"Product": "Keyboard", "Qty": 2, "Price": 450},
    {"Product": "Mouse", "Qty": 1, "Price": 250}
  ],
  "Total": 1150
}');

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

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

3.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": "Peter",
      "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.

4. 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).

4.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>');

4.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);

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

5. JSON vs XML — Which to Choose?

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.

10. Best Practices Summary

11. Summary and Conclusion

Integration complexity decreased.

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.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.