XML in Databases

Databases store and query XML in two ways — native XML databases that treat XML as a first-class data type, and relational databases that add XML support alongside traditional tables. XML in databases is common in document management, enterprise content management, and legacy system integration.

Storing XML in Relational Databases

Most relational databases support an XML data type for columns. You store the full XML document in one column and use built-in XML query functions to query inside it.

SQL Server — XML Column

CREATE TABLE Orders (
  OrderID    INT PRIMARY KEY,
  OrderDate  DATE,
  OrderData  XML
);

INSERT INTO Orders VALUES (
  1,
  '2024-03-15',
  '<order>
    <customer>Rahul Sharma</customer>
    <total>2500</total>
  </order>'
);

-- Query XML data using XQuery in SQL Server
SELECT
  OrderID,
  OrderData.value('(/order/customer)[1]', 'VARCHAR(100)') AS CustomerName,
  OrderData.value('(/order/total)[1]', 'DECIMAL(10,2)')   AS OrderTotal
FROM Orders;

PostgreSQL — xml Type

CREATE TABLE Documents (
  DocID   SERIAL PRIMARY KEY,
  Content XML
);

INSERT INTO Documents (Content) VALUES (
  '<doc><title>XML Report</title><author>Meena</author></doc>'
);

-- Extract with xpath function
SELECT xpath('/doc/title/text()', Content) FROM Documents;

Native XML Databases

Native XML databases store XML documents as their primary format. They use XPath and XQuery for retrieval — no SQL needed.

XQuery in a Native XML Database

(: Query all orders over 2000 in BaseX :)
for $order in collection("orders")/order
where $order/total > 2000
order by $order/total descending
return
  <highValueOrder>
    <customer>{ $order/customer/text() }</customer>
    <total>{ $order/total/text() }</total>
  </highValueOrder>

Key Points to Remember

• Relational databases store XML in a dedicated xml column type.
• SQL Server uses .value(), .query(), and .exist() methods on XML columns.
• PostgreSQL uses the xpath() function to query XML stored in a column.
• Native XML databases treat entire XML documents as their data unit and use XQuery for retrieval.
• BaseX and eXist-db are popular open-source native XML databases.