1 / 31

XML Support in SQL Server 2000

XML Support in SQL Server 2000. Sriram Krishnan Kevin Menard. SQL Server - XML. SQL Server 2000 is an XML-enabled DBMS: It can read and write XML data It can return data from databases in XML format It can read and update data stored in XML documents. FOR XML

quang
Download Presentation

XML Support in SQL Server 2000

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. XML Support in SQL Server 2000 Sriram Krishnan Kevin Menard CS-561 - Advanced Databases

  2. SQL Server - XML • SQL Server 2000 is an XML-enabled DBMS: • It can read and write XML data • It can return data from databases in XML format • It can read and update data stored in XML documents CS-561 - Advanced Databases

  3. FOR XML An extension to SELECT - allows result sets as XML OpenXML Allows reading and writing of data in XML documents XPath queries Allows SQL Server databases to be queried using XPath syntax Schemas Supports XDR mapping schema and XPath queries against them Updategrams XML templates for data modifications XML Bulk Load A high-speed facility for loading XML data into a SQL Server SQL Server's XML Features CS-561 - Advanced Databases

  4. SELECT … FOR XML • FOR XML causes SELECT to return query results as an XML stream • Three formats: RAW, AUTO, or EXPLICIT • SELECT column list FROM table list WHERE filter criteria FOR XML RAW | AUTO | EXPLICIT [, XMLDATA] [, ELEMENTS] [, BINARY BASE64] CS-561 - Advanced Databases

  5. FOR XML -- Raw Mode • SELECT CustomerId, CompanyName FROM Customers FOR XML RAW: XML_F52E2B61-18A1-11d1-B105-00805F49916B ------------------------------------------------------------------ <row CustomerId="ALFKI" CompanyName="Alfreds Futterkiste"/> <row CustomerId="ALMRT" CompanyName="Antonio Moreno Taquer'a"/> <row CustomerId="BERGS" CompanyName="Berglunds snabbköp"/> • Columns  Attributes • Rows  Generic “row element” • XML returned is not well-formed • Lacks a root element – must be generated by the client CS-561 - Advanced Databases

  6. FOR XML -- Auto Mode • SELECT CustomerId, CompanyName FROM Customers FOR XML AUTO XML_F52E2B61-18A1-11d1-B105-00805F49916B ------------------------------------------------------------------ <Customers CustomerId="ALFKI" CompanyName="Alfreds Futterkiste"/> <Customers CustomerId= "ALMRT" CompanyName="Antonio Moreno Taquer'a"/> <Customers CustomerId="BERGS" CompanyName="Berglunds snabbköp"/> • Each row in the result set is named after the table or view • For results with more than one row, this amounts to having more than one top-level (root) element in the fragment, which isn't allowed in XML • The rows from joined tables are nested within one another. CS-561 - Advanced Databases

  7. FOR XML -- Auto Mode (cont.) • Example of a JOIN query: SELECT Customers.CustomerID, CompanyName, OrderId FROM Customers JOIN Orders ON (Customers.CustomerId=Orders.CustomerId) FOR XML AUTO XML_F52E2B61-18A1-11d1-B105-00805F49916B ------------------------------------------------------------------ <Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"> <Orders OrderId="10643"/> <Orders OrderId="10702"/> <Orders OrderId="10952"/> </Customers> <Customers CustomerID="ANATR" CompanyName="Ana Trujillo Emparedado <Orders OrderId="10308"/> <Orders OrderId="10759"/> </Customers> <Customers CustomerID="FRANR" CompanyName="France restauration"> <Orders OrderId="10671"/> <Orders OrderId="10971"/> </Customers> CS-561 - Advanced Databases

  8. FOR XML – Explicit Mode • More flexible and more complicated than either raw mode or auto mode • Explicit mode queries define XML documents in terms of a “universal table format” • A mechanism for describing the format of XML document returned • A universal table is just a SQL Server result set with special column headings that tell the server how to produce an XML document from your data • Element!Tag!Attribute!Directive CS-561 - Advanced Databases

  9. Universal Table Format Tag Parent Customers!1!CustomerId Customers!1 Orders!2!OrderId Orders!2!OrderDate!element ----------------------------------------------------------------------------------------- 1 NULL ALFKI Alfreds Futterkiste NULL NULL 2 1 ALFKI NULL 10643 1997-08-25T00:00:00 2 1 ALFKI NULL 10692 1997-10-03T00:00:00 1 NULL ANATR Ana Trujillo Empare NULL NULL 2 1 ANATR NULL 10308 1996-09-18T00:00:00 CS-561 - Advanced Databases

  10. Explicit Mode – Complex Query • Links the Customers and Orders tables using the CustomerId column • The Tag and Parent values in the second query link it to the first SELECT 1 AS Tag, NULL AS Parent, CustomerId AS [Customers!1!CustomerId], CompanyName AS [Customers!1], NULL AS [Orders!2!OrderId], NULL AS [Orders!2!OrderDate!element] FROM Customers UNION SELECT 2 AS Tag, 1 AS Parent, CustomerId, NULL, OrderId, OrderDate FROM Orders ORDER BY [Customers!1!CustomerId], [Orders!2!OrderDate!element] FOR XML EXPLICIT XML_F52E2B61-18A1-11d1-B105-00805F49916B ------------------------------------------------------------------ <Customers CustomerId="ALFKI">Alfreds Futterkiste <Orders OrderId="10643"> <OrderDate>1997-08-25T00:00:00</OrderDate> </Orders> <Orders OrderId="10692"> <OrderDate>1997-10-03T00:00:00</OrderDate> </Orders> </Customers> <Customers CustomerId="ANATR">Ana Trujillo Emparedados y helados <Orders OrderId="10308"> <OrderDate>1996-09-18T00:00:00</OrderDate> </Orders> </Customers> CS-561 - Advanced Databases

  11. OpenXML • OpenXML is a built-in Transact-SQL function that can return an XML document as a rowset • Syntax: • OpenXML(hdoc, RowPattern [, Flag] [WITH SchemaDeclaration | TableName] • hdoc = Handle to XML Document • Returned from sp_xml_preparedocument • RowPattern = XPath expression that identifies rows • Flag = Attribute or element-centric column patterns • WITH = Shredded rowset based upon additional parameters (omission of WITH = edge table view) CS-561 - Advanced Databases

  12. OpenXML Example DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc output, '<songs> <artist name="Johnny Hartman"> <song> <name>It Was Almost Like a Song</name></song> <song> <name>I See Your Face Before Me</name></song> <song> <name>Easy Living</name></song> </artist> <artist name="Harry Connick, Jr."> <song> <name>Sonny Cried</name></song> <song> <name>A Nightingale Sang</name></song> <song> <name>You Didn't Know Me When</name></song> </artist> </songs>' SELECT * FROM OPENXML(@hdoc, '/songs/artist/song', 2) WITH (artist varchar(30) '../@name', song varchar(50) 'name') EXEC sp_xml_removedocument @hDoc artist song --------------------------- ------------------------------------ Johnny Hartman It Was Almost Like a Song Johnny Hartman I See Your Face Before Me Johnny Hartman Easy Living Harry Connick Jr. Sonny Cried Harry Connick Jr. A Nightingale Sang, Harry Connick Jr. You Didn't Know Me When CS-561 - Advanced Databases

  13. XML Mapping Schema • XML schemas are XML documents that define the type of data that other XML documents may contain • Replacement for DTD • A mapping schema is a special type of schema that maps data between an XML document and a relational table • Can be used to create an XML view of a SQL Server table • SQL Server's XML schema support is based on XML-Data Reduced (XDR) • An XML-Data subset that can be used to define schemas CS-561 - Advanced Databases

  14. Annotated Mapping Schema • An annotated schema is a mapping schema with special annotations (from the XML-SQL namespace) that link elements and attributes with tables and columns • Table  element (default) • Column  attribute (default) • Provides same level of granularity as FOR … XML EXPLICIT, without having to use universal tables CS-561 - Advanced Databases

  15. Annotated Mapping Schema (cont.) • Example Schema: <?xml version="1.0"?> <Schema name="customers" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <ElementType name="Customer“ sql:relation="Customers"> <AttributeType name="CustomerNumber“ sql:field="CustomerId"/> <AttributeType name="Name" sql:field="CompanyName"/> </ElementType> </Schema> CS-561 - Advanced Databases

  16. Querying Using XPath • XPath is a tree navigation language defined by W3C • SQL Server uses XPath to select data from XML views provided by annoted schema • http://localhost/Northwind/Schema/Customer.XDR/Customer[@Id=A%25] • XPath query can be passed via URL or template or via SQLOLEDB provider CS-561 - Advanced Databases

  17. Updategrams • Updategrams provide an XML-based method to update database • Templates with special attributes and elements • Specify the data to update, how to update it • All the execution mechanisms available with templates work equally well with updategrams • POST, save to file and execute via URL, via ADO CS-561 - Advanced Databases

  18. Updategrams (cont.) • Each updategram: • Contains the data changes in the form of before and after elements. • Before element contains the before image of the data to be changed • Row deletions • Have before image but no after image • Row Insertions • Have an after image but no before image CS-561 - Advanced Databases

  19. Updategrams (cont.) <?xml version="1.0"?> <employeeupdate xmlns:updg= "urn:schemas-microsoft-com:xml-updategram"> <updg:sync> <updg:before> <Employees EmployeeID="4"/> </updg:before> <updg:after> <Employees City="Scotts Valley" Region="CA"/> </updg:after> </updg:sync> </employeeupdate> • Updategrams can also be parameterized: <updg:header> <updg:param name="OrderID"/> <updg:param name="ShipCity"/> </updg:header> CS-561 - Advanced Databases

  20. Updategrams (cont.) • Updategrams can also use XDR mapping schemas: <?xml version="1.0"?> <orderupdate xmlns:updg= "urn:schemas-microsoft-com:xml-updategram"> <updg:sync updg:mapping-schema="OrderSchema.xml"> <updg:before> <Order OID="10248"/> </updg:before> <updg:after> <Order City="Reims"/> </updg:after> </updg:sync> </orderupdate> • Where the XDR schema is given by: <?xml version="1.0"?> <Schema xmlns="urn:schemas-microsoft-com:xml-data" xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <ElementType name="Order" sql:relation="Orders"> <AttributeType name="OID"/> <AttributeType name="City"/> <attribute type="OID" sql:field="OrderID"/> <attribute type="City" sql:field="ShipCity"/> </ElementType> </Schema> CS-561 - Advanced Databases

  21. XML Bulk Load • Updategrams and OpenXML, are not suitable for loading large amounts of data • SQLXML provides a facility called the XML Bulk Load • COM object • The first step in using the XML Bulk Load is to define a mapping schema that maps the XML data to tables and columns in database • When the component loads the XML data, it will read it as a stream and use the mapping schema to decide where the data goes in the database CS-561 - Advanced Databases

  22. XML Bulk Load (cont.) • VB Example: Set objBulkLoad=CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad") objBulkLoad.ConnectionString = "provider=SQLOLEDB;data source=SuperServer;database=Northwind;" objBulkLoad.Execute "d:\xml\OrdersSchema.xdr", "d:\xml\OrdersData.xml" Set objBulkLoad = Nothing CS-561 - Advanced Databases

  23. Accessing SQL Server Over HTTP • SQL Server's ability to publish data over HTTP is made possible through SQLISAPI with IIS • An Internet Server API (ISAPI) extension • SQLISAPI uses SQLOLEDB, SQL Server's native OLE DB provider, to access the database associated with a virtual directory • Configuring a virtual directory allows SQL Server's XML features via HTTP CS-561 - Advanced Databases

  24. Accessing SQL Server over HTTP (cont.) • Private Intranet • Send a SELECT … FOR XML query string in URL • Post an XML query template to SQLISAPI • Public Internet • Specify a server-side XML schema • Specify a server-side XML query template CS-561 - Advanced Databases

  25. URL Queries • URL queries allow users to specify a complete Transact-SQL query via a URL • http://localhost/Northwind?sql=SELECT+*+FROM+Customers+WHERE+CustomerId='ALFKI'+OR+CustomerId='ANATR'+FOR+XML+AUTO &root=CustomerList • The first parameter we pass here is sql • The second parameter specifies the name of the root element for the XML document that will be returned CS-561 - Advanced Databases

  26. URL Queries (cont.) • URL query can also include the xsl parameter • Translates the XML document that's returned by the query into a different format • http://localhost/Northwind?sql=SELECT+CustomerId,+CompanyName+FROM+Customers+FOR+XML+AUTO&root=CustomerList&xsl=CustomerList.xsl • http://localhost/Northwind?sql=SELECT+CustomerId,+CompanyName+FROM+Customers+FOR+XML+AUTO&root=CustomerList&xsl=CustomerList.xsl&contenttype=text/xml CS-561 - Advanced Databases

  27. Executing Stored Procedures via URL • Stored Procedure: CREATE PROC ListCustomersXML @CustomerId varchar(10)='%', @CompanyName varchar(80)='%' AS SELECT CustomerId, CompanyName FROM Customers WHERE CustomerId LIKE @CustomerId AND CompanyName LIKE @CompanyName FOR XML AUTO • URL for executing stored procedure: http://localhost/Northwind?sql=EXEC+ListCustomersXML +@CustomerId='A%25',@CompanyName='An%25'&root=CustomerList CS-561 - Advanced Databases

  28. Template Queries • Templates are XML documents based on the XML-SQL namespace • Mechanism for translating a URL into a query that SQL Server can process • Safer and more widely used technique for retrieving data over HTTP • End users never see the source code • Templates are stored on the Web server • Referenced via a virtual name CS-561 - Advanced Databases

  29. Sample Template <?xml version='1.0' ?> <CustomerList xmlns:sql='urn:schemas-microsoft-com:xml-sql'> <sql:query> SELECT CustomerId, CompanyName FROM Customers FOR XML AUTO </sql:query> </CustomerList> • Example invocation: http://localhost/Northwind/templates/CustomerList.XML • Specify a style sheet to apply to a template query: http://localhost/Northwind/Templates/CustomerList3.XML?xsl=Templates/CustomerList3.xsl&contenttype=text/html CS-561 - Advanced Databases

  30. Templates • Parameterized Templates • Permit the user to supply parameters to the query <?xml version='1.0' ?> <CustomerList xmlns:sql='urn:schemas-microsoft-com:xml-sql'> <sql:header> <sql:param name='CustomerId'>%</sql:param></sql:header> <sql:query> SELECT CustomerId, CompanyName FROM Customers WHERE CustomerId LIKE @CustomerId FOR XML AUTO </sql:query> </CustomerList> • Example invocation: http://localhost/Northwind/Templates/CustomerList2.XML? CustomerId=A%25 CS-561 - Advanced Databases

  31. Conclusions • SQL Server 2000 has a lot of ways to work with XML, suitable for a number of situations • Questions? • References: • Conrad, Andrew. A Survey of Microsoft SQL Server 2000 XML Features: Microsoft Corporation, 2001 • http://msdn.microsoft.com/xml/default.aspx?pull=/library/en-us/dnexxml/html/xml07162001.asp • Henderson, Ken. Guru's Guide to SQL Server Architecture and Internals, The (Chapter: Using SQL Server's XML Support) • Rys, Michael. Bringing the Internet to Your Database: Using SQL Server 2000 and XML to Build Loosely-Coupled Systems: Microsoft Corporation CS-561 - Advanced Databases

More Related