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

SQL Server 2000 XML EnhancementsPeter TyMCDBA, MCSE, MCP+SBTechnology SpecialistMicrosoft Hong Kong Ltd. peterty@microsoft.com

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

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

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

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

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

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

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>

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

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

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"?>

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

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> ...

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> ...

#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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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…

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

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

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

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

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

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

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…

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

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

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

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

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>

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…

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

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

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…

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

Télécharger la présentation

Plus de contenu associé