Microsoft Press microsoft sql server 2005 PHẦN 5 ppt

Lesson 5: Converting Between XML Data and Relational Data 327

Figure 8-6 shows the result of this query.

Figure 8-6 Results of query that uses the nodes() method with the CROSS APPLY operator

The following code shows how to use the nodes() method from an XML type column

by using the OUTER APPLY operator:

SELECT T.C.value('@id','int') AS ID,

T.C.value('@name','nvarchar(max)') AS [NAME],

T.C.value('count(./Employees/*)', 'int') AS TOTAL_EMPLOYEE_COUNT,

T2.C.query('.') EMPLOYEES_OLDER_THAN_7

FROM @X.nodes('/Departments/Department') T(C)

OUTER APPLY T.C.nodes('./Employees[Employee/@YearsInRole>7]') T2(C)

Figure 8-7 shows the result of this query.

Figure 8-7 Results of query using the nodes() method with the Outer Apply operator

Quick Check

■ Why are the APPLY operators necessary?

Quick Check Answer

■ You need the APPLY operators to correlate the results from the nodes()

method with the results of other XML data type methods being called in

the SELECT statement. Otherwise, you would not be able to call any of the

XML data type methods.

C0862271X.fm Page 327 Friday, April 29, 2005 7:38 PM

328 Chapter 8 Managing XML Data

Shredding XML by Using SQLXML

SQLXML-annotated XSD schemas enable you to bulk load XML data coming from a

file into a database and to transform that data into tabular-relational format when the

data is inserted. To bulk load XML data by using SQLXML, you must execute the fol￾lowing steps:

1. Create the database schema by issuing the required CREATE DATABASE and

CREATE TABLE statements.

2. Update the XML schema file with the necessary annotations to create an anno￾tated XSD schema, as you learned in Lesson 3.

3. Use the SQLXML API to load both the annotated schema and the XML data that

needs to be loaded into the database and to bulk load the XML data into the

database. To do this, follow these steps:

A. Open a .NET Framework 2.0 SDK command-line window and navigate to

C:\Program Files\Common Files\System\Ole DB.

B. Type tlbimp xblkld4.dll to generate a proxy for the COM library; then

press Enter to execute it.

C. The utility should print “Type library imported to SQLXMLBULKLOADLib.dll”

if it succeeded.

D. Add a reference to the SQLXMLBULKLOADLib.dll assembly from the

Visual Studio 2005 project in which you want to bulk load XML data.

E. If the project is an executable assembly, add the [STAThread] attribute to the

Main method. If the SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class

object is being called from a custom secondary thread, use the Thread.Set￾ApartmentState(ApartmentState.MTA) method before starting the thread. If

the project is a Web application, set the ASPCompat attribute of the @Page

directive like this: <%@ Page AspCompat="true">

F. Add the following code to execute the bulk load:

string connectionString = "Provider=sqloledb; Data Source=SERVER;

Initial Catalog=DATABASE; User Id=USER; Password=PWD";

SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL =

new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();

objBL.ConnectionString = connectionString;

objBL.Execute("annotated_XSD_schema.xsd", "XML_data_file.xml");

C0862271X.fm Page 328 Friday, April 29, 2005 7:38 PM

Lesson 5: Converting Between XML Data and Relational Data 329

The SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class object provides different flags

that you can set to enable different functionality, which Table 8-7 describes.

MORE INFO Bulk Loading XML

For more information about the bulk-loading API, see the “SQL Server XML Bulk Load Object

Model” topic in SQL Server Books Online.

Table 8-7 Properties from the SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class Class

Property Description

BulkLoad

SchemaGen

SGDropTables

The combination of these three properties enables you to config￾ure the bulk-load mechanism to generate the relational schema

based on the annotated XSD schema.

■ Set the BulkLoad property to false so that no XML data will

be loaded into the database.

■ Set the SchemaGen property to true so that SQLXML will

issue the required CREATE TABLE Transact-SQL code

based on what is declared on the mapping schema.

■ Set the SGDropTables property to true so that SQLXML will

drop the tables before creating them if they already exist.

XMLFragment If you set the XMLFragment property to true, SQLXML enables

you to bulk load XML fragments (XML data without a root node)

instead of XML documents.

ErrorLogFile Set the ErrorLogFile property to a file name. SQLXML will log in

this file any unhandled errors that occurred during XML bulk

loading.

Transaction

ForceTableLock

SQLXML uses default implicit transactions, so each BULK

INSERT statement will execute in its own transaction.

■ Set the Transaction property to true so that all XML loading

will occur in a single transaction.

