Master-Detail from an XMLType column
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Master-Detail from an XMLType column

  1. #1
    Join Date
    Sep 2007
    Posts
    8

    Master-Detail from an XMLType column

    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.

    HTML Code:
    - <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

  2. #2
    Join Date
    Sep 2007
    Posts
    8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width