DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET"  >
     <ContactName>Paul Henriot</ContactName>
   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5" 
          OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" > 
     <ContactName>Carlos Gonzlez</ContactName>
   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" 
          OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'

-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer',3)
         WITH (CustomerID  varchar(10),
               ContactName varchar(20))
           
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order',3)
         WITH (OrderID  varchar(10),
               CustomerID varchar(20),
               EmployeeID varchar(10),
               OrderDate datetime)
SELECT *
FROM OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',3)
         WITH (	OrderID  varchar(10),
				ProductID  varchar(10),
                Quantity varchar(20))
        

EXEC sp_xml_removedocument @idoc

select x.y.value('res_cd[1]', 'int')
      --,x.y.value('res_version[1]','int') 
      ,@res_version
      ,x.y.value('cat_cd[1]','CHAR(3)') 
      ,x.y.value('org_cd[1]','VARCHAR(20)') 
      ,x.y.value('res_name[1]','VARCHAR(50)') 
      
-- begin null fields      
     ,case when  x.y.value('func_role_cd[1]','int') = -1         
         then Null
      else
         x.y.value('func_role_cd[1]','int')     
      end
     ,case when x.y.value('func_role_version[1]','int') = -1
         then Null
      else
        x.y.value('func_role_version[1]','int')
      end
      
     ,case when x.y.value('approval_ind[1]','char(01)') = ' '
         then Null
      else 
        x.y.value('approval_ind[1]','char(01)')
      end   
     ,case when x.y.value('approval_dt[1]','datetime') = '1900-01-01 00:00:00.000'
         then Null
      else
         x.y.value('approval_dt[1]','datetime')
      end
     ,case when x.y.value('create_dt[1]','datetime') = '1900-01-01 00:00:00.000'
          then Null
      else
         x.y.value('create_dt[1]','datetime')
      end      
     ,case when x.y.value('res_eff[1]','datetime') = '1900-01-01 00:00:00.000'
        then Null
      else
        x.y.value('res_eff[1]','datetime')  
      end
      ,case when x.y.value('res_term[1]','datetime') = '1900-01-01 00:00:00.000'
        then Null
      else
        x.y.value('res_term[1]','datetime')
      end
     ,case when x.y.value('res_desc[1]','varchar(255)') in ('NULL','Null',' ')
        then Null
      else
        x.y.value('res_desc[1]','varchar(255)')
      end
     ,case when x.y.value('opt_ind[1]','char(01)') = ' '
        then Null
      else
        x.y.value('opt_ind[1]','char(01)')
      end
     ,case when x.y.value('comments[1]','varchar(255)') in ('NULL','Null',' ')
       then Null
      else 
         x.y.value('comments[1]','varchar(255)')
      end      
      ,case when x.y.value('status[1]','varchar(20)') in ('NULL','Null',' ')
        then Null
       else
        x.y.value('status[1]','varchar(20)')
      end       
      ,case when x.y.value('attachment_allowed_ind[1]','char(01)') = ' '
        then Null
       else 
         x.y.value('attachment_allowed_ind[1]','char(01)')        
      end   
      ,case when x.y.value('attachment_ind[1]','char(01)') = ' '
         then Null
       else
        x.y.value('attachment_ind[1]','char(01)')
       end 
       ,x.y.value('add_id[1]','char(12)') 
  --    ,x.y.value('add_ts[1]','datetime') 
       ,getdate()
      ,x.y.value('update_id[1]','char(12)') 
      --,x.y.value('update_ts[1]','datetime')               
      ,getdate()
                    
from #temp_xmldata
cross apply data.nodes('update_resource/resource') x(y)

quote:

---

aha...XQUERY

reading...reading....

---

quote:

---

XQuery is About Querying XML

XQuery is a language for finding and extracting elements and attributes from XML documents.

---