■ If necessary, set the ForceTableLock property to true to force

a table-level lock during the bulk insert operation.

C0862271X.fm Page 329 Friday, April 29, 2005 7:38 PM

330 Chapter 8 Managing XML Data

PRACTICE Bulk Loading XML Files

In this practice, you use OPENXML and SQLXML Bulk Load to upload two XML files

into the database. Then you query the data in the UniversalLog table to build some

reports. The queries require you to shred XML into relational data by using the

nodes() method of the XML data type.

NOTE Code available on the companion CD

The Practice Files\Chapter8\Lesson 5\CompleteLesson5.sql file provides the solution for Practice 1

and Practice 3 in this lesson.

Practice 1: Use OPENXML to Load XML Data

The UniversalLog administrator found an XML file that contains old data that must be

uploaded into the database. The XML file must be loaded into memory by using the

SQLXML XML stored procedures. You then need to insert the data into the Universal￾Log table.

1. The C:\Chapter8\Lesson5\UniversalLog.xml file contains 500 log entries that

you need to upload into the UniversalLog table.

2. Load the UniversalLog.xml file into a XML-typed variable in SQL Server. (Les￾son 1 covered how to load XML files by using the OPENROWSET function in

Transact-SQL.)

3. Use the sp_xml_preparedocument stored procedure to load the XML data into

memory.

4. Issue an INSERT..SELECT statement to insert into the UniversalLog table the data

read by using OPENXML. Remember that you must use explicit mapping in the

OPENXML declaration because the XML is in a different format.

5. Use the sp_xml_removedocument stored procedure to clean up the server memory.

6. Execute the queries and then use a SELECT COUNT statement to validate that

the data has been inserted into the table.

NOTE Code available on the companion CD

The Practice Files\Chapter8\Lesson 5\ BulkLoad\SQLXMLBulkLoad.csproj Visual Studio project

file provides the solution for Practice 2 in this lesson.

C0862271X.fm Page 330 Friday, April 29, 2005 7:38 PM

Lesson 5: Converting Between XML Data and Relational Data 331

Practice 2: Use SQLXML Bulk Load to Load XML Data

The UniversalLog administrator found another XML file that contains old data that

must be uploaded into the database. The XML file must be loaded into memory by

using the SQLXML Bulk Load COM component, so you need to write a .NET appli￾cation to do this. The data should be inserted into the UniversalLog table.

1. The C:\Chapter8\Lesson 5\ForBulkLoad.xml file contains 500 log entries that

you need to upload into the UniversalLog table.

2. Use Visual Studio 2005 to write a console application to load the file into mem￾ory. The console application must use the SQL Server Bulk Load component.

Add the following code to execute the bulk load:

SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL =

new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();

objBL.ConnectionString = "Provider=sqloledb;Data Source=DEMOS;Initial Catalog=

TK431Chapter8;User Id=sa;";

objBL.Execute("UniversalLogSchema.xsd", "ForBulkLoad.xml");

3. Use the provided C:\Chapter8\Lesson 5\ UniversalLogSchema.xsd annotated

XSD schema to map the XML data to the relational database.

4. Run the application to upload the XML data into the database.

5. Validate the data load by running a SELECT COUNT statement in SSMS.

Practice 3: Shred XML Data by Using the nodes() Method

The UniversalLog administrator needs to build a reporting application to analyze the

most common errors raised by applications logging into the UniversalLog table. You

need to develop the queries that extract the information needed by the reporting

application.

1. The first report must show four columns, with the application name in the first

column and the logRecord data from all the logged messages divided into three

columns: Error Messages, Post Messages, and Informational Messages.

SELECT

ApplicationName,

LogRecord.value('(/logRecord//error/

message)[1]','nvarchar(max)') As 'Error Messages',

LogRecord.value('(/logRecord//post/

moreInformation)[1]','nvarchar(max)') As 'Post Messages',

LogRecord.value('(/logRecord//information/

message)[1]','nvarchar(max)') As 'Informational Messages'

FROM UniversalLog

2. Use the CROSS APPLY operator to show log records that contain all three types

of messages.

C0862271X.fm Page 331 Friday, April 29, 2005 7:38 PM

332 Chapter 8 Managing XML Data

SELECT

ApplicationName,

Errors.C.value('./message','nvarchar(max)') As 'Error Messages',

Posts.C.value('./moreInformation','nvarchar(max)') As 'Post Messages',

Info.C.value('./message','nvarchar(max)') As 'Informational Messages'

FROM UniversalLog

CROSS APPLY LogRecord.nodes('/logRecord//error') Errors(C)

