Oracle PL/SQL + XML CLOB

[barakuda]

Vārdu sakot, 2 dienas esmu nobūries, negrib Oracle man XMLu parsēt.

Vēsture ir apmēram tāda - ir WebLogic web-serviss, kas nodarbojas ar klienta datu savākšanu un sarakstīšanu datu bāzē (no vēl viena cita servisa), lai pēc tam to var izmantot citas sistēmas. Sākotnējais variants bija, ka serviss parsē SOAP atbildi un raksta DB, bet tas diezko nerullēja dēļ palieliem taimautiem, sevišķi gadījumos, kad par vienu klientu jāsaraksta vairāki ieraksti.

Tādēļ pašlaik tiek pārtaisīts, ka viss XML tiek padots Orākulim ar domu, ka parsēšanu izveiks PL/SQL procedūrā.

Te tad nu sanāk OPĀ - procedūrā nav diezkādas problēmas parsēties cauri XML struktūrai un taisīt INSERT uz tabulu, tikai tad tas nav diezko advancētāk par to, ko darīja web-serviss pirms tam. Tā vietā gribētos pa tiešo taisīt INSERT INTO tbl SELECT from xml

Šeit arī sākas visas problēmas ar roku izliekumu - nu negrib Orākulis saprast ko es viņam saku.

Izmēģinājos visādus variantus, gan ar extract, gan XMLTABLE - nekas nelīdz.

 

Vistuvākais variants, kas atgriež "kaut ko" (1 tukšu ierakstu, heh, vajadzētu būt 84) - tāds "darba" materiāls:

SELECT a.productcode
FROM
(select xmltype(replace(cprt_clob,'soapenv:','soapenv')) xml_tbl from crm_cusposition_refresh_times where cprt_cl_id is NOT null) i,
XMLTABLE('/soapenvEnvelope/soapenvBody'
PASSING i.xml_tbl
COLUMNS
productcode varchar2(100) PATH '/soapenvEnvelope/soapenvBody/multiRef/productcode') a

 

Nevar kāds specs parādīt kā viņam iemānīt to namespace?

XML piemērs:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Body>
<getClientProductResponse soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<getClientProductReturn soapenc:arrayType="ns1:ProdVO[1]" xsi:type="soapenc:Array" xmlns:ns1="urn:komBank.webservices.crm" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
 <getClientProductReturn href="#id0" /> 
</getClientProductReturn>
</getClientProductResponse>
<multiRef id="id0" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xsi:type="ns2:ProdVO" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns2="urn:komBank.webservices.crm">
<accruedint xsi:type="soapenc:string"/>
<assetliab xsi:type="soapenc:string">A</assetliab>
<bencustomer xsi:type="soapenc:string"/>
<category xsi:type="soapenc:string"/>
<collright xsi:type="soapenc:string"/>
<collrightcover xsi:type="soapenc:string"/>
<committedint xsi:type="soapenc:string"/>
<cusno xsi:type="soapenc:string">ABCDEF</cusno>
<datacountry xsi:type="soapenc:string">LV</datacountry>
<datasource xsi:type="soapenc:string">GS</datasource>
<dealamount xsi:type="soapenc:string">1430000.00</dealamount>
<dealccy xsi:type="soapenc:string">EUR</dealccy>
<deallcyamount xsi:type="soapenc:string">1010438.00</deallcyamount>
<dealtolcyrate xsi:type="soapenc:string">0.706600000</dealtolcyrate>
<exported xsi:type="soapenc:string">0</exported>
<field38 xsi:type="soapenc:string"/>
<field39 xsi:type="soapenc:string"/>
<funds xsi:type="soapenc:string">0</funds>
<productcode xsi:type="soapenc:string">ABCDEF.0010400.01</productcode>
<iban xsi:type="soapenc:string"/>
<intdate xsi:type="soapenc:string"/>
<intrate xsi:type="soapenc:string"/>
<liability xsi:type="soapenc:string">ABCDEF</liability>
<limitlevel xsi:type="soapenc:string">PRODUCT</limitlevel>
<limitref xsi:type="soapenc:string">0010400</limitref>
<localccy xsi:type="soapenc:string">LVL</localccy>
<lockedAmount xsi:type="soapenc:string">0</lockedAmount>
<maturitydate xsi:type="soapenc:string">20100630</maturitydate>
<otheramount xsi:type="soapenc:string"/>
<otherccy xsi:type="soapenc:string"/>
<otherpercamount xsi:type="soapenc:string"/>
<othintchgacc xsi:type="soapenc:string"/>
<othintchgamt xsi:type="soapenc:string"/>
<othintchgdte xsi:type="soapenc:string"/>
<othintchgrte xsi:type="soapenc:string"/>
<percamount xsi:type="soapenc:string">1430000.00</percamount>
<perclcyamount xsi:type="soapenc:string">1010438.00</perclcyamount>
<systemid xsi:type="soapenc:string">LIAL</systemid>
<time xsi:type="soapenc:string">20100708</time>
<valuedate xsi:type="soapenc:string">20031224</valuedate>
</multiRef>
</soapenv:Body>
</soapenv:Envelope>

 

Labi, neko, atradu risinājumu.

 

Ja nu kādam interesē:

1. Definējam XSL:

