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

Lesson 5: Converting Between XML Data and Relational Data
PDF 417 Creator In VS .NET
Using Barcode generator for VS .NET Control to generate, create PDF-417 2d barcode image in VS .NET applications.
www.OnBarcode.com
PDF 417 Decoder In .NET Framework
Using Barcode decoder for Visual Studio .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
Figure 8-6 shows the result of this query.
Bar Code Generation In .NET Framework
Using Barcode maker for .NET Control to generate, create bar code image in VS .NET applications.
www.OnBarcode.com
Barcode Scanner In VS .NET
Using Barcode recognizer for Visual Studio .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
Figure 8-6
Create PDF-417 2d Barcode In Visual C#
Using Barcode generation for .NET Control to generate, create PDF 417 image in .NET framework applications.
www.OnBarcode.com
PDF417 Generation In VS .NET
Using Barcode drawer for ASP.NET Control to generate, create PDF 417 image in ASP.NET applications.
www.OnBarcode.com
Results of query that uses the nodes() method with the CROSS APPLY operator
PDF 417 Drawer In Visual Basic .NET
Using Barcode maker for VS .NET Control to generate, create PDF 417 image in .NET applications.
www.OnBarcode.com
Barcode Generator In .NET Framework
Using Barcode creation for VS .NET Control to generate, create barcode image in .NET applications.
www.OnBarcode.com
The following code shows how to use the nodes() method from an XML type column by using the OUTER APPLY operator:
GS1 RSS Drawer In VS .NET
Using Barcode creation for .NET Control to generate, create GS1 DataBar Truncated image in .NET applications.
www.OnBarcode.com
Generate 2D Barcode In Visual Studio .NET
Using Barcode generator for .NET Control to generate, create 2D Barcode image in VS .NET applications.
www.OnBarcode.com
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)
PDF417 Drawer In .NET
Using Barcode generator for .NET Control to generate, create PDF 417 image in .NET applications.
www.OnBarcode.com
2 Of 5 Interleaved Drawer In VS .NET
Using Barcode generation for .NET Control to generate, create Uniform Symbology Specification ITF image in Visual Studio .NET applications.
www.OnBarcode.com
Figure 8-7 shows the result of this query.
Print Data Matrix ECC200 In None
Using Barcode maker for Font Control to generate, create Data Matrix image in Font applications.
www.OnBarcode.com
UPC-A Supplement 2 Generation In None
Using Barcode generation for Online Control to generate, create UPC-A Supplement 5 image in Online applications.
www.OnBarcode.com
Figure 8-7
Decode Bar Code In Java
Using Barcode Control SDK for Java Control to generate, create, read, scan barcode image in Java applications.
www.OnBarcode.com
Create QR In Java
Using Barcode creator for Java Control to generate, create QR Code ISO/IEC18004 image in Java applications.
www.OnBarcode.com
Results of query using the nodes() method with the Outer Apply operator
EAN-13 Decoder In Visual Basic .NET
Using Barcode scanner for .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Making UCC - 12 In VS .NET
Using Barcode drawer for Reporting Service Control to generate, create GTIN - 12 image in Reporting Service applications.
www.OnBarcode.com
Quick Check
Draw Code 39 In .NET
Using Barcode generation for ASP.NET Control to generate, create Code39 image in ASP.NET applications.
www.OnBarcode.com
Denso QR Bar Code Creation In None
Using Barcode printer for Excel Control to generate, create QR Code image in Excel applications.
www.OnBarcode.com
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.
Quick Check Answer
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 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");
Lesson 5: Converting Between XML Data and Relational Data
The SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class object provides different flags that you can set to enable different functionality, which Table 8-7 describes.
Table 8-7
Properties from the SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class Class
Property BulkLoad SchemaGen SGDropTables
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.
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. 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.
ErrorLogFile
Transaction ForceTableLock
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.
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.
Copyright © OnBarcode.com . All rights reserved.