SQL Server 2000 XML Annotated Schemata Michael Rys

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