Resolved my question. the SQL needed the column name of the XMLType column instead of - ASSET

HTML Code:
SELECT extractvalue(Value(a1), '*/ProductReference') productreference,
extractvalue(Value(a1), '*/SerialNumber') serialnumber,
extractvalue(Value(a1), '*/Model') "model",
extractvalue(Value(a1), '*/Coverage') coverage,
extractvalue(Value(a1), '*/CoverageStatus') coveragestatus,
extractvalue(Value(s1), '*/CountryCode') countrycode,   
extractvalue(Value(s1), '*/CountryName') countryname,   
extractvalue(Value(s1), '*/CustomerName') customername,    
extractvalue(Value(s1), '*/CustomerNumber') customernumber,    
extractvalue(Value(s1), '*/SiteNumber') sitenumber,    
extractvalue(Value(s1), '*/SiteShortName') siteshortname,    
extractvalue(Value(s1), '*/Address1') address1,    
extractvalue(Value(s1), '*/Address2') address2,    
extractvalue(Value(s1), '*/City') city,    
extractvalue(Value(s1), '*/State') state,    
extractvalue(Value(s1), '*/PostalCode') postalcode,    
extractvalue(Value(s1), '*/Location') sitelocation,    
extractvalue(Value(s1), '*/SitePhoneNumber') sitephonenumber,    
extractvalue(Value(s1), '*/ContactName') contactname,    
extractvalue(Value(s1), '*/Contactphone') contactphone,    
extractvalue(Value(s1), '*/CSRCode') csrcode,    
extractvalue(Value(s1), '*/CSRName') csrname,    
extractvalue(Value(s1), '*/BranchCode') branchcode,    
extractvalue(Value(s1), '*/TerritoryCode') territorycode,    
sdi.sdiid  as reqid 
FROM aradmin.sdi_xml_tab sdi, 
TABLE (xmlsequence (extract (sdidocxml, '*/Assets/Asset'))) a1, 
TABLE (xmlsequence (extract (value(a1), '*/Site'))) s1