1 / 42

DAT 200 SQLXML – XML Technology For SQL Server ™

DAT 200 SQLXML – XML Technology For SQL Server ™. Alexander Vaschillo Lead Program Manager WebData XML Group Microsoft Corporation. SQLXML Talks. SQLXML: XML Technology for SQL Server DAT200, Alexander Vaschillo, Jul 2 8:30 Building Web Services with SQL Server and SQLXML 3.0

lidia
Download Presentation

DAT 200 SQLXML – XML Technology For SQL Server ™

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. DAT 200SQLXML – XML Technology For SQL Server™ Alexander Vaschillo Lead Program Manager WebData XML Group Microsoft Corporation

  2. SQLXML Talks • SQLXML: XML Technology for SQL Server • DAT200, Alexander Vaschillo, Jul 2 8:30 • Building Web Services with SQL Server and SQLXML 3.0 • DAT330, Alexander Vaschillo, Jul 2 18:15 • Inside data access with SQLXML: Architecture guide • DAT400, Alexander Vaschillo, Jul 3 10:00

  3. Agenda • The problem domain for SQLXML • Feature overview • Query/Update technologies • Access methods • Where are we going in the future

  4. XML for SQL Server – History • The project started in 1998 to solve XML to relational mapping • Version 1.0 of product shipped as part of SQL Server 2000 • Both Client and Server functionality • New releases of client functionality are available via the MSDN web site • Gold releases approximately every 8 months

  5. Different kinds of data • Structured • Highly regular, homogeneous structure • Rowsets, Comma delimited files • Semi-Structured • Heterogeneous structure • Sparse Occurrences of data • HTML and XML documents • Unstructured • Documents/Content

  6. XPath XQuery XML Files XML View XML/ HTML SQLXML – Bringing worlds together XML world The Two Worlds SQL Language SQL Server Data storage RowSet Data output Relational world

  7. SQLXML From 10,000 Feet • Provides a rich XML view of relational data • Semi-structured, hierarchical view of flat relational data • Two-way view: query and update • Multiple access mechanisms (HTTP, ADO, ADO.NET, SOAP) • Middle tier and Server side • XML: extensible, platform independent format for your data

  8. Query/Update Technologies • FOR XML (raw, auto, nested, explicit) • SQL language extension to retrieve XML instead of rowsets • XML Views – XPath, XQuery • Work with your relational database as if it was XML file (through annotated schema) • Updategrams/Diffgrams • Update through XML View • Bulkload • Shred large XML files into existing tables

  9. XML BCP/SQL XMLBulkload SQL update/ insert/ delete Updategrams XML SQL Server Annotated XSD Mapping Schemas SQL Queries Rowsets FOR XML Query Query Processor XQuery SQL Queries FOR XML Queries XML XPath FOR XML Queries FOR XML Architecture

  10. FOR XML Query • SQL Language Extension SELECT… FROM… WHERE… ORDER BY… FOR XML ( raw | auto [, ELEMENTS] | nested [, ELEMENTS] | explicit) [, XMLData] [, BINARY base64])

  11. Using FOR XML SqlConnection Conn = new SqlConnection(ConnStr); SqlCommand cmd = new SqlCommand(“select * from Authors FOR XML AUTO”, Conn); Conn.Open(); XmlReader r = cmd.ExecuteXmlReader();

  12. For XML – Raw Mode • One <row> element per row in the result set • No nested elements • Columns/values in result set are attributes/values on the <row> • Similar to row set but in XML format <row CustomerID=“ALFKI” OrderID=“10643” /> <row CustomerID=“ALFKI” OrderID=“10692” /> <row CustomerID=“ANATR” OrderID=“10308” /> … <row CustomerID=“FISSA” /> …

  13. For XML – Auto Mode • Supports nested XML output • Nesting determined by ordering of columns in SELECT clause • Table/View name in database used for the element name • Column names used for attributes • Change names using table and column aliases <Customers CustomerID=“ALFKI”> <Orders OrderID=“10643” /> <Orders OrderID=“10692” /> </Customers> <Customers CustomerID=“ANATR”> <Orders OrderID=“10308” /> …

  14. For XML – Explicit Mode • Provides complete control over format of XML result • Columns can be individually mapped to attributes or sub elements • Supports arbitrary nesting • Collapses/hoists hierarchy • Constructs ID/IDREF relationships • Explicit mode requires the SELECT query to be written in a certain way to produce the “universal table format”

  15. For XML – Explicit Mode SELECT 1 as TAG, NULL as PARENT, CustomerID AS [Customer!1!cid!id], CompanyName AS [Customer!1!name!element], NULL AS [Order!2!oid!id] FROM Customers WHERE CustomerID = 'ALFKI' OR CustomerID='BOLID' UNION ALL SELECT 2, 1, Customers.CustomerID, NULL, 'O-'+CAST(Orders.OrderID AS varchar(32)) FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID WHERE Customers.CustomerID = 'ALFKI' OR Customers.CustomerID='BOLID' ORDER BY [Customer!1!cid!id] FOR XML explicit

  16. XML Views • Map between relational data and XML • Declarative • Noninvasive • No changes to legacy data sources • No control over DB Server required • XML View is an XML Schema • XDR for SQL Server 2000 and WR1 • XSD for 2.0 and beyond

  17. Default Mapping Example <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema“ xmlns:msdata="urn:schemas-microsoft-com:mapping-schema“> <xsd:element name=“Customers”> <xsd:complexType> <xsd:attribute name=“CustID”/> </xsd:complexType> </xsd:element > </xsd:schema> Relational Data: Customers Table XML View CustID ------ A NULL B <Customers CustID=“A”/> <Customers/> <Customers CustID=“B”/>

  18. Explicit Mapping Example <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema“ xmlns:msdata="urn:schemas-microsoft-com:mapping-schema“> <xsd:element name=“Customers”msdata:relation=“tblCustomers"> <xsd:complexType> <xsd:attribute name=“ID”msdata:field=“pkCustID"/> </xsd:complexType> </xsd:element > </xsd:schema> Relational Data: tblCustomers Table pkCustID ------ A NULL B <Customers ID=“A”/> <Customers/> <Customers ID=“B”/>

  19. Join Example <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:mapping-schema"> <xsd:element name="Customer"msdata:relation="Customers"> <xsd:complexType> <xsd:sequence> <xsd:element name="Order"msdata:relation="Orders"> <xsd:annotation><xsd:appinfo> <msdata:relationship parent="Customers" parent-key="CustomerID" child="Orders" child-key="CustomerID" /> </xsd:appinfo></xsd:annotation> <xsd:complexType> <xsd:attribute name="OrderDate" type="xsd:dateTime"/> </xsd:complexType> </xsd:element> </xsd:sequence> <xsd:attribute name="CustomerID" /> </xsd:complexType> </xsd:element> </xsd:schema>

  20. XPath/XQuery • Use XPath/XQuery to query SQL Database as if it was an XML file • Each query translates into a SQL statement • XPath • /Customer/Order[@OrderID=‘10692’] • XQuery • For $i in sql:table('Customers', 'CustomerID') Return <Customer ID = {$i/@CustomerID} Name = {$i/@ContactName}/>

  21. Using XPath SqlXmlCommand cmd = new SqlXmlCommand(ConnStr); cmd.CommandType = SqlXmlCommandType.XPath cmd.CommandText = “/Customer[@CustID=‘ALFKI’]”; cmd.SchemaPath = “nwind.xml”; cmd.RootTag = “ROOT”; cmd.XslPath = “Customers.xsl”; XmlReader r = cmd.ExecuteXmlReader();

  22. XQuery Over Relational Data

  23. Mapping Schema Bulkload Query Results = + Annotations + XML XPATH /Customers Send Application Scenario –Business to Business Data Interchange SQL Server SQL Server XSD Schema

  24. XML Bulkload • Bulkload XML documents into a SQL Server database via the XML View • Just like traditional bulkload, except supports hierarchical relationships and semi-structured data • Scriptable COM object • Performance is about 75% of traditional bulkload • Can use or ignore database constraints

  25. XML Bulkload & XML Views • Sample Bulkload vbs script: set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBL.ConnectionString = "provider=SQLOLEDB.1;data source=(local);database=tempdb;uid=…;pwd=…" objBL.ErrorLogFile = "error.xml" objBL.XMLFragment = True objBL.CheckConstraints = False objBL.Execute "schema.xsd", "data.xml" Set objBL = Nothing

  26. Application Scenario – Disconnected, Remote Clients SQL Server XML View for traditional 2 tier Data Source XPath or Template queries via ADO UpdateGrams via ADO Disconnected Client application Logical Business Object (XML document Instance) UpdateGrams via HTTP XML View for remote HTTP Data Source Remote SQL Server XPath or Template queries via HTTP

  27. UpdateGrams • Update relational data through XML View • Retrieve all customers as XML File • Change a few customers in XML File • Update relational data • Translated into one or more INSERT, UPDATE, and DELETE statements behind the scenes • Relational operations are transparent to the user • Optimistic concurrency control

  28. Updategrams And XMLViews <updategram-example> <updg:sync mapping-schema=“schema.xsd” xmlns:updg=“urn:schemas-microsoft-com:xml-updategram”> <updg:before> <Customer CustomerID=“LAZYK” Address=“12 Orchestra Terrace”> <Order OrderID=“27” UnitPrice=“100” Quantity=“1000” /> </Customer> </updg:before> <updg:after> <Customer CustomerID=“LAZYK” Address=“8 Opera Court”/> </updg:after> </updg:sync> </updategram-example>

  29. Data Access Methods • HTTP Access via URL • SQL Query • XPath Query • Templates • ADO / OLEDB • .NET access through SQLXML Managed Classes • SOAP

  30. Application Scenario – Data Driven, High Performance Web Sites • “I can build that web site in 4 lines of code!” • Need data driven web site where business logic and data is stored in SQL Server • Have minimal control over existing database API and schema • Want to restrict web site coding to scripting • Extensible, loosely coupled architecture • Web pages are generated via querying XML mapping layer and transforming via XSLT • Clients communicate with web site via XML for SQL Server ISAPI extension with URL parameters

  31. HTTP Access Via URL • URL Query http://localhost/demos?sql=select+*+from+Customers+FOR+XML+Auto&root=root • XML View http://server/vroot/vname/schema.xsd/Customer[@ID='ALFKI']?params • Template http://server/vroot/vname/template.xml?params

  32. XML Templates <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:query> SELECT CompanyName,ContactName,Phone FROM Customers AS Customer WHERE Region LIKE @state FOR XML auto </sql:query> <sql:xpath-query mapping-schema="nwind.xsd"> /Customers[@Region=$state] </sql:xpath-query> </root>

  33. Dataset Update Fill X M L V I E W SqlXmlAdapter SqlXmlCommand XPath XQuery Template XML SQLXML Managed Classes Database XmlReader XML SqlCommand FOR XML

  34. SQLXML Managed Classes SqlXmlCommand cmd = new SqlXmlCommand(ConnStr); cmd.CommandType = SqlXmlCommandType.XPath cmd.CommandText = “/Customer[@CustID=‘ALFKI’]”; cmd.SchemaPath = “nwind.xml”; cmd.RootTag = “ROOT”; cmd.XslPath = “Customers.xsl”; XmlReader r = cmd.ExecuteXmlReader(); // or DataSet ds = new DataSet(); SqlXmlAdapter ad = new SqlXmlAdapter(cmd); ad.Fill(ds); //… ad.Update(ds); // DataSet generates diffgram

  35. Using Templates from .NET SqlXmlCommand cmd = new SqlXmlCommand(ConnStr); cmd.CommandType = SqlXmlCommandType.Template cmd.CommandStream = new FileStream(“tmpl.xml", FileMode.Open, FileAccess.Read); cmd.ExecuteToStream(Response);

  36. SQL Server Web Services • Expose Stored Procedures and XML Templates as Web Services • SQLXML generates WSDL automatically • Get data from SQLServer in 3 lines of code:Dim Service As New localhost.nwindsoap()Dim retval As Int16DataSet ds = Service.MyStoredProc(param, retval)

  37. Web Release Model And New Features • Web Release 1 (WR1) – XML Updategrams, XML Bulkload, significant performance improvements • RTM - February 2001 • SQLXML 2.0 – Annotated XSD support, Client-Side XML, SQLXML OLEDB provider, SQLXML Managed Classes • RTM – October 2001 • SQLXML 3.0 – Web Services (SOAP) • RTM - February 2002 • Future - XQuery, Server side functionality

  38. Additional Resources • Web page • http://www.microsoft.com/sql/techinfo/xml/ • Download SQLXML releases at: • http://msdn.microsoft.com/sqlxml/ • Contact us • PSS • news:microsoft.public.sqlserver.xml • Email us • Documentation • SQL Server 2000 Documentation (Books Online) • Web Release documentation • Books

  39. Books About SQLXML • “Professional XML Databases”Various; Wrox Press; December, 2000 • “Scripting XML and WML for Microsoft SQL Server 2000”, Tobias Martinsson; John Wiley & Sons; January, 2001 • “Programming Microsoft SQL Server 2000 with XML”Graeme Malcolm; Microsoft Press; June, 2001 • “Professional SQL Server 2000 XML”Various; Wrox Press; June, 2001 • “XML and SQL Server 2000”John Griffin; New Riders Pub; July, 2001 • “Working With Microsoft SQL Server and XML”Sams Pub; January, 2002

  40. Resources from Microsoft Press SQL & XML • For more information please visit the TechEd Bookshop. • www.microsoft.com/mspress

  41. Don’t forget to complete the on-line Session Feedback form on the Attendee Web sitehttps://web.mseventseurope.com/teched/

More Related