DAT319 XML In The Database The XML Data Type In SQL Server 2005 (Code Named "Yukon") Michael Rys Program Manager SQL Server XML Technologies Microsoft.

  • Published on
    24-Dec-2015

  • View
    216

  • Download
    1

Transcript

  • Slide 1
  • DAT319 XML In The Database The XML Data Type In SQL Server 2005 (Code Named "Yukon") Michael Rys Program Manager SQL Server XML Technologies Microsoft Corporation
  • Slide 2
  • XML Characteristics Self-describing: This is an example. Self-describing: This is an example. Complex data Trees, recursive, graph Structured Data: Highly regular, homogeneous structures Semi-structured Data: Heterogeneous, sparse data Markup Data: Documents/content markup Document ordering Schema/Type system Schema-less Optional Schema: Semi-structured, structured Extensible Annotations, multiple schemas (late binding)
  • Slide 3
  • XML Scenarios Document Management Office XML Documents Data/view separation Documents, style sheets, transforms Data Exchange Business to business (B2B), business to consumer (B2C), application to application (A2A) XML is ubiquitous, extensible, platform independent transport format Messaging Simple Object Access Protocol (SOAP) Mid-Tier Collaboration Transport, Store, and Query XML data
  • Slide 4
  • SQL Server 2000 XML Support Overview Server support FOR XML generate XML from tables OpenXML generate relational rowset from XML Mid-tier support XML views (annotated mapping schemas XSD) TemplatesUpdateGrams/BulkLoad Access methods HTTP SOAP (via mid-tier ISAPI) ADO, OLE DB; ADO.NET
  • Slide 5
  • Relational XML mapping Limited support for semi-structured data No full XML document storage Document order not preserved Recursive schemas not supported Limited XML query capabilities SQL Server 2000 XML Support Main focus
  • Slide 6
  • Why XML Datatype? XML for relational data Rel XML Native XML Store in RDBMS + XML dt XML in SQL Server 2000 (including SQL XML) XML, relational and semistructured data Native XML and semistructured data DocumentMgmt Rel XML XML in SQL Server Yukon (including SQL XML)
  • Slide 7
  • Whats New In SQL Server 2005? New primitive XML datatype Unifying relational and XML storage Deep integration (trigger, replication, bulkload, security, data access ) XML Schema Collections Indexing of XML datatype XQuery and update on XML datatype (see DAT327)
  • Slide 8
  • Whats New In SQL Server 2005? FOR XML (Highlights only) Assignable, composable and thus nestable New PATH mode TYPE directive returns XML datatype instance ROOT node directive Element-centric RAW mode with row naming
  • Slide 9
  • XML Or Relational? XMLRelational Flat Structured Data Hierarchical Structured Data Not First Class: PK- FK with cascading delete Semi-structured Data Not First Class Mark-up Data Not First Class: FTS Order preservation Not First Class Recursion (Recursive query)
  • Slide 10
  • Architectural Overview XML Parser XML Validation XML datatype (binary XML) SchemaCollection XML Relational XML Schemata OpenXML/nodes() FOR XML with TYPE directive Rowsets query() modify()
  • Slide 11
  • XML Datatype And XML Schema Collections
  • Slide 12
  • Native SQL type Use for column, variable or parameter Can represent XML 1.0 documents XML 1.0 fragments (0 to n element nodes and text nodes at top) Can be constrained by XML Schema collection Methods on XML data type (see DAT327) query(), value(), exist(), modify(), nodes() Storage of XML instances As LOB (2 GB) in efficient binary representation XML encoding transformed to UTF-16 Well-formed and validation checks XML Datatype
  • Slide 13
  • Variable/Parameter declaration declare @xdoc xml declare @xdoc xml(SchemaCollection) declare @xdoc xml(DOCUMENT SchemaCollection) Column declaration create table T1 (, x xml) Instance creation Using CAST/CONVERT set @xdoc = CAST(N' ' as xml) Using FOR XML set @xdoc = (SELECT * FROM T1 FOR XML auto, type) Reading from File CAST(SELECT * FROM OPENROWSET (BULK Customer1.xml', SINGLE_BLOB) as xml) Serialization set @string = CONVERT(nvarchar(max), @xdoc) XML Datatype Use
  • Slide 14
  • XML Schema Support XML Schema (World Wide Web Consortium [W3C] standard) Rich mechanism for type definitions and validation constraints Can be used to constrain XML documents Benefits of typed data Guarantees shape of data Allows storage and query optimizations XML type system Store XML schemas in system metadata Does not preserve annotations
  • Slide 15
  • XML Schema Collections Creating an XML Schema collection CREATE XML SCHEMA COLLECTION S1 AS ' ') Constraining XML documents CREATE TABLE T(x XML(S1)) CREATE TABLE T(x XML(CONTENT S1)) CREATE TABLE T(xDoc XML(DOCUMENT S1)) Retrieving XML Schema collections SELECT XML_SCHEMA_NAMESPACE(N'dbo', name) FROM sys.xml_schema_collections Deleting an XML Schema collection DROP XML SCHEMA COLLECTION S1
  • Slide 16
  • Adding new types/elements/schemata at top ALTER XML SCHEMA COLLECTION S1 ADD Cannot change existing components and content models (watch for wildcard sections!) Retyping an XML instance ALTER TABLE T ALTER COLUMN x XML (S1) works from untyped to typed and from one schema collection to another Does not work with XML index present (drop index first) XML Schema Collections Evolution
  • Slide 17
  • XML Index Create primary XML index on XML column CREATE PRIMARY XML INDEX idx_1 ON docs (xDoc) Creates secondary XML indexes on values (VALUE), paths (PATH), properties (PROPERTY) CREATE XML INDEX idx_1_Tag ON docs (xDoc) USING XML INDEX idx_1 FOR PATH Speeds up queries Results can be served directly from index Entire query is optimized Same award winning cost based optimizer Indexes are used as available
  • Slide 18
  • XQuery: query XML documents and data Standards-based: W3C working draft In document 123, return section heading of section 3 and later SELECT id, xDoc.query(' for $s in /doc[@id = 123]//sec[@num >= 3] return {data($s/heading)} return {data($s/heading)} ') FROM docs For more details see DAT327 XML Query
  • Slide 19
  • Insert, update, & delete XQuery extensions XML sub-tree modification: Add or delete XML sub-trees Update values Add a new section after section 1: UPDATE docs SET xDoc.modify('insert Background after (/doc/section[@num=1])[1]') XML Data Modification
  • Slide 20
  • Best Practices Data Modeling using XML Data Type XML data granularity (impacts concurrency) Property promotion (computed column, indexed view) Untyped versus Typed XML Content of schema collection Workload Presence of XML column slows down table scans Typed XML yields optimizations XML blob and index are appropriately typed Type inference used in query optimizations
  • Slide 21
  • FOR XML Publishing relational Data as XML
  • Slide 22
  • QueryExecution FOR XML Aggregator SELECT FROM WHERE FOR XML mode, directives ROWSET Architectural Blueprint: FOR XML XML dt nvarchar
  • Slide 23
  • FOR XML And OpenXML Fully backward compatible with SS2K FOR XML New directive TYPE returns XML data type Nested FOR XML Assignment to XML data type Support for new data types element-centric raw mode inline XSD for raw and auto NULL as xsi:nil or absence for element-centric modes New PATH mode OpenXML XML overflow column New types [n]varchar(max), varbinary(max), UDT
  • Slide 24
  • FOR XML New features New directive TYPE returns XML data type instance (will error on invalid data) SELECT * FROM Customers FOR XML AUTO, TYPE Assignment to XML data type, binary or string type SET @x = (SELECT * FROM Customers FOR XML AUTO, TYPE) Nested FOR XML SELECT *, (SELECT * FROM Orders WHERE Orders.CustomerID=Customers.CustomerID FOR XML AUTO, TYPE), (SELECT * FROM Address WHERE Address.CustomerID=Customers.CustomerID FOR XML AUTO, TYPE) FROM Customers FOR XML AUTO, TYPE
  • Slide 25
  • Support for new data types Timestamp is now mapped as binary Element-centric RAW mode with row naming SELECT * FROM Customers FOR XML RAW(Customer), ELEMENTS XMLSCHEMA directive infers inline XSD for RAW and AUTO mode NULL as xsi:nil or absence for element-centric modes SELECT * FROM Customers FOR XML RAW(Customer), ELEMENTS XSINIL ROOT node directive adds wrapper to FOR XML result SELECT * FROM Customers FOR XML RAW, ROOT(root) SELECT * FROM Customers FOR XML RAW, ROOT(root) FOR XML New features
  • Slide 26
  • New PATH mode SELECT customerid as @id, customername, street as address/street, zipcode as address/zip, city as address/city FROM Customers FOR XML PATH, TYPE
  • Slide 27
  • Full-Text Indexing XML filter Index and query XML instances Markup serves as token boundary Markup tags (including attributes) removed Syntax same as for other columns CREATE FULLTEXT INDEX ON docs (xDoc) Can be combined with XQuery: Use full-text search as filter, then XQuery search Uses full-text index first Uses XML index on tags, values, paths
  • Slide 28
  • Full-Text Search Query examples SELECT R.x.query ('//sec[@num=12]') FROM (SELECT * FROM docs (SELECT * FROM docs WHERE contains(xDoc, 'Wrd1 Wrd2')) R(x) WHERE contains(xDoc, 'Wrd1 Wrd2')) R(x) Supports xml:lang attribute Uses appropriate language word breaker SELECT * FROM docs WHERE contains (xDoc, 'Visionen', LANGUAGE 'German') LANGUAGE 'German')
  • Slide 29
  • Client XML Data Access Native Access SQLOLEDB As DBTYPE_WSTR Large string As ISequentialStream SAX or DOM can be built from server character stream Down Level, ADO and ODBC: large string Managed Access & CLR (XML datatype) XML Type exposed as SqlXml (System.Data.SqlTypes) XmlReader can be obtained using sqlxml.CreateReader() HTTP/SOAP native (new) and via ISAPI (as now) XML character stream
  • Slide 30
  • Support within SqlClient SqlDataReader SqlXml GetSqlXml(int i); Use sqlxml.CreateReader() to obtain an XmlReader DataSet New XPathDocument column type Full databinding support Support For XML Data Type Inside ADO.Net
  • Slide 31
  • SQL Server 2005: More Sessions DAT330 Tue 3.30 4.45 SQL Server 2005 (code named "Yukon"): Security Enhancements DAT329 Tue 5.00 6.15 SQL Server 2005 (code named "Yukon"): A Platform for Web Services DAT313 Wed 8.30 9.45 Introducing SQL Server 2005 (code named "Yukon") Data Transformation Services DAT377 Wed 10.15 11.45 Data mining in SQL Server 2000 and SQL Server 2005 (code named "Yukon") DAT317 Wed 2.00 3.15 SQL Server 2005 (code named "Yukon"): Setup and Deployment DAT318 Wed 3.45 5.00 SQL Server 2005 (code named "Yukon"):.NET Framework-Based Programming in the Database DAT323 Wed 5.30 6.45 SQL Server 2005 (code named "Yukon"): What's New in Replication DAT328 Thu 8.30 9.45 SQL Server 2005 (code named "Yukon"): Introduction to the Unified Dimensional Model in Analysis Services DATC15 Thu 10.15 11.30 Real Time Analytics: Bringing It All Together Using Analysis Services DAT321 Thu 10.15 11.30 SQL Server 2005 (code named "Yukon"): Management Tools DAT319 Thu 1.30 2.45 XML in the Database - the XML Data Type in SQL Server 2005 (code named "Yukon") DAT340 Thu 3.15 4.30 Applying and Targeting DTS in SQL Server 2005 (Code Named "Yukon") DAT324 Thu 5.00 6.15 SQL Server 2005 (code named "Yukon"): Backup and Restore Engine DAT322 Fri 9.00 10.15 SQL Server 2005 (code named "Yukon"): SQL Server Management Object (SMO), Next Generation SQL-DMO DAT431 Fri 10.45 12.00 High Availability Technologies in SQL Server 2000 and SQL Server 2005 (code named "Yukon"): A Comparative Study DAT326 Fri 10.45 12.00 SQL Server 2005 (code named "Yukon"): Be More Efficient with T-SQL DAT325 Fri 1.00 2.15 SQL Server 2005 (code named "Yukon"): Using the Service Broker to Build Asynchronous, Queued Database Applications DAT327 Fri 2.45 4.00 SQL Server 2005 (code named "Yukon"): Inside XQuery
  • Slide 32
  • More information on XQuery DAT327 Presentation Whitepapers and other information http://msdn.microsoft.com/sql http://msdn.microsoft.com/xml Newsgroup news: microsoft.public.sqlserver.xml news: microsoft.public.sqlserver.xml My contact Email: mrys@microsoft.com mrys@microsoft.com Weblog: http://www.sqljunkies.com/weblog/mrys http://www.sqljunkies.com/weblog/mrys
  • Slide 33
  • SQL Server Community sites http://www.microsoft.com/sql/community/default.mspx List of newsgroups http://www.microsoft.com/sql/community/newsgroups/default.mspx Locate Local User Groups http://www.microsoft.com/communities/usergroups/default.mspx http://www.microsoft.com/communities/usergroups/default.mspx Attend a free chat or web cast http://www.microsoft.com/communities/chats/default.mspx http://www.microsoft.com/usa/webcasts/default.asp
  • Slide 34
  • Visit the SQL Server 2005 website: www.microsoft.com/sql/2005 www.microsoft.com/sql/2005 Learn more about SQL Server 2005 at Tech Ed Hands On Labs Rooms 6E and 6F 13 Hands On Labs Ask the Experts Track Cabanas located around CommNet Experts Available All Week Next Steps: SQL Server 2005 Exclusive Tech Ed Offer! Receive Beta 2 of SQL Server 2005 Register for SQL Server 2005 Beta 2 at: http://www.msteched.com/SqlBetaBits.aspx http://www.msteched.com/SqlBetaBits.aspx Exclusive Tech Ed Offer! Receive Beta 2 of SQL Server 2005 Register for SQL Server 2005 Beta 2 at: http://www.msteched.com/SqlBetaBits.aspx http://www.msteched.com/SqlBetaBits.aspx
  • Slide 35
  • Q1:Overall satisfaction with the session Q2:Usefulness of the information Q3:Presenters knowledge of the subject Q4:Presenters presentation skills Q5:Effectiveness of the presentation Please fill out a session evaluation on CommNet
  • Slide 36
  • 2004 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.

Recommended

View more >