[Enhancement]: Support XML data type for MSSQL

[JerryNixon]

XML data type

In SQL Server and Azure SQL Database, the XML data type stores structured XML content. Unlike the JSON data type, XML has been supported since SQL Server 2005 and includes native indexing, query, and validation capabilities.

CREATE TABLE documents (
    id INT PRIMARY KEY,
    metadata XML  -- a native XML document
)

FOR JSON support

When used with FOR JSON, SQL Server will emit the XML content as a string, not a parsed structure. This is because XML is not directly translatable to JSON without transformation.

SELECT id, metadata FROM documents FOR JSON AUTO;

Returns:

[
  {
    "id": 1,
    "metadata": "<doc><type>report</type><author>admin</author></doc>"
  }
]

Inserting XML

INSERT INTO documents (id, metadata)
VALUES (
    1,
    '<doc><type>report</type><author>admin</author></doc>'
);

• The XML must be passed as a valid string literal.
• SQL Server will parse and validate the XML at runtime.
• You can define an XML schema collection for strict validation if needed.

Data API builder behavior

Data API builder (DAB) should treat the XML column as a string in both directions.

Query operations

DAB will expose XML values as string content in REST responses:

{
  "value": [
    {
      "id": 1,
      "metadata": "<doc><type>report</type><author>admin</author></doc>"
    }
  ]
}

Mutation operations

Whether creating or updating, input must be a string containing valid XML:

POST /documents
Content-Type: application/json

{
  "id": 2,
  "metadata": "<doc><type>memo</type><author>guest</author></doc>"
}

XML Considerations

1. SQL Server will validate the string as well-formed XML before storing.
2. DAB does not parse or format XML—it treats the field as a text payload.
3. XML schema collections can be applied to restrict content if required.

[JerryNixon]

Basically, treat it as string.