Handling Specialty Data

1. Handling Specialty Data Lesson 24

2. Skills Matrix

3. Handling Special Data • Managing an RDBMS has historically posed special challenges for four specific situations: handling XML data, hierarchical data, spatial data and filestream data storage options. • SQL Serve now has built in data types and processing support providing neat solutions for these cases.

4. XML Data • SQL Server 2005 added a new data type. • This allows storage in a table—just like any other data type—which can be indexed, updated, modified or deleted—like any other data type. • Now, in addition to text or image binary large objects (BLOBs), you can associate structured or unstructured web-ready content in your result set or report.

5. Hierarchical Data • SQL Server 2008 added a new data type. • The hierarchyid data type is a variable length, system data type. • Use hierarchyid to represent position in a hierarchy. • This lets you, for example, create organization charts based on information in the database; or create pedigree charts for all your race horses; or track your entire family tree; or whatever you need to display when data has positional relationships.

6. Spatial Data • SQL Server 2008 added two new data types: geometry and geography. • Use these, for example, to display database locations on a map or chart. • Interface with Microsoft Virtual Earth and display the location of your store in context with all your store’s neighbors.

7. Filestream Data • SQL Server 2008 added the ability to store data on operating system files while at the same time being able to manipulate the data from SQL Server. • Imagine being able to have customers play one of hundreds of your “how to” videos on demand from their web browsers; or provide training videos for your employees managed by SQL Server; or think of other possibilities more applicable to your situation.

8. XML Data • XML is an abbreviation for Extensible Markup Language. • XML has been evolving since 1997 and has found widespread use as a standard for the structure of data and for its transmission across a variety of networks, most notably the World Wide Web.

9. XML • Used as: • Metalanguage • Markup Language • Schema Definitions • Extensible Stylesheet Language

10. XML Data • When storing XML data in your database, you can store the data as varchar or text data, you can decompose the data in relational data, or you can store the data as a native XML data type: • When storing XML as varchar or text data, you will lose most of its representation. • You can shred (reformat) the XML document into relational data and use a FOR XML clause with the SELECT statement to retrieve an XML structure from the relational data you store in the database. • Since SQL Server supports XML as a true data type, you can now benefit from that by storing XML natively inside the database.

11. Using XML Data Type • You can use the XML data type in many ways; it is comparable to using any other SQL Server data type. • Of course, you can also use XML data types in stored procedures as parameters and many other options. • SQL Server is flexible in the way you work with the XML data; you have the ability to store both typed XML and untyped XML in the database.

12. Untyped XML • When using untyped XML, you can store XML in the database in any form, as long as it is well-formed XML. • This means that upon defining an XML column and inserting or assigning a value to the XML column, a check will occur to see whether the data you are about to insert matches the XML standard, without validating it against an XML schema.

13. Typed XML • If you want to validate XML with an XML schema, you can specify an XML schema validation when creating or defining the XML data type. • You do this by referring to the XML schema, which you initially need to store and catalog in the SQL database. • XML that is validated by an XML schema is called typed XML. • An XML schema validation is like putting a check constraint on a scalar data type, since it performs a check on the values you provide.

14. Typed XML • When an XML data type is assigned to a Schema collection, you will not be able to insert any columns that don’t match the schema definition. • To use XML Schema collections in the database and associate them with variables and columns of the XML data type, SQL Server uses the CREATE XML SCHEMA. • When an XML data type is assigned to a Schema collection, you will not be able to insert any columns that don’t match the schema definition. • Once the schema is defined, you can use it and refer to it in a CREATE TABLE or even a DECLARE XML statement.

15. Querying XML Data • Because of the hierarchical structure inside XML data, you can use query methods to retrieve information and search for data in an XML data type. • The XML methods you can use to retrieve data from an XML data type are as follows: • Query method: Returns a fragment of untyped XML. • Value method: Returns XML data as a scalar data type. • Exists method: Checks whether a certain node exists in the XML data. • Modify method: Changes the contents of an XML document. • Nodes method: Returns a single column rowset of nodes from the XML column.

16. Decomposing XML Data • As database administrator, you can benefit from using XML in combination with the EVENTDATA( ) function. • If you have to write a trigger every time you want to use the Eventdata collection, and in that trigger decompose your XML data into a relational format to store it inside a log table, you would be wasting a lot of work rewriting or copying the code data that decomposes the Eventdata results in relational data.

17. Creating XML Indexes • The XML column in its native data type provides great capabilities in terms of retrieving data from within the XML column using various query methods. • To support the querying of XML data, you can create indexes on these columns. • The start of an XML index is a primary XML index, and all other XML indexes will depend on the primary index.

18. Creating XML Indexes • The start of an XML index is a primary XML index, and all other XML indexes will depend on the primary index. • You have the ability to create the following XML indexes to support optimized XML querying: • Primary XML index • Path XML index • Value XML index • Property XML index

19. External File Management • SQL Server introduced the FILESTREAM feature with the release of SQL Server 2008. • It works something like a hybrid of your traditional choices. • Now you can store your files external to SQL Server while SQL Server continues to maintain transactional consistency. • FILESTREAM also enables integration with Transact-SQL and manageability features: ACID, triggers, full-text search, backup and restore, security, database console checks and replication.

20. Summary • In this lesson, you learned about the XML data type, which is a new and native data type supported in SQL Server. • Using and storing XML data in SQL Server can be easy or complex, but it is important that you understand the benefits of using the XML data type. • You also learned how SQL Server works with XML and how you can decompose XML into relational data. • However, these are just basic components of the XML data type and its associated methods.

21. Summary • To query XML data, you learned different methods to use. • The nodes and value methods can convert XML data into relational data and, therefore, are probably the most commonly used methods. • SQL Server supports two types of XML: typed and untyped. • When using typed XML, you first need to catalog the XML Schema inside SQL Server using a CREATE XML SCHEMA COLLECTION statement.

22. Summary • You also learned the basics of hierarchical, spatial and external file management. • Hierarchical stores information about related data sets; spatial stores geometric or geographic descriptors that can be used with other display technologies to create a visual result; and external file management can stream data from your database to desiring users.

23. Summary for Certification Examination • Be able to identify the data structure. Understand when to use XML and when another data type is preferable or usable. • Know how to retrieve XML data. • To retrieve XML data, you can use several methods. • To be able to work with XML data, it is important to know the query, exist, nodes, value, and modify methods. • You need to fully understand how to decompose to XML, as well as how to convert it.

24. Summary for Certification Examination • Know how to modify XML data. XML data can be modified in the XML column using the modify method, and you don’t need to shred out and update the entire XML data in order to replace, insert, or even delete data in an XML column. • Understand how to convert between XML data and relational data. • Since the XML data type is near the highest in the data type precedence hierarchy, you will have conversion from and to the XML data type. • It is important to know how to use the CAST and CONVERT functions to support the XML data type.

25. Summary for Certification Examination • Know how to create an XML index. When optimizing XML querying, you can create multiple indexes, which are the primary starting points for your XML optimization. • To support the various types of XML queries, you will create secondary indexes. • Know how to load an XML schema. SQL Server stores typed or untyped XML. • When storing typed XML, you first will need to load an XML schema in the database before you can use it, and have schema validation in variables, columns, and expressions. • It is important to be familiar with this syntax.

26. Summary for Certification Examination • No certification requirements currently exist on Microsoft's Learning web page for hierarchical, spatial or external file management although these are important new features of SQL Server 2008.