upc nincs internet 2018 Designing Database Queries in VS .NET

Encoder UPC A in VS .NET Designing Database Queries

2

CREATE PROCEDURE spUpdateContactName(@xml xml) AS BEGIN --Get the ID since we will use this in more than one DECLARE @ID int SET @ID = (SELECT @xml.value('/UpdatedContactInfo[1]/@ContactID', 'int')) --Update the Names with the values in the XML Document UPDATE Person.Contact SET FirstName = (SELECT @xml.value('(/UpdatedContactInfo/Name/FirstName) [1]', 'nvarchar(50)')), LastName = (SELECT @xml.value('(/UpdatedContactInfo/Name/LastName) [1]', 'nvarchar(50)')), MiddleName = (SELECT @xml.value('(/UpdatedContactInfo/Name/MiddleName) [1]', 'nvarchar(50)')) WHERE ContactID = @ID

You would then execute the stored procedure and pass in the XML document using the following Transact-SQL code:

DECLARE @xml xml Set @xml = '<UpdatedContactInfo ContactID="1"><Name><FirstName>Gustavo</ FirstName><MiddleName>G.</MiddleName> <LastName>Achong</LastName></Name></UpdatedContactInfo>' EXEC spUpdateContactName @xml

The result of executing the previous Transact-SQL statements should be that one record was updated. The middle name for the first contact would then be set with a value of G.

In this lab, you will experiment with working with XML data. In the first exercise, you will write a query against an XML column using an XQuery expression. In the second exercise, you will return data from a relational database as XML. The completed lab is available in the \Labs\ 02\Lab3 folder on the companion CD.

You will need to have SQL Server 2005 installed before you can complete this lab. Refer to the Introduction for setup instructions.

In this exercise, you will create a query against an XML column in the Person.Contact table. The query returns data for all contacts with postal address information in the XML column AdditionalContactInfo. Not all contacts have this information embedded in the XML column, so the query uses the exist method to test for this condition. If the postal address exists, then the value method is used to retrieve the street address, city, state, ZIP Code, and country for each contact. Unlike the instructions column in the Production.ProductModel table, AdditionalContactInfo utilizes multiple schemas. For this reason, we use the WITH XMLNAMESPACES clause and declare namespace declarations to reference these schemas. 1. Open SQL Server Management Studio. 2. Connect to the instance of SQL Server 2005 that contains the AdventureWorks database. 3. Select New Query. 4. Add the following code to the query window:

USE AdventureWorks GO WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ ContactInfo' As AW) SELECT c.FirstName + ' ' + c.LastName as "Contact Name", ContactInfo.ref.value( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/a dventure-works/ContactTypes"; (act:homePostalAddress/act:Street)[1]', 'nvarchar(50)') As "Postal Street", ContactInfo.ref.value( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ContactTypes"; (act:homePostalAddress/act:City)[1]', 'nvarchar(50)') As "Postal City", ContactInfo.ref.value( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ContactTypes"; (act:homePostalAddress/act:StateProvince)[1]', 'nvarchar(50)') As "Postal State", ContactInfo.ref.value( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ContactTypes"; (act:homePostalAddress/act:PostalCode)[1]', 'nvarchar(50)') As "Zip Code", ContactInfo.ref.value( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ContactTypes"; (act:homePostalAddress/act:CountryRegion)[1]', 'nvarchar(50)')

2

As "Postal Country" FROM Person.Contact c OUTER APPLY c.AdditionalContactInfo.nodes( '/AW:AdditionalContactInfo') AS ContactInfo(ref) WHERE ContactInfo.ref.exist( 'declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/ adventure-works/ContactTypes"; act:homePostalAddress') = 1 AND c.AdditionalContactInfo is not NULL

5. Click the Execute button. The results window should display three records, which are the only records that contain the postal address information. The results from this query are listed in Table 2-7.

Table 2-7

Postal Street 123 Oak P.O. Box 5 990 5th Avenue Postal City Seattle Edmonds Redmond Postal State Zip Code Country WA WA WA 98001 98431 98052 USA USA USA

In this exercise, you will generate raw XML by executing a query and using the FOR XML clause. By default, this will use the RAW option to return the XML in the format that is implied. 1. Open SQL Server Management Studio. 2. Connect to the instance of SQL Server 2005 that contains the AdventureWorks database. 3. Select New Query. 4. Add the following code to the query window:

USE AdventureWorks GO Select [Contact].FirstName, [Contact].LastName, [Contact].Title, [Contact].EmailAddress, [Address].AddressLine1, [Address].AddressLine2, [Address].City, [Address].StateProvinceID, [Address].PostalCode FROM Person.Contact [Contact] JOIN HumanResources.Employee [Employee] ON [Contact].ContactID = [Employee].ContactID JOIN HumanResources.EmployeeAddress ea ON [Employee].EmployeeID = ea.EmployeeID JOIN Person.Address [Address] ON ea.AddressID = [Address].AddressID FOR XML AUTO