-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|