SQL Server: Stored Procedures for XML Documents

Untyped XML is well-formed XML that is represented by an xml data type. Untyped xml columns and variables are defined with the built-in xml data type. Untyped XML is not validated in any other way unless DBA/Developer includes special code to do so. For example, the following Transact-SQL code creates a table that contains a column, OrderDetail, which contains untyped XML:

CREATE TABLE MyXMLOrderTable
(OrderID int NOT NULL PRIMARY KEY, OrderDetail xml, LoadDate datetime);

Untyped XML is used when DBA/Developer need to load XML that does not conform to its specified schema, DBA/Developer does not have defined schemas, or the XML has already been validated and DBA/Developer does not need to perform validation on the server. DBA/Developer should not create a schema collection before importing the XML documents. Schema collections are used to validate XML. In this scenario, the XML is validated by a client application. Therefore, this task does not need to be performed on the database server. DBA/Developer should not use FILESTREAM storage for the XML documents. In this scenario, DBA/Developer wanted to store the XML documents in the database. FILESTREAM storage is used to store data outside the database on the file system. DBA/Developer should not use typed XML when storing the XML documents because in this scenario, DBA/Developer did not need to perform validation. Typed XML can be used if XML needs to validated, such as if DBA/Developer needs to validate that the XML has valid data types and values for specific elements and attributes. To create a typed xml column or variable, DBA/Developer must first use the CREATE XML SCHEMA COLLECTION statement to create a schema used to validate the XML. Then, DBA/Developer can use the schema collection when declaring an xml variable or creating an xml column in a table. DBA/Developer can also use an existing XSD when DBA/Developer creates the schema collection, if one exists.