1 / 29

SQL Server 2000 XML Annotated Schemata Michael Rys

SQL Server 2000 XML Annotated Schemata Michael Rys Program Manager SQLServer XML Technologies Microsoft Corporation mrys@microsoft.com. Scenarios. Web applications with Dynamic Data Browser based applications that require data from a database Business-to-business data processing; messaging

hyman
Download Presentation

SQL Server 2000 XML Annotated Schemata Michael Rys

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SQL Server 2000 XML Annotated Schemata Michael Rys Program ManagerSQLServer XML TechnologiesMicrosoft Corporation mrys@microsoft.com

  2. Scenarios • Web applications with Dynamic Data • Browser based applications that require data from a database • Business-to-business data processing; messaging • Data interchange using XML as ubiquitous, extensible, platform independent transport format

  3. Move data in a standardized format: • XML Syntax • XML Schemata for domain standard Object (XML) Loosely Coupled Systems • Scalable. Many to Many. • Changes in Implementation do not break each other Mapping Mapping Data App Logic Data System Application System

  4. Challenges • Providing XML views to relational tables • Allow efficient querying of XML views • Allow updating of XML views • Provide other useful applications of XML views: • Bulkload • Relational schema generation

  5. View Example Orders ID 222 Cust 243 <order ordid="222" cust="243"> <line qty="1" item="dt334"/> </order> Order Lines Order ID 222 Line No 1 Item dt334 Quantity 1

  6. XML Views • Defines an XML View on the database • Uses XML-Data Reduced (XDR) syntax with annotations (mapping schemas) • Similar to DTD but using XML grammar • Public schema grammar used by BizTalk • Annotations specify the XML to relational database mapping • For column values • For relationships between contained tags • XML View Mapper

  7. Default Mapping <?xml version="1.0"?> <Schema name="Orders" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes"> <ElementType name="line" model="closed"> <AttributeType name="qty" /> <AttributeType name="item" /> <AttributeType name="line" /> <attribute type="qty" /> <attribute type="item" /> </ElementType> <AttributeType name="ordid" /> <AttributeType name="cust" /> <ElementType name=order model="closed"> <attribute type="ordid" /> <attribute type="cust" /> <element type="line"/> </ElementType> </Schema>

  8. Default Mapping • Element name matches table name • Attribute and textOnly subelement names match column names • No way to map hierarchies: resulting XML is flat

  9. Annotations • sql:relation (map table-element name) • sql:field (map column-element/attribute name) • sql:relationship (hierarchies, joins) • sql:limit-field & sql:limit-value (horizontal partitions) • sql:key-fields (keys) • sql:id-prefix (XML prefix for IDs) • sql:is-constant (exists in XML, do not map to DB) • sql:map-field (exists in schema, do not map at all) • sql:overflow (column for unmapped XML) • sql:use-cdata (generate CDATA section) • sql:url-encode (contains dbobject reference) • sql:datatype (SQL datatype hint) xmlns:sql=“urn:schemas-microsoft-com:xml-sql"

  10. Annotations <?xml version="1.0" ?> <Schema xmlns="urn:schemas-microsoft-com:xml-data" xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <ElementType name="Customer" sql:relation="Customers"> <AttributeType name="ID" /> <attribute type="ID" sql:field="CustomerID" /> <element type="Order"> <sql:relationship key-relation="Customers" key="CustomerID" foreign-relation="Orders" foreign-key="CustomerID"/> </element> </ElementType> <ElementType name="Order" sql:relation="Orders"> <AttributeType name="OrderID" /> <attribute type="OrderID" sql:field="OrderID"/> </ElementType> </Schema>

  11. XPath to define XML Views • W3C Recommendation • Allows to navigate XML trees: • Specifies set of nodes • Allows specification of predicates • SQLServer 2000 XPath: • Subset of W3C XPath 1.0 Recommendation • Defines virtual XML view over relational database together with Annotated Schemas • Usable in URLs, Templates, and in ADO/OLE-DB • NOTE: OpenXML uses MSXML XPath implementation

  12. Syntax • General Syntax • /a::n[p]/b::m[q]where a::n[p] location step; a, b axes; m, n nodetests; p, q predicates • Shortform for most common axes • Example: • /child::Customer[attribute::State="WA"] • Shortform: /Customer[@State="WA"]

  13. Semantics • General Semantics: • Select all nodes with name n for which predicate p holds and can be reached via axis a • p is existentially quantified root Customer Customer Customer @state=WA Order @state=CA Order Order Order @state=WA /root /Customer [@state="WA"] /Order

  14. SQLServer 2000 XPath Support • Axes: child, attribute, parent, self • Boolean-valued predicates • Relational operators =, !=, <=, >, >= • Arithmetic operators +, -, *, div • Boolean operators/functions AND, OR, true(), false(), not() • Explicit conversion functions number(), string(), boolean() • Variables • Not supported: See Books-OnLine

  15. AnnotatedSchema XPath Query /A/B/C Defines setof potential XML views over DB First location step A defines instance of XML view over DB XPath and Annotated Schema SQL DB

  16. Querying • XPath Query • Used with a reference to the mapping schema • /Customer[@ID=“ALFKI”] • Results in: <Customer ID="ALFKI"> <Order OrderID="10643" /> <Order OrderID="10692" /> <Order OrderID="10702" /> <Order OrderID="10835" /> <Order OrderID="10952" /> <Order OrderID="11011" /> </Customer>

  17. Functional XPath Limitations • Not a full Query Language, only navigation • XPath cannot project • Tailor annotated schema to project only accessible data • Use XSLT postprocessing to project data needed in query but not exposed in view • XPath cannot prune subtrees (e.g., give me all customers of state X and only their orders from today) • Static pruning: map to SQL views • Dynamic pruning: XSLT • XPath cannot transform • Tailor annotated schema • Use XSLT to transform

  18. URL Query • http://server/vroot/vname/xpath?params • Virtual name (vname) • Direct reference to mapping schema • Path to directory • http://server/vroot/vname/nwind.xml/xpath/?params • XPath Query (xpath) • Specifies set of nodes from virtual document to return • Encode + etc. into %xx in URL • Parameters (params) • xsl, outputencoding, contenttype, root, … • User defined

  19. Template • http://server/vroot/vname?params • Same as template query with embedded SQL Query except: • Embedded query references mapping schema file • Query is expressed as XPath • Supports querying over namespace qualified names

  20. Template (Example) <root xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:xsl="path to XSLT file" > <sql:header> <sql:param name="state">WA</sql:param> </sql:header> <sql:xpath-query mapping-schema="nwind.xdr"> /Customers[@Region=$state] </sql:xpath-query> </root>

  21. Computes Request from CustState.xml Using nwind.xdr nwind .xdr TDS/ XML Computed FOR XML EXPLICIT Template Processing XML URL CustState .xml ISAPI SQL FOR XML Data Middle-Tier SQL Server 2000

  22. BizTalk Integration • Uses same schema format as BizTalk framework and server SQL Server BizTalk Schema BizTalk Schema copy Mapping Schema Query Results = + Annotations + XML in BizTalk Grammar XPATH /Customers

  23. UpdateGrams • Expresses Modifications to an XML document • Uses optimistic concurrency control • Captures inserts, deletes, and updates • Supports hierarchical updates to multiple tables using annotated schemas • Allows specification of null image • Values can be parameterized (uses $param, see DB202 for syntax) • Currently ships in beta as web release

  24. UpdateGrams <r xmlns:updg="urn:schemas-microsoft-com:xml-updategram"> <updg:sync mapping-schema="nwind.xml" updg:nullvalue="ISNULL"> <updg:before> <Customer CustomerID="LAZYK" CompanyName="ISNULL" Address="12 Orchestra Terrace" > <Order oid="10482"/> <Order oid="10545"/> </Customer> </updg:before> <updg:after> <Customer CustomerID="LAZYK" CompanyName="Lazy K Country Store" Address="12 Opera Court" > <Order oid="10354"/> <Order oid="10545"/> </Customer > </updg:after> </updg:sync> </r>

  25. Bulkload • Allows loading of large amount of XML into database via annotated schema. • Allows generation of relational schema • Provided as COM object • Can be called from DTS • Uses SAX to parse XML • Provides transacted and non-transacted modes • Beta will ship Oct 2000 as web release

  26. Bulkload - Rules • Only limited buffering while loading • Record Lifetime Rule • Data for a record needs to be grouped in the same element subtree • Basic Record Generation Rules • A new record is generated for a new relation on node or if a sql:relationship exists for this node • Subset Lifetime Rule • FK of 1:n relationships is buffered only as long as parent that defines FK is in scope • Key Ordering Rule • FK of 1:n needs to appear before many children

  27. Bulkload - Interface ISQLXMLBulkLoad { ConnectionString(…); ConnectionCommand(…); BulkLoad(…); KeepNulls(…); KeepIdentity(…); CheckConstraints(…); ForceTableLock(…); Transaction(…); XMLFragment(…); SchemaGen(…); SGDropTables(…); SGUseID(…); ErrorLogFile(…); Execute(…); }

  28. Summary • Use XML-centric application programming model • Queryable and updatable XML view over database • Provides: • Simple, declarative method for defining XML views on the database • NO CODING REQUIRED • Mechanism to retrieve XML conforming to Biztalk schema definitions • XSL for arbitrary output

  29. Future • Web Release Model • Updategrams (Aug-Nov 2000) • Bulk Load (Oct-Nov 2000) • XML View Mapper (Sep-Nov 2000) • W3C Schemas (XSD) • W3C Query Language • Performance

More Related