OracleApps02: August 2018

1) Simple XML Data

create table x1 (a1 number , xml_col xmltype)
xmltype xml_col store as clob

insert into x1 values (1,'<?xml version="1.0"?>
<catalog>
<book id="bk101">
<author>Gambardella, Matthew</author>
<title>XML Developer-s Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth look at creating applications
with XML. </description>
</book>
<book id="bk102">
<author>Ralls, Kim</author>
<title>Midnight Rain</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2000-12-16</publish_date>
<description>A former architect battles corporate zombies,
an evil sorceress, and her own childhood to become queen
of the world. </description>
</book>
</catalog>')

commit

select a1,xml_books.*
from x1 sc,
xmltable(
'$doc/catalog/book'
passing sc.xml_col as "doc"
columns
book_id varchar(10) path '@id',
author varchar(64) path 'author',
title varchar(128) path 'title',
genre varchar(12) path 'genre',
price decimal(7,2) path 'price',
publish_date date path 'publish_date',
description varchar(128) path 'description',
book_sequence_no for ordinality

) as xml_books

2) Complex XML Data

create table xxdm_ar_sample_xml_t (id number,XML_TAGS xmltype)

xmltype XML_TAGS STORE AS CLOB

insert into xxdm_ar_sample_xml_t values (1,'<ns2:financialMessages xmlns:ns2="http://healthinsurance.oracle.com/financial/v1">

</accountingDetail>

</accountingDetail>

</accountingDetails>

</invoiceLine>

</invoiceLine>

</invoiceLines>

</accountingDetail>

</accountingDetails>

</invoice>

</invoices>

</financialMessage>

</ns2:financialMessages>');

SELECT invoice.ohiInvoiceId,

invoice.documentId,

invoice.organization,

invoice.invoiceType,

invoice.invoiceDate,

invoice.currencyCode,

invoice.invoiceAmount,

invoice.paymentTerms

,invoice.description,

invoice.source,

invoice.inv_attribute1,

invoice.inv_attribute2,

invoice.inv_attribute3,

invoice.inv_attribute4,

invoice.inv_attribute5,

invoice.inv_attribute6,

invoice.inv_attribute7,

invoice.inv_attribute8,

invoice.inv_attribute9,

invoice.inv_attribute10,

invoice.inv_attribute12,

invoice.inv_attribute13,

invoice.inv_attribute14,

invoice.inv_attribute15,

invoiceBulkingCriteria.invoiceBulkingGroup,

invoiceBulkingCriteria.counterpartyCode,

invoiceBulkingCriteria.invoiceDestination,

invoice_line.ohiLineId,

invoice_line.lineNumber,

invoice_line.lineType,

invoice_line.amount ,

invoice_line.distributionAccount ,

invoice_line.line_attribute2 ,

invoice_line.line_attribute3 ,

invoice_line.line_attribute4 ,

invoice_line.line_attribute5,

invoice_line.line_attribute6,

invoice_line.line_attribute7 ,

invoice_line.line_attribute8 ,

invoice_line.line_attribute9 ,

invoice_line.line_attribute10 ,

invoice_line.line_attribute12 ,

invoice_line.line_attribute13,

invoice_line.line_attribute14 ,

invoice_line.line_attribute15

FROM xxdm_financialmsg_data x,

XMLTable(XMLNAMESPACES('http://healthinsurance.oracle.com/financial/v1/' AS "b"

,default'http://healthinsurance.oracle.com/financial/v1'),'financialMessages/financialMessage/invoices/invoice'

PASSINGXMLTYPE.createXML(x.FIN_DATA)

COLUMNS ohiInvoiceId PATH'@ohiInvoiceId',

documentId PATH '@documentId' ,

organization PATH '@organization',

invoiceType PATH '@invoiceType',

invoiceDate PATH '@invoiceDate',

currencyCode PATH '@currencyCode',

invoiceAmount PATH '@invoiceAmount',

paymentTerms PATH '@paymentTerms',

description PATH '@description',

source PATH '@source',

inv_attribute1 PATH '@attribute1',

inv_attribute2 PATH '@attribute2',

inv_attribute3 PATH '@attribute3',

inv_attribute4 PATH '@attribute4',

inv_attribute5 PATH '@attribute5',

inv_attribute6 PATH '@attribute6',

inv_attribute7 PATH '@attribute7',

inv_attribute8 PATH '@attribute8',

inv_attribute9 PATH '@attribute9',

inv_attribute10 PATH '@attribute10',

inv_attribute12 PATH '@attribute12',

inv_attribute13 PATH '@attribute13',

inv_attribute14 PATH '@attribute14',

inv_attribute15 PATH '@attribute15',

invoiceBulkingCriteria XMLTYPE PATH 'invoiceBulkingCriteria',

invoice_line XMLTYPE PATH 'invoiceLines/invoiceLine' )invoice ,

XMLTable(XMLNAMESPACES('http://healthinsurance.oracle.com/financial/v1/' AS "b"

,default'http://healthinsurance.oracle.com/financial/v1'),'invoiceBulkingCriteria'

PASSING invoice.invoiceBulkingCriteria

COLUMNS invoiceBulkingGroup PATH'@invoiceBulkingGroup',

counterpartyCode PATH '@counterpartyCode',

invoiceDestination PATH '@invoiceDestination' )invoiceBulkingCriteria ,

XMLTable(XMLNAMESPACES('http://healthinsurance.oracle.com/financial/v1/' AS "b"

,default'http://healthinsurance.oracle.com/financial/v1'),'invoiceLine'

PASSING invoice.invoice_line

COLUMNS ohiLineId PATH'@ohiLineId',

lineNumber PATH '@lineNumber',

lineType PATH '@lineType',

amount PATH '@amount',

distributionAccount PATH '@distributionAccount',

inv_attribute1 PATH '@attribute1',

line_attribute2 PATH '@attribute2',

line_attribute3 PATH '@attribute3',

line_attribute4 PATH '@attribute4',

line_attribute5 PATH '@attribute5',

line_attribute6 PATH '@attribute6',

line_attribute7 PATH '@attribute7',

line_attribute8 PATH '@attribute8',

line_attribute9 PATH '@attribute9',

line_attribute10 PATH '@attribute10',

line_attribute12 PATH '@attribute12',

line_attribute13 PATH '@attribute13',

line_attribute14 PATH '@attribute14',

line_attribute15 PATH '@attribute15')invoice_line
where x.id=1

Convert XML Data into Column Data