1 / 31

Objectives In this lesson, you will learn to:

Objectives In this lesson, you will learn to: Generate an XML document by using data stored in SQL Server Execute XPath queries against data stored in a SQL Server database Insert XML data in a SQL Server database table. Getting Started

mabyn
Download Presentation

Objectives In this lesson, you will learn to:

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. Objectives In this lesson, you will learn to: • Generate an XML document by using data stored in SQL Server • Execute XPath queries against data stored in a SQL Server database • Insert XML data in a SQL Server database table

  2. Getting Started • SQL Server 2000 has introduced features for integrating XML with SQL Server. • Some of these features are: • XPath queries • Direct URL queries • The OpenXML function • The FOR XML clause

  3. Problem Statement 9.D.1 The details about the products sold at CyberShoppe are stored in a SQL Server 2000 database. This data needs to be displayed in a Web browser.

  4. Task List • Identify the mechanism for generating an XML document. • Build the query for generating the XML document. • Execute the query.

  5. Task 1: Identify the mechanism for generating an XML document. • SQL Server provides the SQL-XML mapping rules for extracting XML data from a database and storing XML data in the database. • The FOR XML clause of the SELECT statement is used to extract data from a database and display it as an XML document. • You can retrieve data in three different modes by using the FOR XML clause. • AUTO • RAW • EXPLICIT

  6. Task 1: Identify the… XML document. (Contd.) • The syntax for using the FOR XML clause in the SELECT statement is as follows: SELECT table1.column_name1, table2.column_name2 FROM table1, table2 FOR XML mode Result • You can use the FOR XML clause of the SELECT statement to generate an XML document and display it in a browser.

  7. Task 2: Build the query for generating the XML document. • You need to create a URL query to display the XML document in a browser. • A URL query allows you to embed a SELECT statement along with the URL of a Web server to generate an XML document with the required data. • The syntax of a URL query is as follows: http://<Server-name>/<vdir>?sql=<sql-statement>&root=rootelementname

  8. Task 2: Build the … XML document. (Contd.) Result To display the data stored in a SQL Server 2000 database, you need to create a URL query. To be able to execute the URL query, you need to create a Web site and map the virtual directory of the Web site to the SQL Server database.

  9. Task 3: Execute the query.

  10. Just a Minute… What are the three modes that can be used with the FOR XML clause?

  11. Just a Minute… The books table contains the details about the books sold at CyberShoppe. Write a query to generate an XML document based on the data stored in the books table. Also ensure that all columns from the table are displayed as elements and not attributes.

  12. Just a Minute… Identify the error in the following URL query. http://localhost/EMPLOYEES?sql=SELECT+*+FROM+EMPLOYEES FOR XML AUTO&root=root

  13. Problem Statement 9.D.2 The details about the toys sold at CyberShoppe need to be displayed in a Web browser. The details are stored in the products table of the CyberShoppe database on SQL Server 2000. The CATEGORY column of the products table indicates the category of a product.

  14. Task List • Identify the method for converting only selective data. • Create an XML schema. • Build a query. • Execute the query.

  15. Task 1: Identify the method for converting only selective data. • SQL Server 2000 allows you to generate an XML document based on the data stored in a database. • You can use the FOR XML clause of the SELECT statement to generate an XML document and display it in a browser. • You can use XPath query to restrict the results returned in an XML document. • XPath query is an XML-based query language that can be used to process the data in an XML document. • To be able to use XPath queries, you need to create an XML schema. • XML Data Reduced language (XDR) is used to describe the content of an XML document.

  16. Task 1: Identify the method …data. (Contd.) Result To display only selective nodes from the resulting XML document, you can use XPath query. XPath queries use XML schemas for determining the structure of the XML document and traversing through various nodes. Therefore, you need to create an XML schema. Since SQL Server 2000 supports XDR schema language, you need to create a schema by using XDR.

  17. Task 2: Create an XML schema. The schema Element • Is the root element used in a file containing the schema for XML documents. • Marks the beginning of a schema definition. • The syntax for this element is as follows: <Schema xmlns=“namepspace”>

  18. Task 2: Create an XML schema. (Contd.) The ElementType Element • Allows you to create an element type that can be used in a schema. • The syntax for the ElementType element of XDR is as follows: <ElementType content=”{eltOnly | textOnly | empty | mixed}” dt:type=”datatype” model=”{open | closed}” name=”name” order=”{one | seq | many }”> Content Model Declaration </ElementType>

  19. Task 2: Create an XML schema. (Contd.) The AttributeType Element • Allows you to declare an attribute type, which can be used later in the schema. • The syntax for the AttributeType element of XDR is as follows: <AttributeType dt:type=“datatype” name=“name” required=“{yes | no }” />

  20. Task 2: Create an XML schema. (Contd.) The element Element • Refers to an element type declared earlier in the schema by using the ElementType element. • Is used to declare the content model for another element type. • The syntax for this element is as follows: <element type=“elementtype” minOccurs=“{0 | 1}” maxOccurs=“{1 | *}” />

  21. Task 2: Create an XML schema. (Contd.) The attribute Element • Refers to an attribute type declared earlier in the schema by using the AttributeType element. • Is used to declare the content model for another element type. • The syntax for this element is as follows: <attribute type=“attributetype” required=“{yes | no }” />

  22. Task 3: Build a query. Task 4: Execute the query.

  23. Just a Minute… • The books sold at CyberShoppe are divided in to various categories, such as Science, Fiction, and Biography. The details about these books are stored in a SQL Server 2000 database. Create an XPath query for displaying the details about all fiction books. Also create a schema required for executing the query.

  24. Problem Statement 9.D.3 The branches of CyberShoppe send data about the sales made for various products in an XML document to the head office. On receiving this data, the head office needs to insert the details into a database table.

  25. Task List • Identify the data that needs to be inserted. • Identify the method for inserting XML data into a database. • Build a query. • Execute the query.

  26. Task 1: Identify the data that needs to be inserted. Result As per the scenario, the data that needs to be inserted into the SALES table is the sales details of the various products. The sales details includes the following: • ORDER • SHIP_ADD • PRICE_PER_QTY • QUANTITY

  27. Task 2: Identify the method for inserting XML data into a database. • The OPENXML function is used to retrieve the data from an XML document as a rowset. • SQL Server 2000 provides the following system stored procedures that can be used with the OPENXML function. • sp_xml_preparedocument • sp_xml_removedocument Result • You can use the OPENXML function with the sp_xml_preparedocument stored procedure to insert XML data into a database.

  28. Task 3: Build a query. Task 4: Execute the query.

  29. Just a Minute… • What are the system stored procedures that can be used with the OPENXML function?

  30. Summary In this lesson, you learned that: • You can display data from a SQL Server 2000 database in a Web browser by using the SQL-XML integration feature. • To host a Web page, you need to install Internet Information Server (IIS) on a Windows 2000 or a Windows NT Server 4.0 system. If you have Windows NT Workstation 4.0 installed on a system, you can use Personal Web Server (PWS) instead of IIS. • The Virtual Directory Management option of SQL Server allows you to associate a SQL Server database with a virtual directory.

  31. Summary (Contd.) • The FOR XML clause forms a part of the T-SQL statement in a URL query. • In a URL query, http://<servername>/<virtual-directoryname > should precede the SQL statement. • The white spaces in the SELECT statement of URL Query must be substituted by the ‘+’ sign. • An XPath query is an XML-based query language that can be used to process the data in XML documents. • Data can be inserted in SQL Server 2000 tables using the sp_xml_preparedocument stored procedure and OpenXML function.

More Related