Can my DB?

-- Store XML in a column
CREATE TABLE documents (
  id      SERIAL PRIMARY KEY,
  content xml
);

-- Insert XML
INSERT INTO documents (content)
VALUES ('<book><title>SQL Guide</title><year>2024</year></book>');

-- XPath query
SELECT xpath('/book/title/text()', content) AS title
FROM documents;

-- Construct XML
SELECT xmlelement(name employee,
         xmlelement(name name, emp_name),
         xmlelement(name salary, salary)
       ) AS emp_xml
FROM employees;

-- Aggregate to XML
SELECT xmlagg(xmlelement(name row, id)) FROM documents;

• The xml type validates that content is well-formed XML; it does not validate against a schema.
• xpath() function returns an array of xml values matching the XPath expression.
• xmlelement(), xmlforest(), xmlroot(), xmlpi(), xmlcomment() construct XML from relational data.
• xmlagg() aggregates XML values.
• No XQuery support; only XPath 1.0.
• XMLTABLE is available for shredding XML into rows (added in PostgreSQL 10).

-- Store XML as TEXT
CREATE TABLE documents (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  content TEXT
);

-- Extract value with XPath
SELECT EXTRACTVALUE(content, '/book/title') AS title
FROM documents;

-- Update XML content
SELECT UPDATEXML(content, '/book/year', '<year>2025</year>') AS updated
FROM documents;

• No native XML data type; XML is stored as TEXT or LONGTEXT.
• EXTRACTVALUE(xml, xpath) extracts a single value using XPath.
• UPDATEXML(xml, xpath, new_xml) replaces a node.
• Limited XPath 1.0 support; no XQuery.
• No XMLTABLE or XML construction functions.

-- Store XML as TEXT
CREATE TABLE documents (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  content TEXT
);

-- Extract value with XPath
SELECT EXTRACTVALUE(content, '/book/title') AS title
FROM documents;

SELECT UPDATEXML(content, '/book/year', '<year>2025</year>') AS updated
FROM documents;

• No native XML data type; XML is stored as TEXT.
• EXTRACTVALUE() and UPDATEXML() provide limited XPath-based manipulation.
• Same limited XML function set as MySQL.
• No XMLTABLE or XQuery support.

-- XMLType column
CREATE TABLE documents (
  id      NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  content XMLType
);

-- Insert XML
INSERT INTO documents (content)
VALUES (XMLType('<book><title>SQL Guide</title><year>2024</year></book>'));

-- XQuery / XPath extraction
SELECT content.extract('/book/title/text()').getStringVal() AS title
FROM documents;

-- XMLTABLE: shred XML into relational rows
SELECT x.title, x.year
FROM documents d,
     XMLTABLE('/book' PASSING d.content
       COLUMNS
         title VARCHAR2(100) PATH 'title',
         year  NUMBER        PATH 'year'
     ) x;

-- Construct XML
SELECT XMLElement("employee",
         XMLForest(emp_name AS "name", salary AS "salary")
       ) AS emp_xml
FROM employees;

-- Return query as XML
SELECT XMLAgg(XMLElement("row", id)) FROM documents;

• XMLType stores XML natively; Oracle XML DB provides XQuery, XPath, XML Schema validation, and XML indexing.
• XMLTABLE() shreds XML documents into relational rows using XQuery.
• XMLElement(), XMLForest(), XMLAgg(), XMLRoot() construct XML from relational data.
• XMLIndex provides fine-grained indexing of XML content.
• Oracle supports XML Schema registration and schema-based XML validation.
• Very mature XML support predating JSON features by over a decade.

-- xml column
CREATE TABLE documents (
  id      INT IDENTITY(1,1) PRIMARY KEY,
  content xml
);

-- Insert XML
INSERT INTO documents (content)
VALUES ('<book><title>SQL Guide</title><year>2024</year></book>');

