retrieving xml data

[aravindk]

Can someone help me understand how I can retrieve data from a xml data type? In this example below, I want to retrieve AddressNo & Area from the table. Sample code below.

I am using Sql server 2005. I need a select statement returns the values from the column. Any help that you can provide is greatly appreciated.



create table #temp2
(
trans xml not null
)

insert into #temp2
select '<Inspection xsi:schemaLocation="blah_blah_blah.xsd" xmlns="_Schema" xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance">

<Request><SchemaVersion>19.5</SchemaVersion></Request>
<LivingQuarters>
<AddressNo>1664</AddressNo>
<Area>849</Area>
</LivingQuarters>
</Inspection>'