DAT301: XML数据和关系性数据的最终结合处 – SQL Server 2005 刘劲浩 XML 组 SQL Server 引擎 Microsoft Corp.

内容 为什么 XML? SQL 2005 中新的数据类型: XML data type XML data的存储和建索引 SQL 2005中新的顶级对象：XML Schema collection 从SQL到XML：FOR XML 从XML到SQL：OpenXML and nodes() 应用方向

数据交换的格式：XML 互联网时代, 数据交换不仅发生在公司内，也发生在 XML是可扩展的，独立于平台的数据交换格式 B2B B2C A2A – Any application to any application XML是可扩展的，独立于平台的数据交换格式 普遍使用方式: 数据存储 as SQL rowset, 传输 as XML Describe the technical theme. The theme groups a set of Features that deliver a value proposition to the user. Introduce that theme and the value proposition. Even though the features may come from various components of SQL Server, they complement each other in some easily-to-understand way.

数据建模 (Data modeling) XML 关系性数据  结构性数据 等级(Hierarchical) 结构性数据 半结构性数据 Not First Class: PK-FK with cascading delete 半结构性数据 Not First Class 非结构性数据 Not First Class: FTS 有序(Order)数据 递归／嵌套数据 (Recursive query)

从市场角度 + XML dt XML in Yukon (incl SQLXML) Document XML, relational and semistructured data Native XML and semistructured data DBMS Document Mgmt Rel  XML XML in SQLServer 2000 (incl SQLXML) + XML dt Native XML Store in RDBMS Rel  XML XML for relational data

SQL 2005 XML功能纵览 关系性数据和XML数据在一个DB中 XML data type XML架构(Schema)的支持 利用现有的数据存储和查询处理能力 XML data type 新数据类型 -> native storage of XML 可被建索引 Support un-typed and typed XML data XML架构(Schema)的支持 载入和储存 Validate and type XML instances XML查询(query)和删改(update) Integrages with relational Queries (part of SQL statement) Insert/update/delete SQL rowsets和XML之间的转换

XML数据类型 新SQL标量类型 可被查询和删改 − XQuery + XML_DML 可用于列，变量，函数和存储进程的参数 Typed by XML schema 可产生于 SELECT …FOR XML 可用OpenXML and nodes() method 产生行集 可被查询和删改 − XQuery + XML_DML 支持 SQL triggers, replication, BCP, DBCC, FTS, etc. 不可比较的数据: PK/FK, UNIQUE unsupported

XML数据类型: 实例 variable column parameter typed xml declare @x xml create table xtable (i int primary key, xcol XML) parameter create procedure xproc (@xdoc Xml) as ... typed xml declare @x xml(name_xml_schema_collection)

XML的存贮方法 SQL 2005的XML数据运用所谓二进制大对象 (BLOB – binary large object) 的方法来贮存. 最大空间为2GB 它使用特殊的格式来保持XML的数据模型 (Infoset). 此格式和文本格式相比节省大约20%的空间 物理上它和varbinary(max)是同一种数据类型 在查询处理中被分离(shredding) - 将XML变成行集合的过程.

XML索引 Create XML index on XML column 主XML索引相当于XML数据的另一拷贝 CREATE PRIMARY XML INDEX idx_1 ON docs (xDoc) 主XML索引相当于XML数据的另一拷贝 Creates secondary indexes on VALUE, PROPERTY and PATH Speeds up queries Results can be served directly from index Entire query is optimized Indexes are used as available

XML的查询和删改 XML的查询语言: XQuery Microsoft 版本的XML_DML, 用于XML的 删改 主体语言还是 SQL W3C working draft at http://www.w3.org/TR/xquery Microsoft 版本的XML_DML, 用于XML的 删改 insert / delete / replace value with 主体语言还是 SQL 将XQuery嵌入XML的数据方法中 (XML data type methods) Correlated with columns and T-SQL variables (via sql:column() and sql:variable())

XML的数据方法(methods) Query methods @x.query (xquery string)  XML Creates new, untyped XML data type instance @x.value (xquery string, sql_type string)  scalar (of type sql_type) Extracts an XQuery value into the SQL value and type space @x.exist (xquery string)  bit Returns 1 if the XQuery returns at least 1 item, 0 otherwise @x.nodes (xquery string)  multiple xml instances with different context nodes Will talk about later Data modification @x.modify (XML_DML string)  void Used with SET in UPDATE statement

