SQL XML Data Type

The XML data type is used to store unstructured XML data.

XML allows you to store XML objects and create XML indexes on columns.

This data type validates the input values when assigning it to an XML column or variable.

Example

#

This example assigns and return an XML value.

DECLARE @CustomerXML XML = '';
SET @CustomerXML = 
'<customer>
  <firstname type="textbox">Harold</firstname>
  <lastname type="textbox">Smith</lastname>
  <address type="textbox">3526 Sherman Street Lawrence, Kansas</address>
  <phone type="textbox">265 558 8799</phone>
</customer>';
    
SELECT @CustomerXML as CustomerXML;
Result:  1 record
CustomerXML
<customer>
  <firstname type="textbox">Harold</firstname>
  <lastname type="textbox">Smith</lastname>
  <address type="textbox">3526 Sherman Street Lawrence, Kansas</address>
  <phone type="textbox">265 558 8799</phone>
</customer>

More Examples

XML with ERROR VALIDATION

An XML value is assigned but it fails validation and returns an error.
DECLARE @CustomerXML XML = '';
SET @CustomerXML = 
'<customer>
  <firstname type="textbox">Harold</firstname>
  <lastname type="textbox">Smith</lastname>
  <address type="textbox">3526 Sherman Street Lawrence, Kansas
  <phone type="textbox">265 558 8799</phone>
</customer>';
    
SELECT @CustomerXML as CustomerXML;
Result:
Msg 9436, Level 16, State 1, Line 2
XML parsing: line 6, character 11, end tag does not match start tag

The XML value returns an error because the address tag does not have a closing tag.


You may also like



Guides


vsn 3.1