Aprovechando XML dentro de la base de datos con SQL Server 2005

1. Aprovechando XML dentro de la base de datos con SQL Server 2005

2. Adolfo Wiernikadolfo@wiernik.net • Microsoft Regional Director - http://msdn.microsoft.com/isv/rd • Mentor Solid Quality Learning - http://www.solidqualitylearning.com • Fundador, Costa Rica User Group .NET - http://www.crug.net • Orador INETA Latinoamérica - http://www.ineta.org/latam • Blog - http://www.wiernik.net Jose Ricardo Ribeiroricardor@microsoft.com • En Microsoft desde 1998 • Desde el 2003 - Regional Program Manager • SQL Server Latinoamérica

3. Series de Webcasts • Introducción a SQL Server 2005 para desarrolladoresViernes, 22 de Julio de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277969&Culture=es-MX • Nuevas características del lenguaje T-SQL en SQL Server 2005Lunes, 25 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277973&Culture=es-MX • Aprovechando XML dentro de la base de datos con SQL Server 2005Viernes, 29 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277975&Culture=es-MX • Programando SQL Server 2005 con el CLR – Integración SQL-CLRLunes, 01 de Agosto de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277977&Culture=es-MX • Nuevas características en ADO.NET 2.0Viernes, 05 de Agosto de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277978&Culture=es-MX

4. Nuevas Características para Desarrollo SQL Server Engine • SQL Service Broker • HTTP Support (Native HTTP) • Multiple Active Result Sets (MARS) • Snapshot Isolation Level Reporting Services • Multiple Output Formats • Parameters (Static, Dynamic, Hierarchical) • Bulk Delivery of Personalized Content • Support Multiple Data Sources • STS (Web Parts, Doc Libraries) • Visual Design Tool • Charting, Sorting, Filtering, Drill-Through • Scheduling, Caching • Complete Scripting Engine • Scale Out architecture • Open XML Report Definition Notification Services SQL Server Mobile Edition MDAC • SNAC • Microsoft Installer base setup ADO.NET 2.0 • Notification Support • Object Model enhancements SQL Client .NET Data Provider • Server Cursor Support • Asynchronous Execution • System.Transactions Security • Separation of Users and Schema • Data encryption primitives Administration • SQL Management Objects (SMO) • Analysis Management Objects (AMO) • Replication Management Objects (RMO) T-SQL • Recursive Queries • Common Table Expressions • PIVOT – UNPIVOT Operators • APPLY Operator • Exception Handling .NET Framework • Common Language Runtime Integration • User-defined Aggregates • User-defined Data Types • User-defined Functions • SQL Server .NET Data Provider • Extended Triggers Data Types • Managed SQL Types • New XML Datatype • Varchar (MAX) Varbinary (MAX) XML • XQUERY Support • XML Data Manipulation Language • FOR XML Enhancements • XML Schema (XSD) Support • MSXML 6.0 (Native) • .Net XML Framework Full-text Search • Indexing of XML Datatype

5. Agenda • Why bring XML and Relational together? • Microsoft SQL Server 2000 Recap • Microsoft SQL Server 2005 XML server-side support • Native XML storage • XML schema support • XML Querying and updating • Publishing with FOR XML • Shredding with nodes()

6. XML and Relational Data Today XML XML XML XML File System

7. XML • Self-describing: <doc id="d1">This is an <important>example</important>.</doc> • 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)

8. Relational Data • Tabular data format with a priori fixed schema • Schema and data are kept separate:CREATE TABLE T (name nvarchar(50), age int)INSERT INTO T(name, age) VALUES ('Zaphod', 42) • Structured Data: highly regular, homogeneous structures • Express relationships through • Referential constraints (foreign keys) • Queries and Views • Provides efficient repurposing of information

9. XML Scenarios • Data Exchange • Business-to-business (B2B), business-to-consumer (B2C), application-to-application (A2A) • XML is ubiquitous, extensible, platform independent transport format • Document Management • Office XML Documents • Data/view separation: Documents, style sheets, transforms • Messaging • Simple Object Access Protocol (SOAP) • Mid-Tier Collaboration • Ad-hoc modeling • storing objects with sparse and multi-valued properties that do not fit well in the traditional relational schemata →Transport, Store, and Query XML data

10. Why Bring XML and Relational Data Together? XML in SQL Server 2005(incl SQLXML) XML in SQL Server 2000(incl SQLXML) Document Mgmt + XML dt Native XML Store in RDBMS Native XML andsemi-structured data DBMS Rel  XML Rel  XML XML for relational data XML, relational and semi-structured data

11. SQL Server as Integrated Data Platform • Provides core DB services to BOTH XML and Relational Data: • Base Services • Concurrency Control • Recovery • Declarative Query and Update language • Execution Engine and Optimizer • Tools to repurpose and combine data • Shared management and deployment • Integration with BI tools

12. XML or Relational?

13. XML and Relational!

14. SQL Server 2000Technologies • Server support • FOR XML: generate XML from tables • OpenXML: generate relational rowset from XML • Mid-tier support • XML views (annotated mapping schemas XSD) • Templates • UpdateGrams/BulkLoad • Access methods • HTTP SOAP (via mid-tier ISAPI) • ADO, OLE DB; ADO.NET

15. XML Datatype • 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 • Queryable with XQuery • Updateable with XML-DML • XML Indexing • Well-formed and validation checks

