1 / 72

SQL Server 2000 XML Enhancements Peter Ty MCDBA, MCSE, MCP+SB Technology Specialist Microsoft Hong Kong Ltd. peterty@m

SQL Server 2000 XML Enhancements Peter Ty MCDBA, MCSE, MCP+SB Technology Specialist Microsoft Hong Kong Ltd. peterty@microsoft.com. What You Will See Today. XML Support in Microsoft SQL Server 2000 XML/XSLT fundamentals Available features

LionelDale
Download Presentation

SQL Server 2000 XML Enhancements Peter Ty MCDBA, MCSE, MCP+SB Technology Specialist Microsoft Hong Kong Ltd. peterty@m

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 EnhancementsPeter TyMCDBA, MCSE, MCP+SBTechnology SpecialistMicrosoft Hong Kong Ltd. peterty@microsoft.com

  2. What You Will See Today • XML Support in Microsoft SQL Server 2000 • XML/XSLT fundamentals • Available features • Development techniques used to take advantage of these features • SQL Server 2000 integration with other tools through XML

  3. Session Prerequisites • XML Basics • Transact-SQL (T-SQL) Language • Microsoft Visual Basic Language (Basics) • Microsoft Visual Basic Development Environment • SQL Server Tools

  4. Agenda • XML Overview • XML in SQL Server 2000 • Database Publishing • OpenXML Rowsets • SQL Server 2000 XML Solutions • BizTalk Server 2000 Integration

  5. XML OverviewOrigin of XML • SGML Originated as a Markup Language for Documents • Many other languages derive from it • XML, HTML, etc. • The Main Goal Is to Unify the Data and Document Transport Format • XML is a W3C standard

  6. SGML XML HTML CSS XSLT XML Overview Origin of XML • XML Originated as a Standard Language for Data Representation

  7. XML OverviewXML Basics • XML Structures the Content • In HTML: <p> Jan 15, 2000 </p> • In XML: <OrderDate> Jan 15, 2000 <OrderDate> • XML Does Not Display or Transform Data • XML separates data from formatting and transforming • HTML and XML are derived from SGML

  8. XML OverviewXML Syntax • XML Is Composed of Tags and Attributes • Tags can be nested • Representing entities, entity properties, and entity hierarchy <ROOT> <Orders OrderID="10643" CustomerID="ALFKI" EmployeeID="6" OrderDate="1997-08-25T00:00:00" RequiredDate="1997-09-22T00:00:00" ShippedDate="1997-09-02T00:00:00" /> </ROOT>

  9. Document Elements } Attributes Text ... Text Text XML OverviewNode Structure of XML Documents • XML Is Parsed into a Tree Structure • Nodes of the tree contain the data

  10. Namespaceuri=“…” root Comment customer list customer customer id id 345 120 name orders first last order order date date Jane Doe 07/16/98 07/23/98 XML OverviewNode Structure of XML Documents • Parsing XML into Nodes

  11. XML OverviewXML Basics • Use XSLT to Display and Transform Data • For example, XSLT can tell Internet Explorer how to format each tag and eventually how to transform it <?xml-stylesheet type="text/xsl" href="t12.xsl"?>

  12. XML OverviewXSLT Basics • XSLT Rules Are Applied at the End of the Process • Once the XML Document Is Parsed and the DOM Is Instantiated with Document Data, XSLT Transformations Are Applied XML FinalOutput (HTML) DOM XSLT

  13. XML OverviewXSLT Basics • Example • This XSLT code first sets the table formatting, then it defines the content of each table cell ... <TABLE STYLE="border:1px solid black"> <xsl:for-each select="ROOT/customers"> <TR > <TD><xsl:value-of select="CustomerID"/></TD> <TD ><xsl:value-of select="ContactName"/></TD> <TD><xsl:value-of select="CompanyName"/></TD> </TR> </xsl:for-each> </TABLE> ...

  14. XML OverviewXSLT Basics ... <TABLE STYLE="border:1px solid black"> <xsl:for-each select="ROOT/customers"> <TR > <TD><xsl:value-of select="CustomerID"/></TD> <TD ><xsl:value-of select="ContactName"/></TD> <TD><xsl:value-of select="CompanyName"/></TD> </TR> </xsl:for-each> </TABLE> ...

  15. #1: Draw an empty table X X X X X #2: Select all elements from ROOT/Customers node #3: Populate each cell in the HTML table with the element’s text value XML OverviewXSLT Basics • Three Basic Steps for XSLT Transformation Table X

  16. XML OverviewXPath Basics • An XPath Provides a Simple Mechanism for Finding and Addressing Specific Parts of an XML Document • The XPath Selects Element Nodes from a Document • Specifies a path in the node tree • Filters nodes with a selection criteria based on element and attribute values

  17. XML OverviewXML Schemas • XML Schemas Describe the Structure of an XML Document • XML schemas describe the tag and attribute specifications • XML schemas also describe constraints on the contained text • XML schemas and the DTD are mutually exclusive

  18. XML Overview DOM Basics • Document Object Model • World Wide Web Consortium (W3C) Language-Independent Interface • Provides Access to XML Structure Through an Object-Oriented Model • Implemented in msxml.dll • Microsoft.XMLDOM

  19. XML Overview Microsoft DOM Implementation XMLDOMDocument / XMLDOMNode –XMLDOMNodeList – XMLDOMNode – XMLDOMNodeList – XMLDOMAttribute – XMLDOMElement – XMLDOMNodeList –XMLDOMNodeMap – XMLDOMNamedNodeMap – XMLDOMAttribute

  20. XML Overview Microsoft DOM Implementation • XMLDOM = XML Document Object Model • XMLDOMDocument – top node in the tree • XMLDOMNode – represents a node in the tree • XMLDOMNodeList – collection of nodes • XMLDOMNamedNodeMap – collection of attribute nodes

  21. parentNode nodeType = Element nodeName = customer nodeValue = null hasChildNodes = true previousSibling nextSibling attributes firstChild childNodes NamedNodeMap lastChild NodeList ... ... XML OverviewAnatomy of an Element Node

  22. XML Overview Using DOM in Visual Basic • Obtain a Reference to an XML Document • Load or Create the Document • Navigate Through Its Nodes • Document • Elements • Attributes • Retrieve and Modify the XML Data

  23. XML Overview Getting Data from an XML Source • Load the Object with loadXML • XML String Parameter Allows Any URL That Returns a Valid XML Document • Use any database publishing method in SQL Server 2000 • Once the Document Is Initialized, It Can Be Navigated

  24. Agenda • XML Overview • XML in SQL Server 2000 • Database Publishing • OpenXML Rowsets • SQL Server 2000 XML Solutions • BizTalk Server 2000 Integration

  25. XML in SQL Server 2000 Business Scenarios • Web Applications with Dynamic Data • Browser-based applications that require data from a database • Business-to-Business Data Processing • Data interchange using XML as a • Ubiquitous • Extensible • Platform-independent data transport mechanism

  26. XML in SQL Server 2000 Areas of Support • Publishing a Database • Provides HTTP access through URLs to templates and annotated schemas • T-SQL Language Extensions • FOR XML Clause in a SELECT Statement • Retrieves XML data from the database engine • New OpenXML Syntax in T-SQL • Stores data into SQL Server

  27. XML in SQL Server 2000 Publishing a Database • Many Forms of HTTP Access – Each with a Different Purpose • URL queries are intended for debugging and easy access in development or testing environments • They are not intended for production sites • Templates and annotated schemas are intended for production sites • They provide safe access by hiding T-SQL code

  28. XML in SQL Server 2000 FOR XML Clause • SELECT Statements Now Have a New Clause • FOR XML clause tells SQL Server 2000 the results • Should be formatted as XML • FOR XML clause supports some modifiers: AUTO, RAW, EXPLICIT • Example SELECT * FROM customers FOR XML AUTO

  29. XML in SQL Server 2000 OpenXML Syntax • OpenXML Clause Provides a Rowset View of an XML Document • Can be used wherever a rowset provider such as a table, view, or OpenRecordset appears • T-SQL Provides Stored Procedures and Clauses to Manipulate XML Data • With T-SQL, you can modify data

  30. Agenda • XML Overview • XML in SQL Server 2000 • Database Publishing • OpenXML Rowsets • SQL Server 2000 XML Solutions • BizTalk Server 2000 Integration

  31. Database PublishingSetting Up HTTP Access • HTTP Access Is Provided by an ISAPI Filter that Maps the Request to SQL Server 2000 Through OLE DB • An MMC Snap-In Provides the HTTP Configuration Support with a Graphical Interface • This tool creates the Microsoft Internet Information Server virtual subdirectory with the ISAPI filter

  32. 4 9 8 5 7 6 2 1 3 ISAPI Filter Customer OLE DB SQL Internet IIS Then Data GoesBack to Customer Database PublishingSetting Up HTTP Access • Example • User sends HTTP request to retrieve SQL Server 2000 data

  33. Database PublishingSetting Up HTTP Access • HTTP URL Format • http://domain/vroot/vname/… • SQL IIS Admin Features • Managing the virtual root • Security and settings • Virtual root physical path • Registering virtual names • Restarting HTTP access to SQL Server 2000 • Managing multiple servers

  34. Database PublishingSetting Up HTTP Access Demo • Create HTTP Access for the Northwind Database • Allow URL Queries to this Virtual Root • Browse the Customers Table • Shows What HTTP Access Is and How To Set It Up on a Particular Server Demo…

  35. Database PublishingFOR XML Clause • Besides the ISAPI Application and Its Virtual Directory, the Engine Has to Know Which Data Format to Use • SELECT Clause Now Supports the FOR XML Clause • It Requests XML Results from SQL Server 2000 SELECT * FROM customers FOR XML AUTO

  36. Database PublishingFOR XML Clause • FOR XML Clause Supports Three Different Modifiers • Raw • Transforms each row in the result set into an XML element with the generic identifier row • Auto • Returns query results in a simple, nested XML tree • Explicit • Specifies the shape of the XML tree

  37. Database PublishingFOR XML Clause • Supports Two Optional Arguments • SchemaOption • Uses XMLData Schema specification • With this option, XMLData schema will be returned • Elements • Columns are returned as sub-elements instead of XML attributes • Auto mode only

  38. Database PublishingFOR XML Modes • EXPLICIT • Allows complete control over XML format of XML result • Values in columns can be mapped to attributes or sub-elements • Supports arbitrary nesting including siblings and collapsing of hierarchy • Construction of ID/IDREF relationships • Supports CDATA sections in XML output • Nesting done based on PK/FK relationships

  39. Database PublishingEXPLICIT Example select 1 as TAG, NULL as PARENT, ‘P-’+ProductID as [Product!1!pid!id], ProductName as [Product!1!name!element], NULL as [OrderDetail!2!oid]from Products union all select 2, 1, ‘P-’+P.ProductID, NULL , OD.OrderIDfrom Products P inner join OrderDetails OD on Products.ProductID=OrderDetails.ProductID order by [Product!1!pid!id] for xml explicit

  40. Database Publishing EXPLICIT Example (Results) <Product pid="P-2"> <Name>Chang</Name> <OrderDetail oid="10258"/> </Products>…

  41. Database Publishing FOR XML Clause Demo • Code Walkthrough of SQL XML Viewer • Execute SQL XML Viewer • Use different FOR XML options • Shows How To • Use FOR XML clause • Use the DOM Inside Visual Basic • Manage SQL Server 2000 XML in Visual Basic Demo…

  42. Database PublishingVirtual Names • Establish the Initial Mapping to Subdirectories Containing Annotated Schemas and Templates • Virtual Names can be set by the SQL IIS Admin, given a name, a path to directory or file, and a type • Supports Dbobject, Schema, or Template types • Thus, Templates or Annotated Schemas Can Be Referenced in the URL http://localhost/Northwind/Customer/cust.xml

  43. Database PublishingUsing Templates • Templates Are Equivalent to Method Calls • Use Templates to Avoid Having the T-SQL Code in the T-SQL Address Query String • Then the browser shows only the resulting code from the template • Like ASP Pages, the Actual Template Requesting Code Is Not Shown

  44. Database PublishingUsing Templates • Other Advantages of Using Templates • Can store several queries • Can contain an associated XSLT file, as with any other XML file • Supports parameters and stored procedures • Supports query schemas through XPaths

  45. Database PublishingUsing Templates • Templates Are Accessed by Creating a Virtual Name in SQL IIS Admin • The virtual name maps a directory where files are stored • Templates Are Then Accessed by References in the URL Using the Virtual Name http://localhost/Northwind/Templates/customer.xml

  46. Database PublishingUsing Templates • Template Example <?xml-stylesheet type="text/xsl" href="Orders.xsl"?> <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:header> <sql:param name="CustomerID">AAAA</sql:param> </sql:header> <sql:query>select OrderId, CustomerID, OrderDate from Orders where CustomerID=@CustomerID order by OrderDate for XML auto, elements </sql:query> </ROOT>

  47. Database PublishingUsing the Templates Demo • Walkthrough the customer.xml Template File • Walkthrough the Customer.xsl Transformation File • Create a Virtual Name Template • Browse the customer.xml File Demo…

  48. Database PublishingAnnotated Schemas • Provide Mapping Between XML and Relational Schema • Uses annotations • Between elements and attributes in the XMLdata schema • To tables and columns in a database • Relationships between XML hierarchy and relational tables

  49. Database PublishingAnnotated Schemas • sql:relation • Establishes a mapping between an element and a database table • sql:field • Establishes a mapping between an element and a database field • sql:relationship • Defines a relationship between two tables or views in the database

  50. Database PublishingAnnotated Schema Demo • Create a Virtual Name for Demo Schemas • Walkthrough the Schema • Browse Schemas with Internet Explorer • Demonstrates How Different Elements in the Schema Map to Different Database Objects Demo…

More Related