Some tips and hints about (XML) file as target...

I was playing with the XML as file target in OWB and I noticed some things that are worthwhile mentioning (I thought)... some of these also apply to a generic flat file though...


First things first, if you want to write to file (using the default operator as described here) make sure you understand that we are using UTL_FILE as a mechanism. This means certain things as to how to set up access to directories. More about is in the Oracle documentation on utilities (I think).


Now if you use a file as target, you do NOT have to:



  1. Create a file definition first (provided you are not interested in fancy attribute names etc.)
  2. Synchronize (or create and bind initially) unless you really want to. The reason for doing this is that we will create a file definition for you and it makes lineage more complete (but the code will work without doing this)

What you do need to do is go into mapping configuration and set the data file properties:


FlatFileTgt:


All of this information will be used to generate the actual mapping. Not checking the XML box obviously will give you an ASCII output file.


Before we go to the XML case, do be aware of the fact that the file is defined as having as many fields (and seperators) as the file definition in the mapping. So for a regular flat file with this mapping:


FileTGTMapping:


You get the following result (Note the last ',' in the file!):


1000,999,1,10/1/05,6,
1001,175955,1,10/1/05,6,
1002,175955,1,10/1/05,6,
1003,205852,1,10/20/05,6,
1004,184497,1,11/1/05,8,
1005,180226,1,10/5/05,17,
1006,175955,1,11/1/05,7,
1007,175955,2,9/30/05,4,
1008,406995,1,11/29/05,118,
1009,180226,2,12/12/05,60,
1010,193039,1,12/1/05,118,
1011,197310,1,11/28/05,104,
1012,193039,1,11/2/05,69,


Now if we look at the same mapping, but now creating an XML file (and only for the XML variant) and not mapping an attribute or element in the XML file, you will run into an error upon deployment (PLS-00201: identifier 'NULL$1' must be declared)...


To avoid this and to explicitly model your expected data result (e.g. that last attribute - or any middle attribute in the file) you should create a mapping like this specifically adding a NULL value into the XML file.


XMLMappingWithConstantForNullHandling:


That way you will get the following (just a single 'row' in this case):


<?xml version="1.0" ?>


- <XML_TGT>



- <INOUTGRP1>



  <ORDER_NBR>1000</ORDER_NBR>


  <CUST_NBR>999</CUST_NBR>


  <BILL_TO_ADR>1</BILL_TO_ADR>


  <ORDER_DT>10/1/05</ORDER_DT>


  <PAYMENT_TERMS>6</PAYMENT_TERMS>


  <STATUS />

  </INOUTGRP1>

Note the 'empty' status part for this XML file...  If you work with the operator this way you will get compiling code and an explicit model of what data you are expecting. If you want to exclude attributes from the file, make sure that you remove the attribute from the definition. Since this is only captured (see first part of the post) in the mapping, this can be done quite quickly and efficiently.

Comments:

Thanks for the hint. Can we influence the case of the XML tags, e.g. [CustomerOrder] i.s.o. [CUSTOMERORDER]? (I am using [ ] here as the pointy XML tag markers mess up the HTML... Thanks, Erik

Posted by Erik Ykema on July 14, 2010 at 12:20 AM PDT #

Doesn't look to be any way (the business names could have been used but weren't). You'd have to use the table function as target approach and do it that way. Cheers David

Posted by David Allan on July 14, 2010 at 08:18 AM PDT #

When defining XML file as target is there any way to affect the tag: <?xml version="1.0"?> ? It seems that changing characterset from file properties does not affect to this tag. I am using OWB 11.1.0.7.0.

I would need something like <?xml version="1.0" encoding="ISO-8859-1"?> or <?xml version="1.0" encoding="windows - 1252"?> so this encoding part is missing and it seems that I cannot tell to OWB how to make that happen automatically...

Best regards,

Juha

Posted by Juha on November 03, 2011 at 04:24 AM PDT #

Hi Juha

In OWB 11gR2 the encoding can be specified. I don't think this is possible before that release you would have to have your own table function used as a target that writes the XML.
Like the one mentioned and included in this blog post;
http://blogs.oracle.com/warehousebuilder/entry/leveraging_xdb

Cheers
David

Posted by David on November 03, 2011 at 06:19 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today