16. XML Datatype: Storage • Primary Storage • As LOB (2 GB per instance) • Efficient binary representation: • Primary goal: efficient parsing and serialization • ca. 20% compression over Unicode textual representation • XML encoding transformed to UTF-16 • Secondary Storage (for Query Optimization) • Via Primary and Secondary Indices • Primary XML index: 2X of XML data • PATH XML index: 0.5 of XML data • CAST from/to varbinary, (n)varchar, and CLR UDT

17. 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

18. XQuery and XML-DML in SQL Server 2005 • Subset of XQuery implemented • Is aligned with July 2004 XQuery working draft • Added XML Data Modification • Applies to single XML data type instance • Methods on XML data type: • query(), value(), exist(), modify(), nodes() • Use SQL to iterate over collection of instances (XML-typed column) • Can refer to relational data • Take advantage of Schema-collection information to operate on typed XML data • Will make use of XML indices for optimization

19. XML Query • 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 <topic>{data($s/heading)}</topic> ') FROM docs

20. XQuery: query() • Creates new, untyped XML data type instance • SELECT T.x.query(‘declare namespace n = “urn:example” for $s in • /n:doc[@id = 123]//n:sec[@num >= 3] return <topic>{data($s/n:heading)}</topic>’)FROM T

21. XQuery: value() • Extracts an XQuery value into the SQL value and type space • Expression has to statically be a singleton • String value of atomized XQuery item is cast to SQL type SELECT T.x.value( ‘(/n:doc[@id = 123]//n:sec[@num >= 3]/@num)[1]’, ’int’)FROM T

22. XQuery: exist() • Returns 1 if the XQuery expression returns at least one item, 0 otherwise select * from T where T.x.exist(‘/a/b[@id=42]’)=1

23. sql:column()/sql:variable() • Map SQL value and type into XQuery values and types in context of XQuery or XML-DML • sql:variable(): accesses a SQL variable • sql:column(): accesses another column value declare @value int set @value=42select * from T where T.x.exist(‘/a/b[@id=sql:variable(“@value”)]’)=1 tables: T(key int, x xml), S(key int, val int)select * from T join S on T.key=S.keywhere T.x.exist(‘/a/b[@id=sql:column(“S.val”)]’)=1

24. XML Indices • Create XML index on XML column CREATE PRIMARY XML INDEX idx_1 ON docs (xDoc) • Creates secondary indexes on tags, values, paths • 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 • Indices can be ALTERed and DROPed

25. Secondary XML Indices • FOR VALUE– useful for queries such as //city[.=“Bellevue”] • FOR PATH– useful for Path expressions: avoids joins by mapping paths to hierarchical index (HID) numbers. Example: /person/address/zip • FOR PROPERTY– useful for “property extraction” scenario. A bunch of properties (e.g. <prop>123</prop>) are being stored in an XML column. Relational part of query (or other XQuery method) has found XML instance already.

26. Shredding and Publishing XML • Server-side: • Publishing: FOR XML • Shredding: nodes() method or OpenXML • Client-side: • SQLXML 4.0 XML Bulkload object • ADO.NET DataSet • SQL Server Integration Services

27. FOR XML and OpenXML • 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 • New PATH mode • WITH XMLNAMESPACES (for FOR XML and XQuery) • Requires explicit cast of CLR UDT to XML • element-centric raw mode • inline XSD for raw and auto • NULL as xsi:nil or absence for element-centric modes • OpenXML • Based on MSXML 3.0 engine • XML overflow column • New types [n]varchar(max), varbinary(max), XML

28. XQuery: nodes() • Provides OpenXML-like functionality on XML data type column in SQL Server 2005 • Returns a row per selected node • Each row contains a special XML data type instance that: • Has the context node set to the selected node • Preserves the original structure and types • Can only be used with the XQuery methods (not modify()), count(*), and IS (NOT) NULL

29. Conclusions • Why bring XML and Relational together? • Microsoft SQL Server 2000 Recap • Microsoft SQL Server 2005 XML server-side support • Native XML storage • XML schema support • XML Querying and updating • Publishing with FOR XML • Shredding with nodes()

30. Resources SQL Server webpage: http://msdn.microsoft.com/SQL/2005/ XML and Databases whitepapers: http://msdn.microsoft.com/XML/BuildingXML/XMLandDatabase/ Online WebCasts: http://msdn.microsoft.com/sql/2005/2005webcasts/ Newsgroups & Forum: microsoft.private.sqlserver2005.xmlhttp://communities.microsoft.com/newsgroups/default.asp?ICP=sqlserver2005&sLCID=ushttp://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=89

31. Series de Webcasts • Introducción a SQL Server 2005 para desarrolladoresViernes, 22 de Julio de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277969&Culture=es-MX • Nuevas características del lenguaje T-SQL en SQL Server 2005Lunes, 25 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277973&Culture=es-MX • Aprovechando XML dentro de la base de datos con SQL Server 2005Viernes, 29 de Julio de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277975&Culture=es-MX • Programando SQL Server 2005 con el CLR – Integración SQL-CLRLunes, 01 de Agosto de 2005 06:00 p.m.(GMT) http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277977&Culture=es-MX • Nuevas características en ADO.NET 2.0Viernes, 05 de Agosto de 2005 06:00 p.m.(GMT)http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032277978&Culture=es-MX

32. Gracias adolfo@wiernik.net adolfo@solidqualitylearning.com Weblog: www.wiernik.net