Module 9 Designing an XML Strategy

1. Module 9 Designing an XML Strategy

2. Module 9: Designing an XML Strategy Designing XML Storage Designing a Data Conversion Strategy Designing an XML Query Strategy

3. Lesson 1: Designing XML Storage Overview of Storing Data as XML Scenarios for Storing Data as XML Storing XML and Relational Data Redundantly Selecting a Column Type to Store XML Data Selecting an XML Data Type Index

4. Overview of Storing Data as XML <any> < xml > Send data as XML to client applications < xml > Split and combine XML data Support <any> elements Use XML schema definition Update XML data Understand the structure of the XML data Transfer XML data < xml > < xml > < xml > < xml > < xml > < xml > < xml > < xml > Storing Data as XML

5. Guidelines for Storing Data as XML You should not store data as XML when: You should store data as XML when: The order of elements is important ü The data contains frequently queried or updated elements × The data requires indexes × The data is based on a volatile XML schema ü The documents must support concurrent access × The documents require validation ü < xml >

6. Storing XML Data Redundantly < ? xml ?> < ? xml ?> Storing Data as XML < xml >

7. Selecting a Column Type to Store XML Data < xml > XML Column VARCHAR NVARCHAR Column • Supports typed and untyped XML data • Support for upto 2 GB of data storage • Supports methods such as Query, Modify, and Nodes • Supports creation of XML indexes over XML columns • Requires additional processing power • Provides good performance • NVARCHAR requires 2 GB of storage space to store 1 GB of data • VARCHAR can store 2 GB of data • Supports property promotion • Suitable only for storing and retrieving XML documents VARCHAR NVARCHAR

8. Selecting an XML Data Type Index XML primary index: XML VALUE index: • Creates a structure with tags, values, and paths • Required to create XML secondary indexes Enhances the performance of content-oriented queries CREATE XML INDEX <index name> ON <column> USING XML INDEX <primary XML index name> FOR VALUE ; GO XML secondary index: • Increases the performance of queries • Contains three specialized index types–VALUE, PATH and PROPERTY XML PATH index: Enhances the performance of structured queries CREATE XML INDEX <index name> ON <column> USING XML INDEX <primary XML index name> FOR PATH ; GO XML PROPERTY index: Enhances the performance of queries that use the Name or Value predicates CREATE XML INDEX <index name> ON <column> USING XML INDEX <primary XML index name> FOR PROPERTY ; GO

9. Lesson 2: Designing a Data Conversion Strategy Creating a Data Conversion Strategy Converting Relational Data into XML Data Demonstration: How To Convert Relational Data into XML Data Converting XML Data to a Rowset Demonstration: How To Convert XML Data into Relational Data

10. Creating a Data Conversion Strategy 1 Evaluate the application requirements for the data format < xml > < xml > < xml > < xml > 2 Determine the most efficient database storage method 3 Determine data access and modification methods 4 Determine whether to convert data on the server side or client side

11. Converting Relational Data into XML Data < xml > Mode Use RAW Generates one XML element per row AUTO Nests the elements based on the relational schema EXPLICIT Shapes the XML data PATH Combines elements and attributes FOR XML clause is used to convert relational data to XML data

12. Demonstration: How To Convert Relational Data into XML Data In this demonstration, you will see how to: Convert relational data into XML data

13. Converting XML Data to a Rowset < xml > Use the OPENXML option to convert XML data Use XML nodes method to convert XML data Use a user-defined function to encapsulate conversion logic

14. Demonstration: How To Convert XML Data into Relational Data In this demonstration, you will see how to: Convert XML data into relational data with query function

15. Writing Queries by Using XQuery Using XQuery to Modify XML Data Parameterizing XQueries Using XPath Expressions Using XQuery in the WHERE Clause of a DML Statement Lesson 3: Designing an XML Query Strategy

16. Writing Queries by Using XQuery < xml > < xml > < xml > XQuery Expressions XQuery Operators XML Constructors 123… • Primary • Sequence • Conditional • Quantified • Sequence type • FLWOR • Logical • Comparison • Arithmetic • Direct • Computed

17. Using XQuery to Modify XML Data You can modify XML documents by using the modify method of the XML data type and specify the modification by using XML DML statements.

18. Parameterizing XQueries XML Column Non-XML Column Single XML Structure Variable Value T-SQL Variable Parameterized XQuery allow developers to pass information from TSQL to XQuery and vice versa with help of parameter sql:column: Use the sql:column function to retrieve and combine XML and non-XML values into single XML structures Sql:variable: Use the sql:variable function to retrieve variable values

19. Using XPath Expressions An Xpathexpression uses a path notation, like those used in URLs, for addressing parts of an XML document User-Defined Function Constraint to a Table Create a user-defined function that contains the path expressions necessary to access XML data Create a function that contains the necessary logic, and then use that function in your constraint definition XML Indexes • Create any of the following to retrieve data: • Primary index • Value index • Path index • Property index Administrator

20. Using XQuery in the WHERE Clause of a DML Statement Use the exist method when possible Create a path index on the XML column Do not create an XML computed column if you must index that column

21. Lab 9: Designing an XML Strategy Exercise 1: Designing an XML Data Storage Model Exercise 2: Converting Data Between XML and Relational Forms Virtual machine NYC-SQL1 Administrator User name Password Pa$$w0rd Logon Information Estimated time: 60 minutes

22. Lab Scenario You are a lead database designer at QuantamCorp. You are working on the Human Resources Vacation and Sick Leave Enhancement (HR VASE) project that is designed to enhance the current HR system of your organization. This system is based on the QuantamCorp sample database in SQL Server 2008. The main goals of the HR VASE project are as follows: Provide managers with current and historical information about employee vacation and sick leave. Grant view rights to individual employees to view their vacation and sick leave balances. Provide permission to selected employees in the HR department to view and update the vacation and sick leave details of employees. Grant the HR manager with the view and update rights to all the data. QuantamCorp currently has a LocalAccounts database at each regional office. This database contains all customer account information in a nonsummarized format. The HR VASE application uses the Accounts database to calculate the staff’s performance metric. A user makes a new request to enhance the local account application to store information about customers’ requests for enquiry and service. The development team has decided to use the XML format to transfer data. The team has taken up this task to reduce the complexity of transferring additional data between the LocalAccounts database. The organization wants you to review the current solution and propose an updated database model that supports these requirements.

23. Lab Review In this lab, you: Designed XML storage Converted XML data into relational data