JSON, XML and Special Data Types

27.1 JSON (2016+; native JSON type in 2025)

DECLARE @j NVARCHAR(MAX) = N'{"id":1,"name":"Asha","tags":["vip","new"],"address":{"city":"Pune"}}';

SELECT ISJSON(@j),
       JSON_VALUE(@j, '$.name'),            -- scalar
       JSON_QUERY(@j, '$.tags'),            -- object/array
       JSON_VALUE(@j, '$.address.city'),
       JSON_MODIFY(@j, '$.name', 'Ravi'),
       JSON_PATH_EXISTS(@j, '$.address');   -- 2022+

-- Shred JSON to rows
SELECT * FROM OPENJSON(@j, '$.tags');
SELECT * FROM OPENJSON(N'[{"id":1,"qty":2},{"id":3,"qty":5}]')
WITH (id INT '$.id', qty INT '$.qty');

-- Produce JSON
SELECT CustomerID, FirstName, LastName FROM dbo.Customers FOR JSON PATH, ROOT('customers');
SELECT c.CustomerID, c.FirstName,
       (SELECT o.OrderID, o.OrderDate FROM dbo.Orders o WHERE o.CustomerID = c.CustomerID FOR JSON PATH) AS Orders
FROM dbo.Customers c FOR JSON PATH;

-- JSON constructors and aggregates (2022+/2025)
SELECT JSON_OBJECT('id': 1, 'name': 'Asha'), JSON_ARRAY(1, 2, 3);
SELECT JSON_OBJECTAGG(CustomerID : FirstName), JSON_ARRAYAGG(FirstName) FROM dbo.Customers;

-- Native JSON type and index (2025)
CREATE TABLE dbo.Events (EventID INT PRIMARY KEY, Payload JSON);
CREATE JSON INDEX IX_Events_Payload ON dbo.Events (Payload) F
…

27.2 XML

DECLARE @x XML = N'<order id="1"><item sku="A1" qty="2"/><item sku="B2" qty="1"/></order>';

SELECT @x.value('(/order/@id)[1]', 'INT'),
       @x.query('/order/item'),
       @x.exist('/order/item[@sku="A1"]');

SELECT i.value('@sku', 'VARCHAR(10)') AS Sku, i.value('@qty', 'INT') AS Qty
FROM @x.nodes('/order/item') AS T(i);

SET @x.modify('insert <item sku="C3" qty="4"/> into (/order)[1]');

-- Produce XML
SELECT CustomerID, FirstName FROM dbo.Customers FOR XML PATH('Customer'), ROOT('Customers');
-- FOR XML RAW | AUTO | EXPLICIT | PATH
-- Typed XML: XML SCHEMA COLLECTION; XML indexes: primary + secondary (PATH, VALUE, PROPERTY)
-- Classic string concatenation trick (pre-2017): STUFF((SELECT ',' + x FOR XML PATH('')),1,1,'')

27.3 Spatial Data

DECLARE @pune GEOGRAPHY = geography::Point(18.5204, 73.8567, 4326);
DECLARE @mumbai GEOGRAPHY = geography::Point(19.0760, 72.8777, 4326);
SELECT @pune.STDistance(@mumbai) / 1000 AS DistanceKm;
-- GEOMETRY (planar) vs GEOGRAPHY (ellipsoidal); spatial indexes; STIntersects, STBuffer, STContains

27.4 HIERARCHYID

CREATE TABLE dbo.Org (Node HIERARCHYID PRIMARY KEY, Lvl AS Node.GetLevel(), Name NVARCHAR(50));
INSERT dbo.Org VALUES (hierarchyid::GetRoot(), N'CEO');
-- GetAncestor(), GetDescendant(), IsDescendantOf(), ToString(), GetReparentedValue()

27.5 VECTOR Data Type and AI (SQL Server 2025)

Several 2025 AI features (vector index, VECTOR_SEARCH) are in preview and need PREVIEW_FEATURES database-scoped configuration. Check the current docs before relying on the syntax.

CREATE TABLE dbo.Docs (DocID INT PRIMARY KEY, Content NVARCHAR(MAX), Embedding VECTOR(1536));

-- Register an embedding model
CREATE EXTERNAL MODEL MyEmbeddingModel
WITH (LOCATION = 'https://<endpoint>/openai/deployments/text-embedding-3-small/embeddings',
      API_FORMAT = 'Azure OpenAI', MODEL_TYPE = EMBEDDINGS, MODEL = 'text-embedding-3-small',
      CREDENTIAL = [https://<endpoint>]);

UPDATE dbo.Docs SET Embedding = AI_GENERATE_EMBEDDINGS(Content USE MODEL MyEmbeddingModel);

-- Exact similarity search
DECLARE @q VECTOR(1536) = AI_GENERATE_EMBEDDINGS(N'refund policy' USE MODEL MyEmbeddingModel);
SELECT TOP (5) DocID, VECTOR_DISTANCE('cosine', Embedding, @q) AS Dist
FROM dbo.Docs ORDER BY Dist;

-- Approximate (ANN) search with a DiskANN vector index
CREATE VECTOR INDEX IX_Docs_Vec ON dbo.Docs(Embedding) WITH (METRIC = 'cosine', TYPE = 'diskann');
SELECT t.DocID, s.distance
FROM VECTOR_SEARCH(TABLE = dbo.Docs AS t, COLUMN = Embedding, SIMILAR_TO = @q,
                   METRIC = 'cosine', TOP_N = 5) AS s;

-- Text chunking for RAG
SELECT * FROM AI_GENERATE_CHUNKS(SOURCE = N'long text...', CHUNK_TYPE = FIXED, CHUNK_SIZE = 500);

27.6 Sparse Columns, FILESTREAM and FileTable

SPARSE: saves space for mostly-NULL columns.

FILESTREAM: stores VARBINARY(MAX) BLOBs on the NTFS file system.

FileTable: Windows-share access to files stored in SQL Server.