| XML extract [message #270704] |
Thu, 27 September 2007 16:09 |
aj250037
Messages: 2
Registered: September 2007
|
Junior Member |
|
|
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:
<?xml version="1.0" encoding="UTF-8"?><Response version="1.0"><Request version="1.0"><SourceSystem>B1</SourceSystem><SourceSystemInstance>9037</SourceSystemInstance><Date>2007-09-27 16:15:33</Date><Identifier>IDaj2300101190924133</Identifier><Type>SiteQuery</Type><MaximumNumberResponses></MaximumNumberResponses ><Filters><CountryCode>US</CountryCode><InstanceId></InstanceId><CustomerName></CustomerName><CustomerNumber></CustomerNumber ><SiteNumber operator="EQ">3434343</SiteNumber><SiteName></SiteName><SiteShortName></SiteShortName><Address2></Address2><City></City><State ></State><SitePostalCode></SitePostalCode><SitePhoneNumber></SitePhoneNumber></Filters></Request>
<Sites><Site><CountryCode>US</CountryCode><CountryName>UNITED STATES</CountryName><CustomerName>PORTAGE NATIONAL BANK OF PORTAGE PA</CustomerName><CustomerNumber>87878</CustomerNumber><SiteNumber>76767676</SiteNumber><SiteShortName>FIRST NATIONAL</SiteShortName><Address2>** RECORD NOT MIGRATED TO ERP !! **</Address2><City>GALLITZIN</City><State>PA</State><PostalCode>16641</PostalCode><Location>75757575</Location><SitePhoneNumber >814-886-8191</SitePhoneNumber><ContactPhone>814-886-8191</ContactPhone></Site><Site><CountryCode>US</CountryCode><CountryName >UNITED STATES</CountryName><CustomerName>PORTAGE NATINAL BANK OF PORTAGE PA</CustomerName><CustomerNumber>87878</CustomerNumber><SiteNumber>75757577</SiteNumber><SiteShortName>FIRST NATIONAL</SiteShortName><Address2>701 MAIN ST</Address2><City>GALLITZIN</City><State>PA</State><PostalCode>16641</PostalCode><Location>454545454</Location><SitePhoneNumber >814-886-8191</SitePhoneNumber><ContactPhone>814-886-8191</ContactPhone></Site></Sites><StatusCode>200</StatusCode><NumberResponsesFound >2</NumberResponsesFound></Response>
[Updated on: Thu, 27 September 2007 16:17] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
