Home » SQL & PL/SQL » SQL & PL/SQL » XML EXtract problem (NOT urgent. never was never will be)
XML EXtract problem (NOT urgent. never was never will be) [message #272577] Fri, 05 October 2007 10:24 Go to next message
aj250037
Messages: 2
Registered: September 2007
Junior Member
I get a "ORA-00947: not enough values" error when I try the SQL below... The table sdi_rel_tab's row that I am referring to in the SQL below, has the "sdidoc" column set to the contents of the attached XML file.

Can someone please help me?

Thanks very much,
Arthur

INSERT into sdi_rel_tab 
SELECT 'IDaj2300101191566886' as requestid, 
sdi.sdiDoc.extract('//Asset[1]/ProductReference/text()').getstringval() as productreference, 
sdi.sdiDoc.extract('//Asset[1]/SerialNumber/text()').getstringval() as serialnumber, 
sdi.sdiDoc.extract('//Asset[1]/OfferingId/text()').getstringval() as offeringid, 
sdi.sdiDoc.extract('//Asset[1]/Model/text()').getstringval() as "model", 
sdi.sdiDoc.extract('//Asset[1]/Coverage/text()').getstringval() as coverage, 
sdi.sdiDoc.extract('//Asset[1]/CoverageStatus/text()').getstringval() as coveragestatus, 
sdi.sdiDoc.extract('/Site[1]/CountryCode/text()').getstringval() as countrycode, 
   sdi.sdiDoc.extract('/Site[1]/CountryName/text()').getstringval() as countryname, 
   -- get the customer id corresponding to the customer name
   sdi.sdiDoc.extract('/Site[1]/CustomerName/text()').getstringval() as customername, 
    sdi.sdiDoc.extract('/Site[1]/CustomerNumber/text()').getstringval() as customernumber, 
    sdi.sdiDoc.extract('/Site[1]/SiteNumber/text()').getstringval() as sitenumber, 
    sdi.sdiDoc.extract('/Site[1]/SiteShortName/text()').getstringval() as siteshortname, 
    sdi.sdiDoc.extract('/Site[1]/Address1/text()').getstringval() as address1, 
    sdi.sdiDoc.extract('/Site[1]/Address2/text()').getstringval() as address2, 
    sdi.sdiDoc.extract('/Site[1]/City/text()').getstringval() as city,
    sdi.sdiDoc.extract('/Site[1]/State/text()').getstringval() as state,
    sdi.sdiDoc.extract('/Site[1]/PostalCode/text()').getstringval() as postalcode,
    sdi.sdiDoc.extract('/Site[1]/Location/text()').getstringval() as sitelocation,
    sdi.sdiDoc.extract('/Site[1]/SitePhoneNumber/text()').getstringval() as sitephonenumber,
    sdi.sdiDoc.extract('/Site[1]/ContactName/text()').getstringval() as contactname,
    sdi.sdiDoc.extract('/Site[1]/Contactphone/text()').getstringval() as contactphone,
    sdi.sdiDoc.extract('/Site[1]/CSRCode/text()').getstringval() as csrcode,
    sdi.sdiDoc.extract('/Site[1]/CSRName/text()').getstringval() as csrname,
    sdi.sdiDoc.extract('/Site[1]/BranchCode/text()').getstringval() as branchcode 
    --sdi.sdiDoc.extract('/Site[1]/TerritoryCode/text()').getstringval() as territorycode 
FROM sdi_xml_tab sdi where sdiid = 'IDaj2300101191529564'

[Updated on: Fri, 05 October 2007 12:31] by Moderator

Report message to a moderator

Re: XML EXtract problem (URGENT) [message #272583 is a reply to message #272577] Fri, 05 October 2007 10:38 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many columns has sdi_rel_tab table?
How many columns is returned by the select?

URGENT should be avoid, please read and follow OraFAQ Forum Guide.

Regards
Michel
Previous Topic: How to get Package.Procedure's Parameter information?
Next Topic: Select Query to display Aggregate functions for each row
Goto Forum:
  


Current Time: Sat Dec 03 22:31:41 CST 2016

Total time taken to generate the page: 0.09031 seconds