Thursday, November 30, 2006
Transform XML to SQL
Sample to transform XML to SQL.
select
to_number(to_char(to_date(extractvalue(value(a), '//Envelope/DateTime/date'),'yyyy-mm-dd'),'yyyy')) a2,
to_number(extractvalue(value(a), '//Envelope/numberOfDeclarations')) a3,
substr(extractvalue(value(a), '//Envelope/Party[@partyRole="sender"]/partyId'),1,2) partyIdDrz,
trim(substr(extractvalue(value(a), '//Envelope/Party[@partyRole="sender"]/partyId'),3,12)) partyIdDS,
substr(extractvalue(value(a), '//Envelope/Party[@partyRole="sender"]/partyId'),15,3) partyIdOE,
substr(extractvalue(value(a), '//Envelope/Party[@partyRole="receiver"]/partyId'),1,2) a7,
trim(substr(extractvalue(value(a), '//Envelope/Party[@partyRole="receiver"]/partyId'),3,12)) a8,
substr(extractvalue(value(a), '//Envelope/Party[@partyRole="receiver"]/partyId'),15,3) a9,
to_char(to_date(extractvalue(value(a), '//Envelope/DateTime/date'),'yyyy-mm-dd'),'yyyymmdd') a10,
to_char(to_date(extractvalue(value(a), '//Envelope/DateTime/time'),'hh24:mi:ss'),'hh24miss') a11,
extractvalue(value(a), '//Envelope/envelopeID') a12,
to_date(extractvalue(value(a),'//Envelope/DateTime/date')' '
extractvalue(value(a),'//Envelope/DateTime/time'),'yyyy-mm-dd hh24:mi:ss') a14,
extractvalue(value(a),'//Envelope/Party[@partyRole="sender"]/@partyType') DrzposType,
extract(value(a),'//Declaration') DeclarationXMLType
from table (xmlsequence(extract(xmltype(trim('
<Message>
<Header>
<OzDok>oznaka</OzDok>
</Header>
<Body><Data>
<INSTAT xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<Envelope>
<envelopeID>43575</envelopeID>
<DateTime>
<date>2006-08-11</date>
<time>14:41:37</time>
</DateTime>
<Party partyType="PSI" partyRole="sender">
<partyId>SI11111111 000</partyId>
</Party>
<Party partyType="CC" partyRole="receiver">
<partyId>SI47731811 000</partyId>
</Party>
<numberOfDeclarations>1</numberOfDeclarations>
<Declaration>
<declarationId>11111111000060811005</declarationId>
<referencePeriod>200607</referencePeriod>
<PSIId>SI11111111000</PSIId>
<Function>
<functionCode>I</functionCode>
</Function>
<flowCode>2</flowCode>
<currencyCode>SIT</currencyCode>
<totalNumberLines>1</totalNumberLines>
<declarationTypeCode>M</declarationTypeCode>
<firstLast>1</firstLast>
</Declaration>
</Envelope>
</INSTAT>
</Data>
<Signatures />
</Body>
</Message>')), '//INSTAT'))) a
# posted by Domen Dolar @ 10:21 PM 
