XML Extract
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: XML Extract

  1. #1
    Join Date
    Sep 2007
    Posts
    8

    XML Extract

    Hi!,
    Following works great for a single Site tag record, but when I have more than one Site tag in the sdiDoc column(see below), how do I change this SQL to insert each Site as seperate record in sdi_rel_tab table?


    INSERT INTO sdi_rel_tab
    SELECT sdi.sdiDoc.extract('//Site/CountryCode/text()').getstringval() as countrycode,
    sdi.sdiDoc.extract('//Site/CountryName/text()').getstringval() as countryname,
    -- get the customer id corresponding to the customer name
    sdi.sdiDoc.extract('//Site/CustomerName/text()').getstringval() as customername,
    sdi.sdiDoc.extract('//Site/CustomerNumber/text()').getstringval() as customernumber,
    sdi.sdiDoc.extract('//Site/SiteNumber/text()').getstringval() as sitenumber,
    sdi.sdiDoc.extract('//Site/SiteShortName/text()').getstringval() as siteshortname,
    sdi.sdiDoc.extract('//Site/Address1/text()').getstringval() as address1,
    sdi.sdiDoc.extract('//Site/Address2/text()').getstringval() as address2,
    sdi.sdiDoc.extract('//Site/City/text()').getstringval() as city,
    sdi.sdiDoc.extract('//Site/State/text()').getstringval() as state,
    sdi.sdiDoc.extract('//Site/PostalCode/text()').getstringval() as postalcode,
    sdi.sdiDoc.extract('//Site/Location/text()').getstringval() as sitelocation,
    sdi.sdiDoc.extract('//Site/SitePhoneNumber/text()').getstringval() as sitephonenumber,
    sdi.sdiDoc.extract('//Site/ContactName/text()').getstringval() as contactname,
    sdi.sdiDoc.extract('//Site/Contactphone/text()').getstringval() as contactphone,
    sdi.sdiDoc.extract('//Site/CSRCode/text()').getstringval() as csrcode,
    sdi.sdiDoc.extract('//Site/CSRName/text()').getstringval() as csrname,
    sdi.sdiDoc.extract('//Site/BranchCode/text()').getstringval() as branchcode,
    sdi.sdiDoc.extract('//Site/TerritoryCode/text()').getstringval() as territorycode,
    sdiid as requestid
    FROM sdi_xml_tab sdi where sdiid = 'IDaj2300101190923984';

    thanks for your help.

    P.S.
    structure of sdi_xml_tab:

    CREATE TABLE "B1SYSTEM"."SDI_XML_TAB"
    ( "SDIID" VARCHAR2(60 BYTE) NOT NULL ENABLE,
    "SDIDOC" "SYS"."XMLTYPE" ,
    CONSTRAINT "SDI_XML_TAB_PK" PRIMARY KEY ("SDIID")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "B1" ENABLE
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "B1"
    XMLTYPE COLUMN "SDIDOC" STORE AS CLOB (
    TABLESPACE "B1" ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 10
    NOCACHE LOGGING
    STORAGE(INITIAL 49152 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;

    Sample data in the sdiDoc column:
    see attachment
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Lookup xmlsequence and xmltable in oracle docs they are part of sql2003 standard

    You need to format your post using code tags

    Try this (i did not understand your requirement completely its very hard due to missing formatting)

    Code:
    SELECT countryname,customername
    from sdi_xml_tab sdi ,
    table(XMLSequence(
    sdi.sdiDoc.extract('//Site/CountryName/text()').getstringval() as countryname,
    sdi.sdiDoc.extract('//Site/CustomerName/text()').getstringval() as customername)
    where 
    existsnode(sdi.sdiDoc, '/Response/[Identifer="IDaj2300101190924133"]') = 1
    regards
    Hrishy

  3. #3
    Join Date
    Sep 2007
    Posts
    8
    Thanks Hrishy!
    That helps

    Regards,
    Arthur

  4. #4
    Join Date
    Sep 2007
    Posts
    8
    Actually that gives me an "ORA-0097: missing right paranthesis" error

    Any ideas?

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    I had put a extra paranthesis previously here is the corrected version

    Code:
    SELECT countryname,customername
    from sdi_xml_tab sdi ,
    table(XMLSequence(
    sdi.sdiDoc.extract('//Site/CountryName/text()').getstringval() as countryname,
    sdi.sdiDoc.extract('//Site/CustomerName/text()').getstringval() as customername
    where 
    existsnode(sdi.sdiDoc, '/Response/[Identifer="IDaj2300101190924133"]') = 1
    If you can post clearly using code tags
    a)create table statement
    b)insert statements to get xml inside the table
    c)the result you wnat to display

    Maybe i can just work out the sql for you.

    On the face of it the way i understand your problem looks like what you want can be done by XMLSequence or the new ansi standard XMLTable function

    regards
    Hrishy

  6. #6
    Join Date
    Sep 2007
    Posts
    8
    Hrishy,
    I was able to use the SQL below to get my job done.

    Code:
    SELECT sdi.sdiDocxml.extract('//Site[3]/CountryCode/text()').getstringval() as countrycode,   
    sdi.sdiDocxml.extract('//Site[3]/CountryName/text()').getstringval() as countryname,   
    sdi.sdiDocxml.extract('//Site[3]/CustomerName/text()').getstringval() as customername,    
    sdi.sdiDocxml.extract('//Site[3]/CustomerNumber/text()').getstringval() as customernumber,    
    sdi.sdiDocxml.extract('//Site[3]/SiteNumber/text()').getstringval() as sitenumber,    
    sdi.sdiDocxml.extract('//Site[3]/SiteShortName/text()').getstringval() as siteshortname,    
    sdi.sdiDocxml.extract('//Site[3]/Address1/text()').getstringval() as address1,    
    sdi.sdiDocxml.extract('//Site[3]/Address2/text()').getstringval() as address2,    
    sdi.sdiDocxml.extract('//Site[3]/City/text()').getstringval() as city,    sdi.sdiDocxml.extract('//Site[3]/State/text()').getstringval() as state,    sdi.sdiDocxml.extract('//Site[3]/PostalCode/text()').getstringval() as postalcode,    
    sdi.sdiDocxml.extract('//Site[3]/Location/text()').getstringval() as sitelocation,    
    sdi.sdiDocxml.extract('//Site[3]/SitePhoneNumber/text()').getstringval() as sitephonenumber,    
    sdi.sdiDocxml.extract('//Site[3]/ContactName/text()').getstringval() as contactname,    
    sdi.sdiDocxml.extract('//Site[3]/Contactphone/text()').getstringval() as contactphone,    
    sdi.sdiDocxml.extract('//Site[3]/CSRCode/text()').getstringval() as csrcode,    sdi.sdiDocxml.extract('//Site[3]/CSRName/text()').getstringval() as csrname,    
    sdi.sdiDocxml.extract('//Site[3]/BranchCode/text()').getstringval() as branchcode,    
    sdi.sdiDocxml.extract('//Site[3]/TerritoryCode/text()').getstringval() as territorycode,   
     'IDaj2300101192285613' as requestid, 'IDaj2300101192285613_'||3 as sdiidentifierrow 
    FROM aradmin.sdi_xml_tab sdi 
    where sdiid = 'IDaj2300101192285613'
    Thanks,
    Arthur
    Last edited by hrishy; 10-17-2007 at 08:43 AM.

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