You currently administer a database on an instance of SQL Server 2008. Your Prod database contains a Project table as shown:
The MaterialList column stores XML documents that represent the materials required to complete each project. The material lists are compiled and automatically transmitted by project managers in the field using a third-party application, and loaded into the database programmatically.
As each material list is added to the Project table, you want to ensure that certain elements within the XML conform to the following criteria:
Each material in the material list must have values for the product and stock number elements.
Each material in the material list must have a numeric quantity value.
Each material in the material list must have a value representing the date the item is required at the job site.
Which action should you take?
A. Use typed XML.
B. Use untyped XML.
C. Store the incoming XML in a staging table containing needed constraints.
D. Store the incoming XML into columns in a temporary table to perform validation.
Correct Answer: A
Explanation/Reference:
Typed XML can be used if XML needs to validated, such as if you need to validate that the XML has valid data types and values for specific elements and attributes.
To create a typed xml column or variable, you must first use the CREATE XML SCHEMA COLLECTION statement to create a schema used to validate the XML.
Then, you can use the schema collection when declaring an xml variable or creating an xml column in a table. You can also use an existing XSD when you create the schema collection, if one exists.
You should not use untyped XML. 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 you include special code to do so. Untyped XML is used when you need to load XML that does not conform to a specified schema, you do not have a defined schema, or the XML has already been validated and you do not need to perform validation on the server. In this scenario, you wanted the XML to be validated. Therefore, you should use typed XML.
You should not store the incoming XML into columns in a temporary table to perform validation, or store the incoming XML in a staging table containing needed constraints. In this scenario, you can continue to store the XML in the existing column and perform the required validation. There is no need to create a temporary or staging table that requires intermediate processing.
