- Home
- Products
- Integration
- Tutorial
- Barcode FAQ
- Purchase
- Company
Lesson 5: Converting Between XML Data and Relational Data in .NET framework
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.comPDF 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.comFigure 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.comBarcode Scanner In VS .NET Using Barcode recognizer for Visual Studio .NET Control to read, scan read, scan image in VS .NET applications. www.OnBarcode.comFigure 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.comPDF417 Generation In VS .NET Using Barcode drawer for ASP.NET Control to generate, create PDF 417 image in ASP.NET applications. www.OnBarcode.comResults 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.comBarcode Generator In .NET Framework Using Barcode creation for VS .NET Control to generate, create barcode image in .NET applications. www.OnBarcode.comThe 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.comGenerate 2D Barcode In Visual Studio .NET Using Barcode generator for .NET Control to generate, create 2D Barcode image in VS .NET applications. www.OnBarcode.comSELECT 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.com2 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.comFigure 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.comUPC-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.comFigure 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.comCreate QR In Java Using Barcode creator for Java Control to generate, create QR Code ISO/IEC18004 image in Java applications. www.OnBarcode.comResults 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.comMaking UCC - 12 In VS .NET Using Barcode drawer for Reporting Service Control to generate, create GTIN - 12 image in Reporting Service applications. www.OnBarcode.comQuick Check
Draw Code 39 In .NET Using Barcode generation for ASP.NET Control to generate, create Code39 image in ASP.NET applications. www.OnBarcode.comDenso QR Bar Code Creation In None Using Barcode printer for Excel Control to generate, create QR Code image in Excel applications. www.OnBarcode.comWhy 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.
|
|