Go Back

Use Case - Product Catalog

XML in SQL Server

By storing the attributes within an XML column in MS SQL Server, the attributes can be stored with dynamic schema.

We can create a table with an XML column type like this...

CREATE TABLE ProductType (
    ID INT IDENTITY PRIMARY KEY,
    Type NVARCHAR(30) NOT NULL,
);

CREATE TABLE product (
    ID INT IDENTITY PRIMARY KEY,
    Name NVARCHAR(30) NOT NULL,
    TypeID INT REFERENCES ProductType(ID),
    Info XML
);
GO         
        

We can then insert data in XML format.

INSERT INTO product (
    Name, 
    TypeID, 
    Info
) VALUES(
    'Hawaiian Pizza (Small, Pan)', 
    1,
    '<Specifications Size="Small" Crust="Pan"/>'
);
GO
        

When the application retrieves the XML data in the Info column, it would then parse the XML data with a schema dependent on the type of product.

Fetching the values within SQL and filtering the data based on the XML data is possible using XQuery

SELECT 
    ID,
    Name,
    Info.value('(/Specifications/@Size)[0]', 'NVARCHAR(30)') AS Size,
    Info.value('(/Specifications/@Crust)[0]', 'NVARCHAR(30)') AS Crust
FROM product;
GO

SELECT 
    ID,
    Name,
    Info
FROM product
WHERE
    Info.exist('/Specifications/@Size[.="1.5"]') = 1
;
GO
        

To facilitate queries on the XML data, an XML index can be created

CREATE PRIMARY XML INDEX idx_product_info ON product(info);  -- A primary XML index is for all tags, paths and values

-- Optional: Secondary indices can be created to further optimize querying
CREATE XML INDEX idx_product_info_path ON product(info) USING XML INDEX idx_product_info FOR PATH;
CREATE XML INDEX idx_product_info_path ON product(info) USING XML INDEX idx_product_info FOR VALUE;
CREATE XML INDEX idx_product_info_path ON product(info) USING XML INDEX idx_product_info FOR PROPERTY;
        

XML columns can be associated with a schema.

        
CREATE XML SCHEMA COLLECTION exampleSchema AS '
<schema>
    <element name="root" />
</schema>
';
GO

CREATE TABLE tableWithXml (xmlCol XML(exampleSchema));
GO
        
    

SQL Server also makes it possible to change specific elements or attributes within the XML.

        
-- Change: Small pizzas will only be available with a Pan crust
UPDATE product
SET Info.modify('replace value of (/Specifications/@Crust)[1] with "Pan"')
WHERE Info.value('(/Specifications/@Size)[1]', 'nvarchar(32)') = 'Small';
GO

--Add Price and MaxOrderQty
UPDATE product
SET Info.modify('
insert 
(
    attribute Price {"14.95"},
    attribute MaxOrderQty {"10"},
)
into (/Specifications)[1]
')
WHERE Info.value('(/Specifications/@Size)[1]', 'nvarchar(32)') = 'Small';
GO
        
    

Go Back