What is XML
XML (eXtensible Markup Language) is used to structure data with tags. It’s human-readable and machine-readable, making it common in older systems, web services, and document formats.
How It Works
XML documents nest elements (tags) to represent hierarchical structures. Attributes store extra information. XML parsers validate the syntax, and schemas (DTD or XSD) set rules for elements and data types.
Technical Details
XML can be more verbose than JSON, but it allows custom tag names and advanced validation. XSLT can transform XML into various formats. Many older APIs still rely on XML, though JSON has become more popular.
How to Write It
Basic Syntax
-- Example: Working with XML in SQL
-- 1. Create table with an XML column
CREATE TABLE product_catalog (
id SERIAL PRIMARY KEY,
metadata XML
);
-- 2. Insert XML data
INSERT INTO product_catalog (metadata) VALUES (
'<product>
<name>Laptop</name>
<specs>
<cpu>Intel i7</cpu>
<ram>16GB</ram>
<storage>512GB SSD</storage>
</specs>
<price currency="USD">999.99</price>
</product>'
);
-- 3. Query XML data (SQL Server syntax as an example)
SELECT
id,
metadata.value('(/product/name)[1]', 'VARCHAR(100)') AS product_name,
metadata.value('(/product/specs/cpu)[1]', 'VARCHAR(50)') AS cpu,
metadata.value('(/product/price/@currency)[1]', 'VARCHAR(3)') AS currency,
metadata.value('(/product/price)[1]', 'DECIMAL(10,2)') AS price
FROM product_catalog;
-- 4. Update XML
UPDATE product_catalog
SET metadata.modify('
replace value of (/product/price/text())[1]
with "1099.99"
')
WHERE id = 1;
-- 5. Search in XML
SELECT id
FROM product_catalog
WHERE metadata.exist('/product/specs/ram[text()="16GB"]') = 1;
-- 6. Generate XML from relational data
SELECT
product_id,
name,
category,
price
FROM products
FOR XML PATH('product'), ROOT('catalog');Supported Platforms
MySQL
Basic XML functions like ExtractValue() and UpdateXML(), though limited compared to other engines.
PostgreSQL
Robust XML functions and operators for advanced queries and transformations.
SQL Server
Native XML data type, indexing, and strong XQuery support.
Learn More
Best Practices
- Use clear, consistent tag names and attributes
- Validate XML against a schema (XSD or DTD) when possible
- Keep nesting logical and avoid unnecessary complexity
Common Pitfalls
- Tangled hierarchies that make parsing difficult
- Mixing data structure with presentation in the same XML
- Ignoring special characters or entity references
Advanced Tips
- Use XPath or XQuery for more powerful searching
- Leverage XSLT to transform XML into other formats (e.g., HTML)
- Use streaming parsers (SAX) for very large XML files to save memory
