You are previewing Pro SQL Server 2008 XML.
O'Reilly logo
Pro SQL Server 2008 XML

Book Description

Knowledge and ability to apply XML are de rigueur in today's world, and SQL Server developers and administrators are no exception to that rule. Pro SQL Server 2008 XML is your key to unlocking the powerful XML feature set first introduced in SQL Server 2005 and since refined in SQL Server 2008. Author Michael Coles shows how to store XML using SQL Server's built-in XML data type. Learn to query and manipulate XML data using standard technologies such as XQuery and XSLT. No SQL Server database professional can afford to be without knowledge of the XML feature set. Pro SQL Server 2008 XML delivers on the knowledge that you need to remain competitive in your career.

  • Shows how to store, query, and manipulate XML documents in SQL Server

  • Provides step-by-step examples showing best practices and advanced features

  • Accurately discusses how SQL Server's XML feature set stacks up against the ISO XML standards

Table of Contents

  1. Copyright
  2. Foreword
  3. About the Author
  4. About the Technical Reviewer
  5. Acknowledgments
  6. Introduction
    1. Who This Book Is For
    2. How This Book Is Structured
      1. Chapter 1
      2. Chapter 2
      3. Chapter 3
      4. Chapter 4
      5. Chapter 5
      6. Chapter 6
      7. Chapter 7
      8. Chapter 8
      9. Chapter 9
      10. Chapter 10
      11. Chapter 11
      12. Chapter 12
      13. Chapter 13
      14. Chapter 14
      15. Appendix A
      16. Appendix B
      17. Appendix C
      18. Appendix D
      19. Appendix E
      20. Appendix F
      21. Appendix G
    3. Conventions
    4. Prerequisites
    5. Downloading the Code
    6. Contacting the Author
  7. 1. Enter XML
    1. 1.1. Looking Back at SQL Server XML
    2. 1.2. What Is XML?
    3. 1.3. Defining XML Data
      1. 1.3.1. XML Requirements
      2. 1.3.2. Well-Formed and Valid XML
    4. 1.4. Considering Other Formats
    5. 1.5. When to Use XML
    6. 1.6. What's New in SQL Server 2008 XML
      1. 1.6.1. The xml Data Type
      2. 1.6.2. XML Schema Collections
      3. 1.6.3. XML Indexes
      4. 1.6.4. FOR XML
      5. 1.6.5. XQuery and XML DML Support
      6. 1.6.6. HTTP SOAP Endpoints
    7. 1.7. Summary
  8. 2. FOR XML and Legacy XML Support
    1. 2.1. Using the FOR XML Clause
      1. 2.1.1. PATH Mode
      2. 2.1.2. RAW Mode
      3. 2.1.3. AUTO Mode
      4. 2.1.4. EXPLICIT Mode
    2. 2.2. Using XPath Node Tests
    3. 2.3. Adding Namespaces to FOR XML
    4. 2.4. Creating Complex FOR XML Queries
    5. 2.5. OPENXML Rowset Provider
    6. 2.6. OPENROWSET XML Loading
    7. 2.7. Summary
  9. 3. The xml Data Type
    1. 3.1. Creating xml Instances
      1. 3.1.1. Casting and Converting
      2. 3.1.2. Using xml Parameters and Return Types
    2. 3.2. Creating Well-Formed and Valid XML
      1. 3.2.1. XML Schema Collections
      2. 3.2.2. DTDs
    3. 3.3. Using XML Type Methods
      1. 3.3.1. Using the query() Method
      2. 3.3.2. Using the value() Method
      3. 3.3.3. Using the exist() Method
      4. 3.3.4. Using the nodes() Method
      5. 3.3.5. Using the modify() Method
    4. 3.4. Summary
  10. 4. XML Schema Collections
    1. 4.1. Introducing XML Schema
      1. 4.1.1. Documenting with Annotations
      2. 4.1.2. Using Declaration Components
      3. 4.1.3. Creating Complex Elements
      4. 4.1.4. Defining Model Groups
      5. 4.1.5. Adding Attributes
    2. 4.2. Constraining Occurrences
    3. 4.3. Extending XML Schemas with Wildcards
    4. 4.4. Typing XML
    5. 4.5. Summary
  11. 5. XQuery
    1. 5.1. Introducing the XQuery Language
    2. 5.2. Creating XQuery Queries
      1. 5.2.1. Defining the XQuery Prolog
      2. 5.2.2. Building Path Expressions
      3. 5.2.3. Limiting Results with Predicates
      4. 5.2.4. Using Quantified Expressions
    3. 5.3. Using FLWOR Expressions
    4. 5.4. Constructing XML with XQuery
    5. 5.5. Using the SQL Server xml Methods
      1. 5.5.1. Querying with query()
      2. 5.5.2. Retrieving Scalar Values with value()
      3. 5.5.3. Checking for Node Existence with exist()
      4. 5.5.4. Shredding XML with nodes()
      5. 5.5.5. Manipulating XML with modify()
      6. 5.5.6. Conditional Evaluation with if...then...else
    6. 5.6. Maximizing XQuery Performance
      1. 5.6.1. Use the value() Method
      2. 5.6.2. Avoid Reverse Axis Steps
      3. 5.6.3. Avoid // and Wildcards in the Middle
      4. 5.6.4. Use Subqueries
      5. 5.6.5. Avoid Predicates in the Middle
    7. 5.7. Summary
  12. 6. XQuery Functions and Operators and XML DML
    1. 6.1. Using Operators
      1. 6.1.1. Calculating with Math Operators
      2. 6.1.2. Using Comparison Operators
      3. 6.1.3. Constructing Sequences with the Comma Operator
    2. 6.2. Using XQuery Type Expressions
      1. 6.2.1. Casting XQuery Values
      2. 6.2.2. Checking the Instance Type
    3. 6.3. Using XQuery Functions
      1. 6.3.1. Using Data Accessor Functions
      2. 6.3.2. Using String Functions
      3. 6.3.3. Using the Boolean Function
      4. 6.3.4. Using Numeric Functions
      5. 6.3.5. Using Aggregate Functions
      6. 6.3.6. Using Sequence Functions
      7. 6.3.7. Using Node Functions
      8. 6.3.8. Using Context Functions
      9. 6.3.9. Using Constructor Functions
      10. 6.3.10. Using QName Functions
      11. 6.3.11. Using SQL Server XQuery Extension Functions
    4. 6.4. Modifying XML with XML DML
      1. 6.4.1. Inserting Nodes with insert
      2. 6.4.2. Deleting Nodes with delete
      3. 6.4.3. Updating Nodes with replace value of
    5. 6.5. Summary
  13. 7. Indexing XML
    1. 7.1. Creating a Primary XML Index
    2. 7.2. Creating Secondary XML Indexes
      1. 7.2.1. Creating PATH Secondary XML Indexes
      2. 7.2.2. Creating VALUE Secondary XML Indexes
      3. 7.2.3. Creating PROPERTY Secondary XML Indexes
      4. 7.2.4. Setting XML Index Options
    3. 7.3. Full-Text Indexing XML
    4. 7.4. Summary
  14. 8. XSLT and the SQLCLR
    1. 8.1. Transforming XML
      1. 8.1.1. Accessing XSLT Through .NET
      2. 8.1.2. Performing a Simple Transformation
      3. 8.1.3. Elements of XSLT Stylesheets
    2. 8.2. Performing a Back-End Transformation
    3. 8.3. Advanced XSL Transformations
      1. 8.3.1. The Multitemplate Stylesheet
      2. 8.3.2. Recursion in the Stylesheet
    4. 8.4. Summary
  15. 9. HTTP SOAP Endpoints
    1. 9.1. Creating Endpoints
    2. 9.2. Consuming Endpoints
    3. 9.3. Summary
  16. 10. .NET XML Support
    1. 10.1. XML Validation
    2. 10.2. Accessing XML on the Web
    3. 10.3. REST Services
    4. 10.4. .NET XML Classes
      1. 10.4.1. System.Xml Namespace
      2. 10.4.2. SqlXml Data Type
      3. 10.4.3. SqlCommand Options
      4. 10.4.4. Additional .NET XML Support
    5. 10.5. Summary
  17. 11. Spatial Data and GML
    1. 11.1. Spatial Data
    2. 11.2. Populating Spatial Data
    3. 11.3. GML
      1. 11.3.1. Geometric Objects
      2. 11.3.2. Elements of GML
    4. 11.4. Summary
  18. 12. SQLXML
    1. 12.1. Querying
    2. 12.2. Updategrams
      1. 12.2.1. Inserts
      2. 12.2.2. Updates
      3. 12.2.3. Deletes
      4. 12.2.4. Executing Updategrams with SqlXmlCommand
      5. 12.2.5. Diffgrams
    3. 12.3. Bulk Loading
    4. 12.4. Querying SQLXML with XPath
    5. 12.5. Summary
  19. 13. LINQ to XML
    1. 13.1. Functional Construction
    2. 13.2. Loading XML from Other Sources
      1. 13.2.1. Loading XML with the XmlReader
      2. 13.2.2. Querying with LINQ to SQL
      3. 13.2.3. Loading XML from the File System
      4. 13.2.4. Loading XML from a String
      5. 13.2.5. Loading XML via HTTP
    3. 13.3. Querying XML
    4. 13.4. Transforming XML
    5. 13.5. Summary
  20. 14. XML Support title
    1. 14.1. Bulk Copy Program
    2. 14.2. XML for Analysis
    3. 14.3. SQL Server Integration Services
    4. 14.4. XML Query Plans
    5. 14.5. Database Tuning Advisor
    6. 14.6. XMLSpy
    7. 14.7. Web Browsers
    8. 14.8. Visual Studio
    9. 14.9. Summary
  21. A. W3C and Other References
    1. A.1. W3C Specifications
    2. A.2. Other Useful Documents
  22. B. SQL Server XQuery Data Types
  23. C. XML Schema Reference
    1. C.1. Element Information Items
      1. C.1.1. all Element
      2. C.1.2. annotation Element
      3. C.1.3. any Element
      4. C.1.4. anyAttribute Element
      5. C.1.5. appInfo Element
      6. C.1.6. attribute Element
      7. C.1.7. attributeGroup Element
      8. C.1.8. choice Element
      9. C.1.9. complexContent Element
      10. C.1.10. complexType Element
      11. C.1.11. documentation Element
      12. C.1.12. element Element
      13. C.1.13. extension Element
      14. C.1.14. group Element
      15. C.1.15. import Element
      16. C.1.16. list Element
      17. C.1.17. notation Element
      18. C.1.18. restriction Element
      19. C.1.19. schema Element
      20. C.1.20. sequence Element
      21. C.1.21. simpleContent Element
      22. C.1.22. simpleType Element
      23. C.1.23. union Element
    2. C.2. XML Schema Data Type Facets
  24. D. XQuery/XPath/XML DML Quick Reference
    1. D.1. XPath
    2. D.2. XQuery
    3. D.3. XML DML
  25. E. XSLT 1.0 and XPath 1.0 Reference
  26. F. Glossary
  27. G. Selected T-SQL and .NET Code Listings
    1. G.1. Chapter 1
    2. G.2. Chapter 2
    3. G.3. Chapter 3
    4. G.4. Chapter 4
    5. G.5. Chapter 5
    6. G.6. Chapter 6
    7. G.7. Chapter 7
    8. G.8. Chapter 8
    9. G.9. Chapter 9
    10. G.10. Chapter 10
    11. G.11. Chapter 11
    12. G.12. Chapter 12