XQuery:实例 declare namespace n = "http://example.com/ns1"; for $s in /n:doc[@id = 123]//n:sec[@num >= 3] Where $s/n:heading return <topic>{data($s/n:heading)}</topic> Query Prolog Query Body

XQuery之功能 FLWOR: FOR / LET / WHERE / ORDER BY / RETURN FOR LET WHERE Includes XPath 2.0 (/doc[@id = 123]) Element constructors (<topic>{…}</topic>) Order-preserving operators Input order (FLWR) Document order (XPath, union) Statically (or dynamically) typed Strong typing with schema, weak typing without schema SQL: WITH SELECT FROM WHERE ORDER BY & SET

XML架构集合(XML Schema Collections) SQL 2005中一个新的顶级对象 CREATE XML SCHEMA COLLECTION xsc_Test as '<schema xmlns="http://www.w3.org/2001/XMLSchema"> <element name="root" type="integer" /> </schema>' go declare @x xml(xsd_Test) set @x = '<root>12</root>'

XML架构集合(XML Schema Collections) XML Schema (XSD) — optional, XML doc. XSD is W3C standard at http://www.w3.org/2001/XMLSchema Simple and complex types, type inheritance, namespaces, etc. Import schema into db before use Stored in SQL meta-data component of server Schema can be exported from db XML架构为XML数据提供了一种约束 Provides validation constraint Type information on the XML instance data Utilized in query execution

FOR XML: XML的聚合器 从SQL行集合到XML 它产生单行, 单列的XML数据 Set @x = (select * from T for xml auto, type) Can be used in subqueries and assignments as singletons Example: SELECT CustomerId, (SELECT * FROM Orders WHERE Orders.CustomerId=CustomerId FOR XML AUTO, TYPE) AS orders FROM Customers FOR XML AUTO, ELEMENTS, TYPE

FOR XML in SQL 2005 Old FOR XML (SQL 2000) queries stay the same New features: Use TYPE directive to get XML data type instance Support new SQL types element-centric raw mode inline XSD for raw and auto NULL as xsi:nil or absence for element-centric modes Specify new row tag and root element name FOR XML PATH Nested FOR XML

FOR XML in SQL 2005 Old FOR XML (SQL 2000) queries stay the same New features: Use TYPE directive to get XML data type instance Support new SQL types element-centric raw mode inline XSD for raw and auto NULL as xsi:nil or absence for element-centric modes Specify new row tag and root element name FOR XML PATH Nested FOR XML

FOR XML in SQL 2005 Old FOR XML (SQL 2000) queries stay the same New features: Use TYPE directive to get XML data type instance Support new SQL types element-centric raw mode inline XSD for raw and auto NULL as xsi:nil or absence for element-centric modes Specify new row tag and root element name FOR XML PATH Nested FOR XML

FOR XML in SQL 2005 Old FOR XML (SQL 2000) queries stay the same New features: Use TYPE directive to get XML data type instance Support new SQL types element-centric raw mode inline XSD for raw and auto NULL as xsi:nil or absence for element-centric modes Specify new row tag and root element name FOR XML PATH Nested FOR XML

OpenXML: XML的分离器 从XML到SQL行集合 OpenXML: Old syntax still works Using MSSQLXML.DLL now sp_xml_preparedocument transfer XML into an internal edge table. Using XPath to look for the nodes Using WITH clause to map XML value(s) into SQL column(s) Old syntax still works 假如你想将单一的XML文档分离成 rows, you can use OpenXML

nodes()方法: 取代OpenXML If you want to shred multiple XML instances into SQL rowsets, nodes() is the answer. 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

FOR XML with TYPE directive SQL Server 2005 XML 纵览 XML Relational XML XML Parser XML Schemata Schema Collection Validation OpenXML/nodes() PATH Index XML data type (binary XML) Node Table Rowsets modify() PRIMARY XML INDEX PROP Index FOR XML with TYPE directive VALUE Index query() query()

SQL 2005 XML应用方向 属性管理(Property management) 数据交换(Data exchange) 文本管理(Document management) Save all MS Office files into SQL You can query and modify them XML视图技术 消息(Messaging) 对象储存(Object persistent)

关键论点: Key Takeaway 1: Key Takeaway 2: XML 数据类型提供了一种新的数据建模方法. 此种方法可描述各种格式的数据 Key Takeaway 2: 这一功能就在SQL2005引擎中

© 2002 Microsoft Corporation. All rights reserved. This presentation is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.