Master-Detail from an XMLType column

[aj250037]

Following is the XML content and SQL I used to extract the xml as rows, but I get an error - ORA-00904: "ASSET": invalid identifier. Not sure what is wrong. The error comes up at the first TABLE command.

- <Response version="1.0">
	+ <Request version="1.0">
	- <Assets>
		- <Asset>
			  <SerialNumber>...</SerialNumber> 
			  <OfferingId>...</OfferingId> 
			  <Model>...</Model> 
			  <CoverageStatus>...</CoverageStatus> 
			- <Site>
				  <CountryCode>...</CountryCode> 
				  <InstanceId>...</InstanceId> 
				  <CustomerName>...</CustomerName> 
				  <CustomerNumber>...</CustomerNumber> 
				  <SiteNumber>...</SiteNumber> 
				  <Address2>...</Address2> 
				  <City>...</City> 
				  <State>...</State> 
				  <PostalCode>...</PostalCode> 
				  <Location>...</Location> 
				  <SitePhoneNumber>...</SitePhoneNumber> 
				  <SiteCovered>...</SiteCovered> 
				  <CSRCode>...</CSRCode> 
				  <CSRName>...</CSRName> 
				  <BranchCode>...</BranchCode> 
				  <TerritoryCode>...</TerritoryCode> 
			  </Site>
		  </Asset>
		  .
		  .
		  .
	  </Assets>
	  <StatusCode>200</StatusCode> 
	  <NumberResponsesFound>84</NumberResponsesFound> 
  </Response>
  
  
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 (Asset, '*/Assets'))) a1, 
TABLE (xmlsequence (extract (value(a1), '*/Asset/Site'))) s1

[aj250037]

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

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