-- XQuery methods on xml type
SELECT
  content.value('(/book/title)[1]', 'NVARCHAR(100)') AS title,
  content.exist('/book[year=2024]') AS is_2024,
  content.query('/book/title') AS title_xml
FROM documents;

-- OPENXML: shred XML into relational rows
DECLARE @xml XML = '<books><book><title>A</title></book><book><title>B</title></book></books>';
DECLARE @hdoc INT;
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;
SELECT * FROM OPENXML(@hdoc, '/books/book', 2) WITH (title NVARCHAR(100));
EXEC sp_xml_removedocument @hdoc;

-- Return query as XML using FOR XML
SELECT id, title FROM books FOR XML PATH('book'), ROOT('books');

• The xml type stores well-formed XML or XML fragments.
• XQuery methods: .value() extracts a scalar, .query() returns xml, .exist() returns 0/1, .nodes() shreds into rows, .modify() updates in place.
• FOR XML clause (AUTO, PATH, RAW, EXPLICIT) returns relational query results as XML.
• OPENXML shreds an XML document into a relational rowset.
• XML indexes (primary and secondary) can be created on xml columns for efficient XQuery execution.
• XML Schema Collections allow typed xml columns with schema validation.

-- No XML support. Store as TEXT and parse in application code.
CREATE TABLE documents (
  id      INTEGER PRIMARY KEY,
  content TEXT
);

• No native XML type or XML functions.
• Store XML as TEXT and parse in the application layer.

-- No XML support. Store as VARCHAR/TEXT.
CREATE TABLE documents (
  id      INTEGER IDENTITY(1,1) PRIMARY KEY,
  content VARCHAR(MAX)
);

• No XML data type or XML functions.
• Store XML as VARCHAR or use Amazon S3 with AWS Glue or Lambda for XML preprocessing.

-- XML column using pureXML
CREATE TABLE documents (
  id      INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  content XML
);

-- Insert XML
INSERT INTO documents (content)
VALUES (XMLPARSE(DOCUMENT '<book><title>SQL Guide</title><year>2024</year></book>'));

-- XMLQUERY: XQuery extraction
SELECT XMLQUERY('$d/book/title' PASSING content AS "d" RETURNING CONTENT) AS title
FROM documents;

-- XMLTABLE: shred XML into relational rows
SELECT x.title, x.year
FROM documents d,
     XMLTABLE('$d/book' PASSING d.content AS "d"
       COLUMNS
         title VARCHAR(100) PATH 'title',
         year  INTEGER      PATH 'year'
     ) AS x;

-- Serialize XML back to text
SELECT XMLSERIALIZE(content AS VARCHAR(1000)) FROM documents;

• DB2's pureXML stores XML natively in a parsed, hierarchical format for efficient XQuery execution.
• XMLPARSE() parses a string into an XML value; XMLSERIALIZE() converts back.
• XMLQUERY() evaluates XQuery expressions against XML data.
• XMLTABLE() shreds XML into relational rows using XQuery.
• XML indexes can be created on specific XPath paths within XML columns.
• Supports XML Schema validation via XMLVALIDATE().

-- Limited XML parsing via PARSE_XML into VARIANT
SELECT PARSE_XML('<book><title>SQL Guide</title></book>') AS xml_val;

-- Access elements (returns VARIANT)
SELECT
  PARSE_XML('<book><title>SQL Guide</title></book>') :title :: STRING AS title;

• No native XML data type.
• PARSE_XML() parses an XML string into a VARIANT (semi-structured) value.
• XML access via VARIANT dot-notation is limited compared to XPath/XQuery.
• For complex XML processing, use external functions or preprocess XML before loading into Snowflake.

-- No XML support. Store as STRING.
CREATE TABLE dataset.documents (
  id      INT64,
  content STRING
);

-- Parse XML in application code or use Cloud Dataflow for preprocessing.

• No XML data type or XML functions.
• Store XML as STRING and parse in application code.
• Use Cloud Dataflow or Cloud Data Fusion for XML-to-JSON transformation at ingestion time.