Azure / Azure/data-api-builder
[Enhancement]: Support XML data type for MSSQL
- Dominant language
- C#
- Stars
- 1.5k
- Forks
- 370
- Avg merge
- 3d 17h
- Merged PRs (30d)
- 8
Description
# 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.
```sql
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.
```sql
SELECT id, metadata FROM documents FOR JSON AUTO;
```
Returns:
```json
[
{
"id": 1,
"metadata": "reportadmin"
}
]
```
## Inserting XML
```sql
INSERT INTO documents (id, metadata)
VALUES (
1,
'reportadmin'
);
```
* 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:
```json
{
"value": [
{
"id": 1,
"metadata": "reportadmin"
}
]
}
```
### Mutation operations
Whether creating or updating, input must be a string containing valid XML:
```http
POST /documents
Content-Type: application/json
{
"id": 2,
"metadata": "memoguest"
}
```
### 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.
Contributor guide
Assessment
This issue has not been assessed yet.