Solved

XML Column in Oracle - cRYSTAL rEPORT

Posted on 2012-03-22
16
651 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now