Lesson 5: Converting Between XML Data and Relational Data in .NET framework

Create PDF-417 2d barcode in .NET framework Lesson 5: Converting Between XML Data and Relational Data

Figure 8-6

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

Why are the APPLY operators necessary 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.

8

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 following 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 annotated 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.SetApartmentState(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");

The SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class object provides different flags that you can set to enable different functionality, which Table 8-7 describes.

Table 8-7

Description The combination of these three properties enables you to configure 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.

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. Set the ErrorLogFile property to a file name. SQLXML will log in this file any unhandled errors that occurred during XML bulk loading. 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.

For more information about the bulk-loading API, see the SQL Server XML Bulk Load Object Model topic in SQL Server Books Online.