CROSS APPLY LogRecord.nodes('/logRecord//post') Posts(C)

CROSS APPLY LogRecord.nodes('/logRecord//information') Info(C)

3. Use the OUTER APPLY operator to show all log records and to see the messages

for each record.

SELECT

ApplicationName,

Errors.C.value('./message','nvarchar(max)') As 'Error Messages',

Posts.C.value('./moreInformation','nvarchar(max)') As 'Post Messages',

Info.C.value('./message','nvarchar(max)') As 'Informational Messages'

FROM UniversalLog

OUTER APPLY LogRecord.nodes('/logRecord//error') Errors(C)

OUTER APPLY LogRecord.nodes('/logRecord//post') Posts(C)

OUTER APPLY LogRecord.nodes('/logRecord//information') Info(C)

Lesson Summary

■ OPENXML supports implicit and explicit mapping.

■ Always remember to call the sp_xml_removedocument after using OPENXML.

■ OPENXML loads the whole XML structure into memory.

■ The nodes() method of the XML data type returns a new row for each XML node

that matches a given XQUERY expression. Use the value(), query(), and exist()

methods available in the XML data type to extract data from each row.

■ The APPLY operator enables you to invoke a function for each row returned from

a query.

■ The CROSS APPLY operator returns from the invoked function only those results

that are not NULL.

■ The OUTER APPLY operator returns all results, even if they are NULL.

Lesson Review

The following questions are intended to reinforce key information presented in this

lesson. The questions are also available on the companion CD if you prefer to review

them in electronic form.

C0862271X.fm Page 332 Friday, April 29, 2005 7:38 PM

Lesson 5: Converting Between XML Data and Relational Data 333

NOTE Answers

Answers to these questions and explanations of why each answer choice is right or wrong are

located in the “Answers” section at the end of this book.

1. An application you wrote uses OPENXML to parse XML data into a relational

table. As soon as the XML data got bigger, you started to see that SQL Server was

running out of memory, and your OPENXML query started to return memory

errors. What can you do to improve performance? (Choose all that apply.)

A. When possible, process all XML documents at once instead of splitting the

documents into multiple smaller files.

B. Check that you are calling sp_xml_removedocument as soon as possible after

executing OPENXML.

C. Reduce the size of the XML files by making the XML tag names smaller.

D. When possible, split the XML data into multiple smaller files and process

each of them independently.

2. Under which circumstances should you use the nodes() method instead of

OPENXML? (Choose all that apply.)

A. The XML data is already stored in an XML data type column.

B. You need to extract XML data out of multiple columns, not just a single

source.

C. You need to use an XPATH expression not supported by OPENXML but

supported by the XML data type implementation.

D. You are migrating stored procedure code from a previous version of SQL

Server.

C0862271X.fm Page 333 Friday, April 29, 2005 7:38 PM

334 Chapter 8 Managing XML Data

Lesson 6: Creating XML Indexes

Lessons 2 and 3 examined different alternatives for retrieving XML data out of SQL

Server 2005. Depending on the size of this data, extraction can be a costly operation.

By implementing different indexing options on the XML data type, you can have SQL

Server 2005 resolve queries on XML data types by inspecting only a certain set of

nodes and not navigating through the complete XML document or fragment. In this

lesson, you see the benefits of indexing XML data as well as the best indexes to use for

different scenarios.

After this lesson, you will be able to:

■ Describe the benefits of creating a primary XML index.

■ Define a strategy to create secondary indexes.

■ Choose the appropriate secondary index based on the queries to be executed.

■ Create XML indexes.

Estimated lesson time: 30 minutes

Indexing an XML Data Type Instance

The XML data type in SQL Server 2005 can store a maximum of 2-GB of information.

When XML data is assigned to an XML data type instance, it is transformed into an

internal binary large object (BLOB) representation. When you use XML data type

methods to query the XML data, SQL Server performs a lookup on the table data

rows to extract the required nodes.

You can gain a performance improvement when you index the XML internal struc￾ture. Instead of having to look up the queried data in the 2 GB binary representation

of all the XML data, the SQL Server query processor can perform an index lookup

and probe fewer memory pages to serve the query. You can create two general types of

indexes on the XML data type: a primary index and a secondary index (of which there

are three types).

Indexes improve performance when reading data because fewer data pages must be

read into memory to return the desired result. On the other hand, performance can be

affected under a heavy load of insert, update, and delete operations because of SQL

Server having to update the index structures in addition to the table itself.

C0862271X.fm Page 334 Friday, April 29, 2005 7:38 PM