XML Extract

[aj250037]

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