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

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

Lesson 5: Converting Between XML Data and Relational Data
PDF-417 2d Barcode Maker In .NET
Using Barcode generation for .NET Control to generate, create PDF 417 image in .NET applications.
www.OnBarcode.com
Scanning PDF 417 In .NET Framework
Using Barcode recognizer for .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
The (case sensitive) names of the elements in the XML structure, which must match the name of the columns as declared on the returning table schema.
Make Bar Code In VS .NET
Using Barcode generation for Visual Studio .NET Control to generate, create barcode image in .NET applications.
www.OnBarcode.com
Bar Code Scanner In .NET Framework
Using Barcode reader for Visual Studio .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
The following code example shows how to use OPENXML properly with implicit mapping:
Painting PDF 417 In C#.NET
Using Barcode creation for Visual Studio .NET Control to generate, create PDF417 image in VS .NET applications.
www.OnBarcode.com
Creating PDF-417 2d Barcode In Visual Studio .NET
Using Barcode printer for ASP.NET Control to generate, create PDF-417 2d barcode image in ASP.NET applications.
www.OnBarcode.com
DECLARE @h INT EXEC sp_xml_preparedocument @h OUTPUT, @X SELECT * FROM OPENXML( @h , '/Departments/Department/Employees/Employee', 3) WITH ( id INT, YearsInRole int, StartDate datetime) WHERE YearsInRole >= 8 EXEC sp_xml_removedocument @h GO
Generate PDF 417 In VB.NET
Using Barcode maker for .NET Control to generate, create PDF417 image in VS .NET applications.
www.OnBarcode.com
Printing Barcode In Visual Studio .NET
Using Barcode drawer for Visual Studio .NET Control to generate, create bar code image in .NET framework applications.
www.OnBarcode.com
The @h variable will contain the memory handle to the parsed XML structure. The @X variable contains the XML structure. It is of type XML. The OPENXML statement filters the XML structure by executing the given XPATH query. The resulting XML nodes are converted into the table structure declared inside the WITH clause. Figure 8-3 shows the result of this query.
Code128 Drawer In .NET Framework
Using Barcode encoder for .NET framework Control to generate, create Code 128 Code Set A image in VS .NET applications.
www.OnBarcode.com
Data Matrix 2d Barcode Generator In .NET
Using Barcode encoder for .NET framework Control to generate, create Data Matrix 2d barcode image in .NET framework applications.
www.OnBarcode.com
Figure 8-3
Create PDF 417 In VS .NET
Using Barcode generation for .NET framework Control to generate, create PDF 417 image in Visual Studio .NET applications.
www.OnBarcode.com
Drawing Standard 2 Of 5 In .NET Framework
Using Barcode creator for .NET Control to generate, create Standard 2 of 5 image in VS .NET applications.
www.OnBarcode.com
Result of running example OPENXML query with implicit mapping
QR Code ISO/IEC18004 Encoder In Java
Using Barcode creator for Java Control to generate, create QR Code ISO/IEC18004 image in Java applications.
www.OnBarcode.com
PDF417 Decoder In Visual Basic .NET
Using Barcode reader for Visual Studio .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
Explicit Mapping in OPENXML
Barcode Drawer In VS .NET
Using Barcode maker for ASP.NET Control to generate, create bar code image in ASP.NET applications.
www.OnBarcode.com
Painting QR Code JIS X 0510 In None
Using Barcode generation for Online Control to generate, create QR Code image in Online applications.
www.OnBarcode.com
Using the explicit XML mapping syntax, developers can manually specify the relationship between an XML structure and the return table schema. Instead of using the flags parameter and the element names, you must provide a column mapping pattern
EAN 13 Maker In .NET Framework
Using Barcode maker for ASP.NET Control to generate, create European Article Number 13 image in ASP.NET applications.
www.OnBarcode.com
Creating GS1 - 13 In None
Using Barcode maker for Software Control to generate, create European Article Number 13 image in Software applications.
www.OnBarcode.com
8
Generate UCC.EAN - 128 In Objective-C
Using Barcode generation for iPhone Control to generate, create USS-128 image in iPhone applications.
www.OnBarcode.com
DataMatrix Reader In Visual C#.NET
Using Barcode decoder for Visual Studio .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
Managing XML Data
to explicitly specify how to map each column to the XML data, as shown in the following code example:
DECLARE @h INT EXEC sp_xml_preparedocument @h OUTPUT, @X SELECT * FROM OPENXML( @h , '/Departments/Department/Employees/Employee') WITH ( ID INT '../../@id', StartDate datetime, [Name] nvarchar(max) '../../@name', YearsInRole int) WHERE YearsInRole = 8 EXEC sp_xml_removedocument @h GO
Notice that the column declarations inside the WITH clause explicitly indicate how to extract the values from the values contained inside the XML document. The ID column is mapped to the ID attribute of the Department element, and the Name column is mapped to the name attribute of the Department element. Figure 8-4 shows the result of this query.
Figure 8-4
MORE INFO
Result of running example OPENXML query with explicit mapping Edge Table
If the WITH clause is not specified in the OPENXML statement, SQL Server returns an internal relational representation of the XML data called an Edge Table. The Edge Table can be further queried to get information about the structure of the XML data. To learn about the structure of the Edge Table, see the topic OPENXML (Transact-SQL) in SQL Server 2005 Books Online.
Lesson 5: Converting Between XML Data and Relational Data
Shredding XML by Using the XML Data Type s nodes() Method
With the inclusion of the XML data type in SQL Server 2005, Microsoft had to solve a new problem: how to shred the contained XML data from the XML data type instance into a tabular-relational format. Instead of having to convert the XML data into a string type and paying the high price of loading the whole XML structure into memory by using OPENXML, the XML data type provides its own shredding mechanism: the nodes() method. The nodes() method returns a tabular result set. This result set represents a table containing a single column of type XML. A new row is returned for each XML node that matches a given XQUERY expression. Because the result set returned from the nodes() method contains a single column of type XML, you should apply the other methods available in the XML data type value(), query(), exist() to extract data out of each row. The following code shows how to use the nodes() method:
C.value('@id','int') AS ID, C.value('@name','nvarchar(max)') AS [NAME], C.value('count(./Employees/*)', 'int') AS EMPLOYEE_COUNT, C.query('./Employees') AS EMPLOYEE_LIST FROM @X.nodes('/Departments/Department') T(C) GO SELECT
Figure 8-5 shows the result of this query.
Figure 8-5
Result of query that uses the nodes() method
8
Managing XML Data
NOTE
OPENXML and nodes() method performance
The nodes() method is more efficient than the OPENXML statement because it does not have to load into memory and parse the XML structure before querying and shredding it. The XML data type in SQL Server 2005 stores the XML data in an internal structure that looks very similar to an Edge Table.
Using CROSS APPLY and OUTER APPLY Operators
When a table contains a column of type XML, you cannot call the nodes() method in the SELECT expression. Because the nodes() method returns a result set, the SELECT expression will not permit you to call it; thus, you cannot use a query like the following example:
-- Create a new table CREATE TABLE T(C1 XML); -- Query the table SELECT C1.nodes('XQUERY expression') FROM T
Because the nodes() method must be called at the XML column, it is also impossible to call the nodes method in the FROM section of a SELECT expression, so you can t use the following query, either:
-- Create a new table CREATE TABLE T(C1 XML); -- Query the table SELECT * FROM T.C1.nodes('XQUERY expression')
To call the nodes() method on an XML type column, you must use the APPLY operators, which enable 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. By using the APPLY operators, you can invoke the nodes() method for each row returned from a query. The following code shows how to use the nodes() method from an XML type column by using the CROSS 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) CROSS APPLY T.C.nodes('./Employees[Employee/@YearsInRole>7]') T2(C)
Copyright © OnBarcode.com . All rights reserved.