Aligning columns and Element Contents in XSU

[DairylandDan]

Hey Folks,

I'm finding myself in a double tight spot, and so, I'm coming to the one site where I know I can always get assistance. Due to company shuffles and a dead-line, I'm working on a project which is not in my area of expertise. My background is SQL Server - this project is Oracle 10g Release 2 attempting to utilize XSU. I'm living in the training manuals - but, I'm still a little unsure about quite a few things . . . but, I'll start with just my immediate situation --> Differing structure between the XML elements and the table field names. (again, I come from the SQL Server universe - so please forgive me if my terminology is incorrect.)

For this example let's call the table (is the correct term in the oracle universe 'SID'?): 'table1' and let's say that I have three fields (columns in oracle?): 'field1' and 'field2', 'field3', and (I realize that this will be totally unexpected) 'field4'. <g>

OK, from the XML document I have the following four elements:

<VOUCHER>
 <INVOICE>
  <VENDOR>
   <ID>

<VOUCHER>
 <INVOICE>
  <ID>

<VOUCHER>
 <PAYMENT>
  <AMOUNT qualifier="PAYMENT" type="T">

<VOUCHER>
 <DATETIME qualifier="BASE_DATE">
  <YEAR>
  <MONTH>
  <DAY>

Alright, so, here's where I'm lost . . .

First: By reading the manual (

http://youngcow.net/doc/oracle10g/appdev.102/b14252/adx_j_xsu.htm#i1016364)

I see that i need to create a view of the table ('table1') where I can reference the fields ('field1', 'field2', etc) with the column alias 'AS' so that the fields match with the expected xml elements. Now, with the first two xml elements, they both end up being '<ID>' . . . and I'm pretty sure that I can't alias two different fields ('field1' & 'field2') as the same ('AS ID' for both of them). So, how do I rectify this?

Second: How do I relate the third xml element example (with attributes of 'qualifier' and 'type') to a field (‘field3’)? For example, there are multiple '<AMOUNT' elements with different attributes - so, I need to differentiate between those.

Third: How do I combine the multiple elements in the fourth xml element example (year, month, day) into a single field (‘field4’)?

Whew! OK, I know there’s quite a bit here . . . but, I’d appreciate any and all input/assistance. You all rock!




DD

 

[SantaMufasa]

A couple of provisos here:[ul][li]I am not literate with (Oracle's) XML, but I don't believe your questions are rooted in XML.[/li][li]I had to do a bit of inferrence in deriving responses to your questions (simply because I am not yet familiar enough with your context), but perhaps my responses can be helpful to you.[/li][/ul]

I can't alias two different fields ('field1' & 'field2') as the same ('AS ID' for both of them). So, how do I rectify this?

I infer that you want to refer to the contents of 'field1' and 'field2' together as a single string named ID. In Oracle, we can concatenate any number of strings together to form a single resulting expression:

SELECT field1 || ' ' || field2 ID...

In the code, above, two successive pipe symbols represent the "concatenation operator" symbol. The resulting string from the above concatenation operations would be the contents of field1, a blank space, and field2, with the alias "ID". ("AS" is syntactically allowable, but superfluous.)

there are multiple '<AMOUNT' elements with different attributes - so, I need to differentiate between those.

I could not derive a meaningful inferrence from this without, at minimum, an example....Sorry.

How do I combine the multiple elements in the fourth xml element example (year, month, day)

If your source for the date information is an Oracle DATE expression, then you have an easy solution. Here is an example of how you can achieve your objective:

select to_char(sysdate,'yyyy-mm-dd') field4 from dual;

FIELD4
----------
2008-04-10

Let us know if any of this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DairylandDan]

'SantaMufasa',

Thank you very much for jumping into this thread and offering your time - it's MUCH appreciated!

OK, I suppose first, I should provide a critical piece of information which I forgot to include in my original post - that being the direction of data flow. OK, here's the deal:

I'm being given a 'well formed' XML document - and I need to get the included data out of the xml document and into one of our Oracle tables (SID?).

So, the data flow is FROM the XML document and TO the Oracle table.

OK, second - let me work through your responses individually with my follow-ups.

In your first response to my questions, you are referencing my 'alias' question. Unfortunately, you've inferred (or I explained) the scenario exactly backwards. <G> Let me try again:

According to the XSU documentation (link provided in my original post) it states that the oracle fields and the corresponding xml document elements much be direct relations. The documentation states "If the XML document does not perfectly map to the target database scheme, then you can . . . . Modify the target. Create an object-relational view over the target scheme and make the view the new target." So (if I understood this correctly), this means that in my view I need to alias any/all fields to correctly correspond with the appropriate xml element. For example: I have 'field1' but I want that to correspond to xml element '<ID>' . . . so, in the view I alias 'field1' 'AS ID'. However, within the xml document I'm receiving, there are two (actually three) elements of '<ID>'. Here are two with which I'm directly working:

<VOUCHER>
 <INVOICE>
  <VENDOR>
   <ID>20070017016</ID>

<VOUCHER>
 <INVOICE>
  <ID>43810</ID>

So, I have two fields which (according to the documentation) I need to alias as 'ID' . . . but, I can't have two fields aliased as the same thing. See my problem?

In your second response to my questions, you are referencing my question about differentiating between multiple editions of elements with differing attributes. Here’s an example of what I mean. The xml might look like this:

<PAYMENT>
 <AMOUNT qualifier="PAYMENT" type="T" AVTYPE="AMT">
  <VALUE>12345</VALUE>
  <NUMOFDEC>2</NUMOFDEC> 
  <SIGN>+</SIGN> 
  <CURRENCY>CAD</CURRENCY> 
 </AMOUNT> 
</PAYMENT> 
<PAID_TO_DATE> 
 <AMOUNT qualifier="PAID" type="T" AVTYPE="AMT"> 
  <VALUE /> 
  <NUMOFDEC /> 
  <SIGN /> 
  <CURRENCY /> 
 </AMOUNT> 
</PAID_TO_DATE> 
<DISCOUNT> 
 <AMOUNT qualifier="DISCOUNT" type="T" AVTYPE="AMT"> 
  <VALUE>67890</VALUE> 
  <NUMOFDEC>2</NUMOFDEC> 
  <SIGN>+</SIGN> 
  <CURRENCY>CAD</CURRENCY> 
 </AMOUNT> 
</DISCOUNT>

So, you see, within the xml, I have three different editions of the ‘<amount’ element – differentiated by the ‘qualifier’ and ‘type’ attributes. If I want ‘field3’ to be associated with only one of these ‘<amount’ elements (I.E., map that field to the appropriate xml element) how do I go about this?

And in your third response to my questions, you are referencing my question concerning combining multiple xml elements for insertion into a single oracle field. I think, in looking at your response, you have interpreted my question (most probably based upon a lack of better explanation on my part) exactly backwards (from oracle to xml – rather than from xml into oracle). So, I want to combine multiple xml elements for insertion into (or, perhaps the more correct word choice is ‘mapping to’) a single oracle field; and I'm not quite sure how to do that.

Again, I really appreciate your time (I realize that we are all busy . . . and you, who read and respond to questions here, undoubtedly have your own work to take care of) and expertise!


DD