Solved

XML Column in Oracle - cRYSTAL rEPORT

Posted on 2012-03-22
16
657 Views
Last Modified: 2012-04-23
Hi,

I have PO Line details that are stored in XML format in 1 column in an Oracle Table.  I need to be able to report through crystal reports off this column showing each line of the PO Order.  Is there any way to break this out and report off it.  Perhaps into a new table.  I have no knowledge of xml


Below is an example of 3 lines of data on a PO Line template, contained in a column:

<basket><line><catalogue_category>true</catalogue_category><item_code><![CDATA[656659]]></item_code><category><![CDATA[3455034550]]></category><catalogue><![CDATA[656659]]></catalogue><description><![CDATA[]]></description><lead_time>2</lead_time><supplier_name>CH2-CLIFFORD HALLAM HEALTHCARE</supplier_name><supplier_code>00000100</supplier_code><contract><![CDATA[]]></contract><supplier_ref><![CDATA[#1007971]]></supplier_ref><item_type>CAT</item_type><source_key>XRF-00000100-3455034550-656659</source_key><qty_of_measure></qty_of_measure><unit_of_issue>CTN/500EA</unit_of_issue><stock_or_not>false</stock_or_not><notes><![CDATA[]]></notes><price>29.6000</price><quantity>1.0</quantity><line_value>29.6</line_value><fixed_price>false</fixed_price><multi_gl_in_use>false</multi_gl_in_use><delivery_date>20120321</delivery_date><auto_receipt>false</auto_receipt><restricted>false</restricted><capital_code></capital_code><mandatory_found>false</mandatory_found><non_mand_found>false</non_mand_found><supplier_xref_found>true</supplier_xref_found><line_in_error>false</line_in_error><line_error_message><![CDATA[]]></line_error_message><amend_mode>false</amend_mode><requisition_number></requisition_number><line_number></line_number><line_cancelled>false</line_cancelled><already_cancelled>false</already_cancelled><supplier_lookup_disabled>false</supplier_lookup_disabled><solicit_gl>true</solicit_gl><price_details><discount>0.0</discount><supplier_code>00000100</supplier_code><category_code>3455034550</category_code><catalogue_code>656659</catalogue_code><unit_of_purchase>CTN/500EA</unit_of_purchase><pack_details0><![CDATA[]]></pack_details0><pack_details1><![CDATA[]]></pack_details1><minimum_order_qty>0.0</minimum_order_qty><minimum_order_value>0.0</minimum_order_value><latest_buy_price>29.6</latest_buy_price><latest_date></latest_date><quantity_bands><band_0>0.0</band_0><band_1>0.0</band_1><band_2>0.0</band_2><band_3>0.0</band_3><band_4>0.0</band_4><band_5>0.0</band_5></quantity_bands><price_bands><band_0>0.0</band_0><band_1>0.0</band_1><band_2>0.0</band_2><band_3>0.0</band_3><band_4>0.0</band_4><band_5>0.0</band_5></price_bands><price>0.0</price></price_details><vat_details><vat_code>A</vat_code><vat_rate>10.0</vat_rate><vat_amount>0.0</vat_amount><vat_inclusive>29.6</vat_inclusive><vat_reclaimable_percent>100.0</vat_reclaimable_percent><non_recoverable_percent>0.0</non_recoverable_percent><vat_exclusive>0.0</vat_exclusive><reclaim_type>Y</reclaim_type><vat_desc>GST Applicable</vat_desc></vat_details><gl_details><gl_code>Z423034550</gl_code></gl_details></line><line><catalogue_category>true</catalogue_category><item_code><![CDATA[656970]]></item_code><category><![CDATA[3455034550]]></category><catalogue><![CDATA[656970]]></catalogue><description><![CDATA[]]></description><lead_time>5</lead_time><supplier_name>CH2-CLIFFORD HALLAM HEALTHCARE</supplier_name><supplier_code>00000100</supplier_code><contract><![CDATA[]]></contract><supplier_ref><![CDATA[#1009560]]></supplier_ref><item_type>CAT</item_type><source_key>XRF-00000100-3455034550-656970</source_key><qty_of_measure></qty_of_measure><unit_of_issue>PKT/200EA</unit_of_issue><stock_or_not>false</stock_or_not><notes><![CDATA[]]></notes><price>65.0000</price><quantity>1.0</quantity><line_value>65.0</line_value><fixed_price>false</fixed_price><multi_gl_in_use>false</multi_gl_in_use><delivery_date>20120324</delivery_date><auto_receipt>false</auto_receipt><restricted>false</restricted><capital_code></capital_code><mandatory_found>false</mandatory_found><non_mand_found>false</non_mand_found><supplier_xref_found>true</supplier_xref_found><line_in_error>false</line_in_error><line_error_message><![CDATA[]]></line_error_message><amend_mode>false</amend_mode><requisition_number></requisition_number><line_number></line_number><line_cancelled>false</line_cancelled><already_cancelled>false</already_cancelled><supplier_lookup_disabled>false</supplier_lookup_disabled><solicit_gl>true</solicit_gl><price_details><discount>0.0</discount><supplier_code>00000100</supplier_code><category_code>3455034550</cate
gory_code><catalogue_code>656970</catalogue_code><unit_of_purchase>PKT/200EA</unit_of_purchase><pack_details0><![CDATA[]]></pack_details0><pack_details1><![CDATA[]]></pack_details1><minimum_order_qty>0.0</minimum_order_qty><minimum_order_value>0.0</minimum_order_value><latest_buy_price>65.0</latest_buy_price><latest_date></latest_date><quantity_bands><band_0>0.0</band_0><band_1>0.0</band_1><band_2>0.0</band_2><band_3>0.0</band_3><band_4>0.0</band_4><band_5>0.0</band_5></quantity_bands><price_bands><band_0>0.0</band_0><band_1>0.0</band_1><band_2>0.0</band_2><band_3>0.0</band_3><band_4>0.0</band_4><band_5>0.0</band_5></price_bands><price>0.0</price></price_details><vat_details><vat_code>A</vat_code><vat_rate>10.0</vat_rate><vat_amount>0.0</vat_amount><vat_inclusive>65.0</vat_inclusive><vat_reclaimable_percent>100.0</vat_reclaimable_percent><non_recoverable_percent>0.0</non_recoverable_percent><vat_exclusive>0.0</vat_exclusive><reclaim_type>Y</reclaim_type><vat_desc>GST Applicable</vat_desc></vat_details><gl_details><gl_code>Z423034550</gl_code></gl_details></line><line><catalogue_category>true</catalogue_category><item_code><![CDATA[600246]]></item_code><category><![CDATA[3320033200]]></category><catalogue><![CDATA[600246]]></catalogue><description><![CDATA[]]></description><lead_time>5</lead_time><supplier_name>CH2-CLIFFORD HALLAM HEALTHCARE</supplier_name><supplier_code>00000100</supplier_code><contract><![CDATA[]]></contract><supplier_ref><![CDATA[#1034951]]></supplier_ref><item_type>CAT</item_type><source_key>XRF-00000100-3320033200-600246</source_key><qty_of_measure></qty_of_measure><unit_of_issue>BX/50EA</unit_of_issue><stock_or_not>false</stock_or_not><notes><![CDATA[]]></notes><price>20.5000</price><quantity>1.0</quantity><line_value>20.5</line_value><fixed_price>false</fixed_price><multi_gl_in_use>false</multi_gl_in_use><delivery_date>20120324</delivery_date><auto_receipt>false</auto_receipt><restricted>false</restricted><capital_code></capital_code><mandatory_found>false</mandatory_found><non_mand_found>false</non_mand_found><supplier_xref_found>true</supplier_xref_found><line_in_error>false</line_in_error><line_error_message><![CDATA[]]></line_error_message><amend_mode>false</amend_mode><requisition_number></requisition_number><line_number></line_number><line_cancelled>false</line_cancelled><already_cancelled>false</already_cancelled><supplier_lookup_disabled>false</supplier_lookup_disabled><solicit_gl>true</solicit_gl><price_details><discount>0.0</discount><supplier_code>00000100</supplier_code><category_code>3320033200</category_code><catalogue_code>600246</catalogue_code><unit_of_purchase>BX/50EA</unit_of_purchase><pack_details0><![CDATA[]]></pack_details0><pack_details1><![CDATA[]]></pack_details1><minimum_order_qty>0.0</minimum_order_qty><minimum_order_value>0.0</minimum_order_value><latest_buy_price>20.5</latest_buy_price><latest_date></latest_date><quantity_bands><band_0>0.0</band_0><band_1>0.0</band_1><band_2>0.0</band_2><band_3>0.0</band_3><band_4>0.0</band_4><band_5>0.0</band_5></quantity_bands><price_bands><band_0>0.0</band_0><band_1>0.0</band_1><band_2>0.0</band_2><band_3>0.0</band_3><band_4>0.0</band_4><band_5>0.0</band_5></price_bands><price>0.0</price></price_details><vat_details><vat_code>A</vat_code><vat_rate>10.0</vat_rate><vat_amount>0.0</vat_amount><vat_inclusive>20.5</vat_inclusive><vat_reclaimable_percent>100.0</vat_reclaimable_percent><non_recoverable_percent>0.0</non_recoverable_percent><vat_exclusive>0.0</vat_exclusive><reclaim_type>Y</reclaim_type><vat_desc>GST Applicable</vat_desc></vat_details><gl_details><gl_code>Z423033200</gl_code></gl_details></line></basket>

Each Template table has the following fields: 'BASKET' (in XML format)  Code, FileName, Create_date, orig_code, PROG_NAME, type_bort
0
Comment
Question by:sochionnaitj
  • 6
  • 6
  • 2
16 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 37755672
I don't believe Crystal can use the XML from within a database field.  However, if you dump that field to an XML file, then Crystal can use it for the source of a report.

You may have to develop an application to read the data from the database and save it in a file.

mlmcc
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37755882
is it a clob type or an xmltype?

can you post the entire xml doc along with what you want to extract from it?
0
 

Author Comment

by:sochionnaitj
ID: 37755932
Hi sdstuber, I have posted above what I need to extract.

I would be happy to do some work such as breaking the column out into a new oracle table before using crystal if that was easier

My understanding is that it is xmltype
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 73

Expert Comment

by:sdstuber
ID: 37756515
do you need every field extracted from what you have above?
0
 

Author Comment

by:sochionnaitj
ID: 37756585
Yes, each line into 1 row
0
 

Author Comment

by:sochionnaitj
ID: 37756631
Sorry, each of the 3 lines in the example with all fields for each
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 37757474
try using this sql


SELECT                    x.*
                     FROM templates,
                          XMLTABLE(
                              '//line'
                              PASSING basket
                              COLUMNS catalogue_category VARCHAR2(5) PATH '/line/catlogue_category',
                                      item_code VARCHAR2(100) PATH '/line/item_code',
                                      category VARCHAR2(100) PATH '/line/category',
                                      catalogue VARCHAR2(100) PATH '/line/catalogue',
                                      description VARCHAR2(4000) PATH '/line/description',
                                      lead_time VARCHAR2(100) PATH '/line/lead_time',
                                      supplier_name VARCHAR2(100) PATH '/line/supplier_name',
                                      supplier_code VARCHAR2(100) PATH '/line/supplier_code',
                                      contract VARCHAR2(100) PATH '/line/contract',
                                      supplier_ref VARCHAR2(100) PATH '/line/supplier_ref',
                                      item_type VARCHAR2(100) PATH '/line/item_type',
                                      source_key VARCHAR2(100) PATH '/line/source_key',
                                      qty_of_measure VARCHAR2(100) PATH '/line/qty_of_measure',
                                      unit_of_issue VARCHAR2(100) PATH '/line/unit_of_issue',
                                      stock_or_not VARCHAR2(5) PATH '/line/stock_or_not',
                                      notes VARCHAR2(4000) PATH '/line/notes',
                                      price NUMBER PATH '/line/price',
                                      quantity NUMBER PATH '/line/quantity',
                                      line_value NUMBER PATH '/line/line_value',
                                      fixed_price VARCHAR2(5) PATH '/line/fixed_price',
                                      multi_gl_in_use VARCHAR2(5) PATH '/line/multi_gl_in_use',
                                      delivery_date NUMBER PATH '/line/delivery_date',
                                      auto_receipt VARCHAR2(5) PATH '/line/auto_receipt',
                                      restricted VARCHAR2(5) PATH '/line/restricted',
                                      capital_code VARCHAR2(100) PATH '/line/capital_code',
                                      mandatory_found VARCHAR2(5) PATH '/line/mandatory_found',
                                      non_mand_found VARCHAR2(5) PATH '/line/non_mand_found',
                                      supplier_xref_found VARCHAR2(5) PATH '/line/supplier_xref_found',
                                      line_in_error VARCHAR2(5) PATH '/line/line_in_error',
                                      line_error_message VARCHAR2(4000) PATH '/line/line_error_message',
                                      amend_mode VARCHAR2(5) PATH '/line/amend_mode',
                                      requisition_number VARCHAR2(100) PATH '/line/requisition_number',
                                      line_number VARCHAR2(100) PATH '/line/line_number',
                                      line_cancelled VARCHAR2(5) PATH '/line/line_cancelled',
                                      already_cancelled VARCHAR2(5) PATH '/line/already_cancelled',
                                      supplier_lookup_disabled VARCHAR2(5) PATH '/line/supplier_lookup_disabled',
                                      solicit_gl VARCHAR2(5) PATH '/line/solicit_gl',
                                      price_discount NUMBER PATH '/line/price_details/discount',
                                      price_supplier_code VARCHAR2(100) PATH '/line/price_details/supplier_code',
                                      price_category_code VARCHAR2(100) PATH '/line/price_details/category_code',
                                      price_catalogue_code VARCHAR2(100) PATH '/line/price_details/catalogue_code',
                                      price_unit_of_purchase VARCHAR2(100) PATH '/line/price_details/unit_of_purchase',
                                      price_pack_details0 VARCHAR2(100) PATH '/line/price_details/pack_details0',
                                      price_pack_details1 VARCHAR2(100) PATH '/line/price_details/pack_details1',
                                      price_minimum_order_qty NUMBER PATH '/line/price_details/minimum_order_qty',
                                      price_minimum_order_value NUMBER PATH '/line/price_details/minimum_order_value',
                                      price_latest_buy_price NUMBER PATH '/line/price_details/latest_buy_price',
                                      price_latest_date VARCHAR2(100) PATH '/line/price_details/latest_date',
                                      quantity_band0 NUMBER PATH '/line/price_details/quantity_bands/band_0',
                                      quantity_band1 NUMBER PATH '/line/price_details/quantity_bands/band_1',
                                      quantity_band2 NUMBER PATH '/line/price_details/quantity_bands/band_2',
                                      quantity_band3 NUMBER PATH '/line/price_details/quantity_bands/band_3',
                                      quantity_band4 NUMBER PATH '/line/price_details/quantity_bands/band_4',
                                      quantity_band5 NUMBER PATH '/line/price_details/quantity_bands/band_5',
                                      price_band0 NUMBER PATH '/line/price_details/price_bands/band_0',
                                      price_band1 NUMBER PATH '/line/price_details/price_bands/band_1',
                                      price_band2 NUMBER PATH '/line/price_details/price_bands/band_2',
                                      price_band3 NUMBER PATH '/line/price_details/price_bands/band_3',
                                      price_band4 NUMBER PATH '/line/price_details/price_bands/band_4',
                                      price_band5 NUMBER PATH '/line/price_details/price_bands/band_5',
                                      vat_code VARCHAR2(5) PATH '/line/vat_details/vat_code',
                                      vat_rate VARCHAR2(5) PATH '/line/vat_details/vat_code',
                                      vat_amount VARCHAR2(5) PATH '/line/vat_details/vat_code',
                                      vat_inclusive VARCHAR2(5) PATH '/line/vat_details/vat_code',
                                      vat_reclaimable_percent VARCHAR2(5) PATH '/line/vat_details/vat_code',
                                      vat_non_recoverable_percent NUMBER
                                              PATH '/line/vat_details/non_recoverable_percent',
                                      vat_exclusive VARCHAR2(5) PATH '/line/vat_details/vat_code',
                                      vat_reclaim_type VARCHAR2(1) PATH '/line/vat_details/reclaim_type',
                                      vat_desc VARCHAR2(4000) PATH '/line/vat_details/vat_code',
                                      gl_code VARCHAR2(5) PATH '/line/gl_details/gl_code') x
0
 

Author Comment

by:sochionnaitj
ID: 37759577
hI,

I am getting the following error:

 PASSING BASKET
                                      *
ERROR at line 5:
ORA-00932: inconsistent datatypes: expected - got LONG

On review, the following columns are all that are necessary - if that makes it easier!

                             item_code VARCHAR2(100) PATH '/line/item_code',
                                      category VARCHAR2(100) PATH '/line/category',
                                      catalogue VARCHAR2(100) PATH '/line/catalogue',
                                      description VARCHAR2(4000) PATH '/line/description',
                                      supplier_name VARCHAR2(100) PATH '/line/supplier_name',
                                      supplier_code VARCHAR2(100) PATH '/line/supplier_code',
                                      contract VARCHAR2(100) PATH '/line/contract',
                                      supplier_ref VARCHAR2(100) PATH '/line/supplier_ref',
                                      item_type VARCHAR2(100) PATH '/line/item_type',
                                      qty_of_measure VARCHAR2(100) PATH '/line/qty_of_measure',
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37759679
I think the problem is your data is NOT XMLTYPE but instead is of type LONG


and, unfortunately, LONG type data does not support much of anything.
0
 

Author Comment

by:sochionnaitj
ID: 37760153
Is there no way of doing it?  Any ideas?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37760492
you could write a pl/sql function that would accept a rowid, read the long column, convert it to a clob and then convert the clob to an xmltype which would be the return value of the function.

then your query would look like the one above, except

instead of

FROM templates,

it would look like...

FROM (select your_function(row_id) basket from templates),


option 2- similar idea,  create an object type to support of all of the columns needed,  do all of the stuff above to convert the long into something useable and do the xml parsing inside the function and pipeline the results as a table function.

option 3 -
create a new table with a clob type,  insert into the new table your long value with the to_lob function  then use the query above  except instead of


FROM templates,
                          XMLTABLE(
                              '//line'
                              PASSING basket

it would be something like...


FROM new_templates,
                          XMLTABLE(
                              '//line'
                              PASSING XMLTYPE(clob_basket)



option 4 - find something in crystal to support the xml, but here I'll have to leave you in the hands of the crystal experts because I can't really help much in that area.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37879466
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 37879467
http:#a37757474 is correct for the xmltype as originally claimed in the question.

The only problems are in the followup where the datatype is long but there are alternates suggested to work with that too
0
 

Author Comment

by:sochionnaitj
ID: 37884333
Please Close - unable to resolve on our environment
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 72
Character matching different date formats for dates between 6 46
Crystal reports vb.net 2 41
Include a logo in email body using Oracle utl_mail 11 30
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question