-
1 Attachment(s)
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
-
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
-
Thanks Hrishy!
That helps
Regards,
Arthur
-
Actually that gives me an "ORA-0097: missing right paranthesis" error
Any ideas?
-
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
-
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
