| title | XML Data Modification Language (XML DML) | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| description | XML Data Modification Language (XML DML) | ||||||||
| author | MikeRayMSFT | ||||||||
| ms.author | mikeray | ||||||||
| ms.date | 03/16/2017 | ||||||||
| ms.service | sql | ||||||||
| ms.subservice | t-sql | ||||||||
| ms.topic | reference | ||||||||
| ms.custom |
|
||||||||
| helpviewer_keywords |
|
||||||||
| dev_langs |
|
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]
The XML Data Modification Language (XML DML) is an extension of the XQuery language. As defined by W3C, the XQuery language lacks the Data Manipulation (DML) part. The XML DML introduced in this topic, and also the XQuery language, provides a fully functional query and data-modification language that you can use against the xml data type.
The XML DML adds the following case-sensitive keywords to XQuery:
-
insert
-
delete
-
replace value of
As described in XML Data Type and Columns (SQL Server), you can create variables and columns of the xml type and assign XML documents or fragments to them. To modify or update these XML instances, do the following:
-
Use the modify() Method xml Data Type) of the xml data type.
-
Specify the appropriate XML DML statements inside the modify() method.
Note that there are some attributes that cannot be inserted, deleted, or have their value modified. For example:
-
For typed or untyped xml, the attributes are xmlns, xmlns:*, and xml:base.
-
For typed xml only, the attributes are xsi:nil, and xsi:type.
Other restrictions include the following:
-
For typed or untyped xml, inserting the attribute xml:base will fail.
-
For typed xml, deleting and modifying the xsi:nil attribute will fail. For untyped xml, you can delete the attribute or modify its value.
-
For typed xml, modifying the value of the xs:type attribute will fail. For untyped xml, you can modify the attribute value.
When you modify a typed XML instance, the final format must be a valid instance of that type. Otherwise, a validation error is returned.
insert (XML DML)
delete (XML DML)
replace value of (XML DML)
Compare Typed XML to Untyped XML
Create Instances of XML Data
xml Data Type Methods