Lesson 2: Retrieving XML Data by Using SQL Server Server-Side Technologies in Visual Studio .NET

Maker PDF-417 2d barcode in Visual Studio .NET Lesson 2: Retrieving XML Data by Using SQL Server Server-Side Technologies

Lesson 2: Retrieving XML Data by Using SQL Server Server-Side Technologies
PDF 417 Creation In .NET
Using Barcode printer for .NET framework Control to generate, create PDF-417 2d barcode image in .NET applications.
www.OnBarcode.com
PDF-417 2d Barcode Recognizer In Visual Studio .NET
Using Barcode reader for Visual Studio .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
when the XQUERY or XPATH expression returned at least one resulting node. A 0 is returned when the XQUERY or XPATH expression did not return any resulting node. The exist() method is usually used in the WHERE clause of the SELECT statement in Transact-SQL to validate that the expression actually has matching nodes. The following code sections walk you through an example of how to apply several XML manipulation techniques: 1. Create a new table with an XML column:
Barcode Encoder In .NET Framework
Using Barcode creation for .NET Control to generate, create barcode image in .NET framework applications.
www.OnBarcode.com
Reading Barcode In .NET
Using Barcode scanner for .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
--CREATE TABLE CREATE TABLE OLDEMPLOYEES(ID INT IDENTITY, EMPLOYEE_DATA XML) GO
Print PDF 417 In Visual C#.NET
Using Barcode generation for VS .NET Control to generate, create PDF417 image in .NET framework applications.
www.OnBarcode.com
PDF-417 2d Barcode Creator In .NET Framework
Using Barcode creator for ASP.NET Control to generate, create PDF417 image in ASP.NET applications.
www.OnBarcode.com
2. Create a new variable of type XML, initialize the variable with the result of a FOR XML query, and insert the XML stored in the variable into the table declared in step 1:
Generating PDF-417 2d Barcode In VB.NET
Using Barcode drawer for VS .NET Control to generate, create PDF 417 image in .NET applications.
www.OnBarcode.com
Make USS Code 39 In .NET Framework
Using Barcode generator for .NET framework Control to generate, create Code-39 image in .NET framework applications.
www.OnBarcode.com
DECLARE @EMPLOYEES XML SET @EMPLOYEES = (SELECT Department.[DepartmentID] 'Department/@id' ,Department.[Name] 'Department/@name', ( SELECT History.[EmployeeID] 'Employee/@id' ,History.[StartDate] 'Employee/@StartDate' ,DATEDIFF(year, History.[StartDate], GetDate()) 'Employee/@YearsInRole' FROM HumanResources.EmployeeDepartmentHistory History WHERE Department.DepartmentID = History.DepartmentID AND History.EndDate IS NULL ORDER BY History.[StartDate] FOR XML PATH(''), TYPE ) 'Department/Employees' FROM HumanResources.Department ORDER BY Department.[DepartmentID] FOR XML PATH (''), TYPE) INSERT OLDEMPLOYEES(EMPLOYEE_DATA) VALUES (@EMPLOYEES)
UPC A Maker In .NET
Using Barcode printer for Visual Studio .NET Control to generate, create UPC-A image in Visual Studio .NET applications.
www.OnBarcode.com
Barcode Drawer In Visual Studio .NET
Using Barcode encoder for .NET Control to generate, create bar code image in .NET applications.
www.OnBarcode.com
3. Execute an XPATH expression on the XML data stored in the table:
Draw Matrix 2D Barcode In .NET Framework
Using Barcode printer for Visual Studio .NET Control to generate, create Matrix 2D Barcode image in Visual Studio .NET applications.
www.OnBarcode.com
Generate European Article Number 8 In VS .NET
Using Barcode creation for Visual Studio .NET Control to generate, create EAN8 image in Visual Studio .NET applications.
www.OnBarcode.com
DECLARE @YEARS INT SET @YEARS = 7 SELECT EMPLOYEE_DATA.query( '/Department[@name = sql:column("D.Name")]// Employee[@YearsInRole>sql:variable("@YEARS")]') FROM HumanResources.Department D, OLDEMPLOYEES
Code 39 Full ASCII Drawer In Visual C#.NET
Using Barcode drawer for .NET framework Control to generate, create Code 3 of 9 image in Visual Studio .NET applications.
www.OnBarcode.com
Generate Matrix Barcode In VS .NET
Using Barcode printer for ASP.NET Control to generate, create Matrix Barcode image in ASP.NET applications.
www.OnBarcode.com
8
Making USS-128 In Java
Using Barcode creator for BIRT reports Control to generate, create EAN / UCC - 14 image in BIRT reports applications.
www.OnBarcode.com
Bar Code Decoder In Java
Using Barcode Control SDK for Java Control to generate, create, read, scan barcode image in Java applications.
www.OnBarcode.com
Managing XML Data
Print QR Code 2d Barcode In Java
Using Barcode encoder for BIRT Control to generate, create Quick Response Code image in BIRT reports applications.
www.OnBarcode.com
Data Matrix ECC200 Encoder In None
Using Barcode creator for Excel Control to generate, create Data Matrix ECC200 image in Microsoft Excel applications.
www.OnBarcode.com
WHERE D.DepartmentID in ( SELECT DepartmentID FROM HumanResources.Department WHERE GroupName = 'Manufacturing' ) AND EMPLOYEE_DATA.exist( '/Department[@name = sql:column("D.Name")]// Employee[@YearsInRole>sql:variable("@YEARS")]') = 1 FOR XML RAW('Candidates'), ROOT('Bonus')
EAN128 Generation In None
Using Barcode encoder for Office Excel Control to generate, create EAN128 image in Microsoft Excel applications.
www.OnBarcode.com
PDF-417 2d Barcode Generation In None
Using Barcode maker for Online Control to generate, create PDF417 image in Online applications.
www.OnBarcode.com
Notice the use of the query() method, the use of the sql:variable function, the use of the sql:column function, and the use of the exist() method. The result you see should be similar to this structure:
<Bonus> <Candidates> <Employee id="1" StartDate="1996-07-31T00:00:00" YearsInRole="10" /> <Employee id="7" StartDate="1998-01-26T00:00:00" YearsInRole="8" /> <Employee id="8" StartDate="1998-02-06T00:00:00" YearsInRole="8" /> <Employee id="10" StartDate="1998-02-07T00:00:00" YearsInRole="8" /> <Employee id="13" StartDate="1998-03-05T00:00:00" YearsInRole="8" /> <Employee id="14" StartDate="1998-03-11T00:00:00" YearsInRole="8" /> <Employee id="15" StartDate="1998-03-23T00:00:00" YearsInRole="8" /> <Employee id="16" StartDate="1998-03-30T00:00:00" YearsInRole="8" /> <Employee id="17" StartDate="1998-04-11T00:00:00" YearsInRole="8" /> <Employee id="18" StartDate="1998-04-18T00:00:00" YearsInRole="8" /> <Employee id="19" StartDate="1998-04-29T00:00:00" YearsInRole="8" /> </Candidates> </Bonus>
Quick Check
1. What is the main difference between using the query() method and using the value() method of the XML data type 2. What function is used to input external values into an XQUERY FLWOR expression
Quick Check Answers
1. The query() method returns an untyped XML fragment as a result. The value() method returns a scalar Transact-SQL typed value. 2. There are two functions sql:variable and sql:column that enable you to include external values from the relational context into the XML expression.
Lesson 2: Retrieving XML Data by Using SQL Server Server-Side Technologies
PRACTICE
Use XQUERY to Query the UniversalLog Table
This practice uses the results of the Lesson 1 practice. If you have not completed that practice, please go back and follow the instructions to complete it. In this exercise, you query the data in the UniversalLog table in the TK4318 database. You create the appropriate queries to return the results in the requested format. Remember that the LogRecord column in the UniversalLog table is of type XML.
NOTE
Code available on the companion CD
For solutions to the exercises in the Lesson 2 practice, see the Practice Files\8\Lesson 2\ CompleteLesson2.sql file on the companion CD.
1. Create a query to retrieve all records from the UniversalLog table by using the query() method and XPATH. The result should resemble this structure:
<UniversalLog> <logRecord machine="server1" timestamp="2000-01-12T12:13:14Z"> <post eventType="appStart"> <moreInformation>All Services starting</moreInformation> </post> </logRecord> <logRecord machine="server2" timestamp="2000-01-13T12:13:14Z"> <post eventType="appStart" /> <information flag="warning"> <message>Duplicate IP address</message> </information> </logRecord> ........... <UniversalLog>
2. Retrieve records from the UniversalLog table by using the query() method and XPATH. Filter the results for log records that apply to Server2. The result should resemble this structure:
<UniversalLog> <logRecord machine="server2" timestamp="2000-01-13T12:13:14Z"> <post eventType="appStart" /> <information flag="warning"> <message>Duplicate IP address</message> </information> </logRecord> ........... <UniversalLog>
8
Managing XML Data
3. Retrieve records from the UniversalLog table by using the query() method and XPATH. Filter the results for log records that notify about a failure. The result should resemble this structure:
<UniversalLog> <logRecord machine="server2" timestamp="2000-01-11T12:13:14Z"> <information flag="failure"> <message>Hard Disk with ID #87230283 is not responding</message> </information> <error number="18763"> <message>Application can not start</message> <module>AppLoader</module> </error> <post eventType="appStart" /> </logRecord> </UniversalLog>
4. Retrieve records from the UniversalLog table by using the query() method and XPATH, but include in the XML structure data stored in the relational infrastructure; for example, include the LogDateTime and ApplicationName columns. The result should resemble this structure:
<UniversalLog> <LogDateTime>2006-01-27T19:29:44.420</LogDateTime> <ApplicationName>SalesApp</ApplicationName> <logRecord machine="server2" timestamp="2000-01-11T12:13:14Z"> <information flag="failure"> <message>Hard Disk with ID #87230283 is not responding</message> </information> <error number="18763"> <message>Application can not start</message> <module>AppLoader</module> </error> <post eventType="appStart" /> </logRecord> </UniversalLog>
5. Retrieve records from the UniversalLog table by using the query() method and XQUERY. Return an XML structure representing a report with all logged errors. The result should resemble this structure:
<UniversalLog> <errorReport issuedby="dbo" date="Jan 27 2006 11:59PM" /> <error number="1001" timestamp="2000-01-14T12:13:14Z" server="server1"> <message>The user does not have enough permissions to execute query</message> <module>DataAccessLayer</module> </error> <error number="18763" timestamp="2000-01-11T12:13:14Z" server="server2"> <message>Application can not start</message> <module>AppLoader</module> </error> </UniversalLog>
Copyright © OnBarcode.com . All rights reserved.