The Designing Database Tables module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:
- Design XML storage
- Design a strategy to query and modify XML data
- Design a query strategy by using FOR XML
- Design a strategy to transform XML into relational data
Exercise 1 - Design XML storage
Determining when to use XML for storage
If your data is highly structured with known schema, the relational model is likely to work best for data storage. SQL Server provides the required functionality and tools you may need. On the other hand, if the structure is semi-structured or unstructured, or unknown, you have to give consideration to modeling such data.
XML is a good choice if you want a platform-independent model in order to ensure portability of the data by using structural and semantic markup. Additionally, it is an appropriate option if some of the following properties are satisfied:
- Your data is sparse or you do not know the structure of the data, or the structure of your data may change significantly in the future.
- Your data represents containment hierarchy, instead of references among entities, and may be recursive.
- Order is inherent in your data.
- You want to query into the data or update parts of it, based on its structure.
If none of these conditions is met, you should use the relational data model. For example, if your data is in XML format but your application just uses the database to store and retrieve the data, an [n]varchar(max) column is all you require. Storing the data in an XML column has additional benefits. This includes having the engine determine that the data is well formed or valid, and also includes support for fine-grained query and updates into the XML data.
Following are some of the reasons to use native XML features in SQL Server instead of managing your XML data in the file system:
- You want to share, query, and modify your XML data in an efficient and transacted way. Fine-grained data access is important to your application. For example, you may want to extract some of the sections within an XML document, or you may want to insert a new section without replacing your whole document.
- You have relational data and XML data and you want interoperability between both relational and XML data within your application.
- You need language support for query and data modification for cross-domain applications.
- You want the server to guarantee that the data is well formed and also optionally validate your data according to XML schemas.
- You want indexing of XML data for efficient query processing and good scalability, and the use of a first-rate query optimizer.
- You want SOAP, ADO.NET, and OLE DB access to XML data.
- You want to use administrative functionality of the database server for managing your XML data. For example, this would be backup, recovery, and replication.
Untyped vs. typed (XML schema collection)
SQL Server supports two types of XML namely TYPED and UNTYPED.
A TYPED XML column or variable is bound to an XML schema that defines the structure of the XML that the variable or column will store. UNTYPED XML variable or column will store any XML value. However, a TYPED XML variable or column will store solely XML values with the precise structure outlined by the SCHEMA.
Just like a TABLE includes a schema that defines the columns, their information, data typeetc., the XML SCHEMA that is bound to a TYPED XML variable/column specifies the structure of the XML that it will store. Using TYPED XML will make your code more efficient as SQL Server has detailed knowledge about the structure of your XML column/variable.
Exercise 2 - Design a strategy to query and modify XML data
When to use appropriate XPath and XQuery expressions
.query vs. .value, XML indexes for performance
Function value returns the tag value desired in a tabular format which was not the case with the exist function. Before we use this function on the table Clothes we will first see how it works by executing it against a simple xml variable.
Exercise 3 - Design a query strategy by using FOR XML
By defining a mapping between your XML schemas and the tables in a database, you create an "XML view" of your persistent data. XML bulk load can be used to populate the underlying tables by using the XML view. You can query the XML view by using XPath version 1.0; the query is translated to SQL queries on the tables. Similarly, updates are also propagated to those tables.
This technology is useful in the following situations:
- You want to have an XML-centric programming model using XML views over your existing relational data.
- You have a schema (XSD, XDR) for your XML data that an external partner may have provided.
- Order is not important in your data, or your query table data is not recursive, or the maximal recursion depth is known in advance.
- You want to query and modify the data through the XML view by using XPath version 1.0.
- You want to bulk load XML data and decompose them into the underlying tables by using the XML view.
Exercise 4 - Design a strategy to transform XML into relational data
The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.
Every xml data type instance has an implicitly provided context node. For the XML instance stored in a column or variable, this is the document node. The document node is the implicit node at the top of every xml data type instance.
The result of the nodes() method is a rowset that contains logical copies of the original XML instances. In these logical copies, the context node of every row instance is set to one of the nodes identified with the query expression, so that subsequent queries can navigate relative to these context nodes.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.