<?xml version='1.0' ?> 
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:a="http://schemas.xmlsoap.org/soap/envelope/">
 <xsl:output method="xml" indent="yes" encoding="utf-8"/>
 <xsl:param name="cus_no"/>
 <xsl:param name="cunit_id"/>
 <xsl:param name="sign_on_name"/>
 <xsl:template match="/">
   <ROWSET>
     <xsl:for-each select="a:Envelope/a:Body/multiRef">
       <ROW>
         <OTHINTCHGACC>
           <xsl:value-of select="othintchgacc"/>
         </OTHINTCHGACC>
         <BENCUSTOMER>
           <xsl:value-of select="bencustomer"/>
         </BENCUSTOMER>
         <OTHINTCHGDTE>
           <xsl:value-of select="othintchgdte"/>
         </OTHINTCHGDTE>
         <DEALCCY>
           <xsl:value-of select="dealccy"/>
         </DEALCCY>
         <INTDATE>
           <xsl:value-of select="intdate"/>
         </INTDATE>
         <CATEGORY>
           <xsl:value-of select="category"/>
         </CATEGORY>
         <OTHERPERCAMOUNT>
           <xsl:value-of select="otherpercamount"/>
         </OTHERPERCAMOUNT>
         <OTHERAMOUNT>
           <xsl:value-of select="otheramount"/>
         </OTHERAMOUNT>
         <DEALTOLCYRATE>
           <xsl:value-of select="dealtolcyrate"/>
         </DEALTOLCYRATE>
         <FIELD39>
           <xsl:value-of select="field39"/>
         </FIELD39>
         <FIELD38>
           <xsl:value-of select="field38"/>
         </FIELD38>
         <CUSNO>
           <xsl:value-of select="cusno"/>
         </CUSNO>
         <LIABILITY>
           <xsl:value-of select="liability"/>
         </LIABILITY>
         <COMMITTEDINT>
           <xsl:value-of select="committedint"/>
         </COMMITTEDINT>
         <SYSTEMID>
           <xsl:value-of select="systemid"/>
         </SYSTEMID>
         <PERCLCYAMOUNT>
           <xsl:value-of select="perclcyamount"/>
         </PERCLCYAMOUNT>
         <ASSETLIAB>
           <xsl:value-of select="assetliab"/>
         </ASSETLIAB>
         <OTHINTCHGRTE>
           <xsl:value-of select="othintchgrte"/>
         </OTHINTCHGRTE>
         <DEALLCYAMOUNT>
           <xsl:value-of select="deallcyamount"/>
         </DEALLCYAMOUNT>
         <FUNDS>
           <xsl:value-of select="funds"/>
         </FUNDS>
         <LOCALCCY>
           <xsl:value-of select="localccy"/>
         </LOCALCCY>
         <LOCKEDAMOUNT>
           <xsl:value-of select="lockedAmount"/>
         </LOCKEDAMOUNT>
         <DATACOUNTRY>
           <xsl:value-of select="datacountry"/>
         </DATACOUNTRY>
         <PERCAMOUNT>
           <xsl:value-of select="percamount"/>
         </PERCAMOUNT>
         <OTHINTCHGAMT>
           <xsl:value-of select="othintchgamt"/>
         </OTHINTCHGAMT>
         <LIMITREF>
           <xsl:value-of select="limitref"/>
         </LIMITREF>
         <COLLRIGHTCOVER>
           <xsl:value-of select="collrightcover"/>
         </COLLRIGHTCOVER>
         <COLLRIGHT>
           <xsl:value-of select="collright"/>
         </COLLRIGHT>
         <OTHERCCY>
           <xsl:value-of select="otherccy"/>
         </OTHERCCY>
         <VALUEDATE>
           <xsl:value-of select="valuedate"/>
         </VALUEDATE>
         <DEALAMOUNT>
           <xsl:value-of select="dealamount"/>
         </DEALAMOUNT>
         <TIME>
           <xsl:value-of select="time"/>
         </TIME>
         <MATURITYDATE>
           <xsl:value-of select="maturitydate"/>
         </MATURITYDATE>
         <DATASOURCE>
           <xsl:value-of select="datasource"/>
         </DATASOURCE>
         <ACCRUEDINT>
           <xsl:value-of select="accruedint"/>
         </ACCRUEDINT>
         <INTRATE>
           <xsl:value-of select="intrate"/>
         </INTRATE>
         <IBAN>
           <xsl:value-of select="iban"/>
         </IBAN>
         <LIMITLEVEL>
           <xsl:value-of select="limitlevel"/>
         </LIMITLEVEL>
         <EXPORTED>
           <xsl:value-of select="exported"/>
         </EXPORTED>
       </ROW>
     </xsl:for-each>
   </ROWSET>
 </xsl:template>
</xsl:stylesheet>

2. Transformējam:

DECLARE
 XSL xmltype;    -- XSL
 L_XML XMLTYPE;  -- Inputs
 L2_XML XMLTYPE; -- temps
BEGIN
...
 L2_XML := L_XML.TRANSFORM(XSL);
...

3. Selektējam:

...
SELECT a.cusno
FROM 
XMLTABLE('$d/ROWSET/ROW'
      PASSING L2_XML AS "d"
      COLUMNS
      CUSNO varchar2(100) PATH 'CUSNO') a

Labots Jūlijs 13, 2010 - barakuda