Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

generating relational data from xml with multilevel node

3230404Apr 18 2018 — edited Apr 18 2018

i have the below xml example where i want to generate relational data from it.

<row id="AA17001J21K8-DRINTEREST">

<fromDate>20171121</fromDate>

<fromDate m="2">20171120</fromDate>

<fromDate m="3">20171119</fromDate>

<fromDate m="4">20171118</fromDate>

<fromDate m="5">20171118</fromDate>

<fromDate m="6">20171118</fromDate>

<toDate>20171121</toDate>

<toDate m="2">20171120</toDate>

<toDate m="3">20171119</toDate>

<toDate m="4">20171118</toDate>

<toDate m="5">20171118</toDate>

<toDate m="6">20171118</toDate>

<noOfDays>1</noOfDays>

<noOfDays m="2">1</noOfDays>

<noOfDays m="3">1</noOfDays>

<noOfDays m="4">1</noOfDays>

<noOfDays m="5">1</noOfDays>

<noOfDays m="6">1</noOfDays>

<balance>24121.02</balance>

<balance m="2">27502.14</balance>

<balance m="3">27485.89</balance>

<balance m="4">30000</balance>

<balance m="4" s="2">1163.94</balance>

<balance m="5">30000</balance>

<balance m="6">30000</balance>

<balance m="6" s="2">1163.94</balance>

</row>

i simply need the output to be like this:

fromDate       toDate        noOfDays     balance1       balance2

20171121      20171121   1                   24121.02

20171120      20171120   1                   27502.14

20171119      20171119    1                  27485.89

20171118      20171118    1                  30000            1163.94

20171118      20171119    1                  30000

20171118      20171119    1                  30000            1163.94

im using script like below (below is a sample script that i used on real data but with the same concept):

/* Formatted on 4/18/2018 4:05:29 PM (QP5 v5.163.1008.3004) */

       SELECT recid,

              from_date.from_date,

              to_dates.to_dates,

              days.days,

              balance.balance,

              balance2.balance2,

             

         FROM t24.FBNK_AA_INTEREST_AC003 t

              LEFT JOIN XMLTABLE (

                           '/row'

                           PASSING t.xmlrecord

                           COLUMNS xml_from_date XMLTYPE PATH 'c1',

                                   xml_to_date XMLTYPE PATH 'c2',

                                   xml_days XMLTYPE PATH 'c3',

                                   xml_balance XMLTYPE PATH 'c4',

                                   xml_balance2 XMLTYPE PATH 'c4'

                                   ) xmldata

                 ON (1 = 1)

              LEFT JOIN XMLTABLE (

                           '/c1'

                           PASSING xmldata.xml_from_date

                           COLUMNS from_date DATE PATH '.',

                                   fruit_idx    FOR ORDINALITY) from_date

                 ON (1 = 1)

              LEFT JOIN XMLTABLE (

                           '/c2'

                           PASSING xmldata.xml_to_date

                           COLUMNS to_dates DATE PATH '.',

                                   fruit_idx2   FOR ORDINALITY) to_dates

                 ON (fruit_idx = fruit_idx2)

              LEFT JOIN XMLTABLE (

                           '/c3'

                           PASSING xmldata.xml_days

                           COLUMNS days NUMBER (10) PATH '.',

                                   fruit_idx3   FOR ORDINALITY) days

                 ON (fruit_idx = fruit_idx3)

              LEFT JOIN XMLTABLE (

                           '/c4[@s=""]'

                           PASSING xmldata.xml_balance

                           COLUMNS balance NUMBER PATH '.',

                                   fruit_idx4   FOR ORDINALITY) balance

                 ON (fruit_idx = fruit_idx4)

              LEFT JOIN XMLTABLE (

                           '/c4'

                           PASSING xmldata.xml_balance2

                           COLUMNS balance2 NUMBER PATH '.[@s="2"]',

                                   fruit_idx5   FOR ORDINALITY) balance2

                 ON (fruit_idx = fruit_idx5)

              LEFT JOIN XMLTABLE (

                           '/c8[@s=""]'

                           PASSING xmldata.xml_accr_amt

                           COLUMNS accr_amt NUMBER PATH '.',

                                   fruit_idx6   FOR ORDINALITY) accr_amt

                 ON (fruit_idx = fruit_idx6)

              LEFT JOIN XMLTABLE (

                           '/c8'

                           PASSING xmldata.xml_accr_amt

                           COLUMNS accr_amt2 NUMBER PATH '.[@s="2"]',

                                   fruit_idx7   FOR ORDINALITY) accr_amt2

                 ON (fruit_idx + 1 = fruit_idx7)

        WHERE recid = 'AA17001J21K8-DRINTEREST'

the script actually worked except for "balance2", u see what i cant achieve is to bring the nodes with "s=2" inline with balance1. the problem im getting is that balance2 is coming but in a different row.

any help plz

Comments

Mark Williams
Answer

Hi,

What your NuGet link points to is the Oracle Data Provider for .NET Core (ODPC) which is different from the Oracle Provider for Entity Framework Core. The EF provider is a separate assembly which has not been released yet (as of this posting date). The EF assembly will require the ODPC assembly.

Regards,

Mark

Marked as Answer by c0036513-d01d-46af-8a69-d47e16c6c3ce · Sep 27 2020
3822801

When will the provider be released?

Alex Keh-Oracle

We're targeting November/December for the public beta.

JoLi

Hi Alex,

that is good to hear. We are currently evaluating Entity Framework Core in a product that runs on .net 4.7.2.

Will the new provider also work in full framework or is .net core required?

66aed53f-0401-48d6-bf2f-390fe8ae1d95

Are you guys still targeting December on initial Beta release? I am currently working on a brand new project rewrite of .net over Oracle with .net core and I hope to see if work nicely with EF Core. I would love to help with the beta testing if you guys are looking for any help there!

Alex Keh-Oracle

We're currently conducting an Oracle EF Core private beta with a small number of customers right now. If you want to join, send an email to us: dotnet_us (at) oracle.com

We still plan to have a wide public beta distribution in December. It will probably be late December.

user902614

Hi, Oracle.

Do you have any updates on releasing beta version of entity framework core support?  It is holding up several development activities for our company. We have both Oracle and SQL Server databases.

Thank you,

Alex Keh-Oracle

Public beta still looking like late December.

You can get access to the private beta by emailing the address in my previous post.

user12147365

Hi , Excuse me , Public beta is released ? or How long does it take?

a0d7b336-f6d4-420a-846e-f15c67c1f11b

Hello, we have been reached January/2019 and still don't have any information about this feature.

I searched all over the site and 18c released documentation as well, but couldn't find a mention about EF Core.

Can confirm if the EF Core feature has already been released in 18c?

Thanks.

Alex Keh-Oracle

The public beta is not yet available. However, the private beta is. If you would like to join the private beta and start using Oracle EF Core now, send an email to dotnet_us (at) oracle.com.

3866898

How long should I expect to wait after sending an email for beta access?

Alex Keh-Oracle

Usually, we can get you access within a couple of business days.

3878615

Any estimation on when the EF Core driver will be released ?

Abderrahman Cheddir

Hello, we're actually working on a .Net Core project targeting an oracle database using ODP.NET for .Net Core and we'd like to migrate to EF Core, any news about the public beta or the final release, will it be available on the second quarter of this year ?

Alex Keh-Oracle

After the public beta and hearing the feedback, Oracle will have a better idea of when production will be for Oracle EF Core. My best estimate right now for production remains second quarter this year.

The public beta should be available quite soon.

user3120128

Hi Alex.

I'm having troubles with private beta, where can I find documentation or where to report

thanks

Alex Keh-Oracle

The doc is enclosed in the NuGet beta package. You can email me if you encounter an issue.

3873836

If you rename the .nupkg file to .zip, you can just unzip it like a regular zip file, and find the documentation inside.

3889708
Mark Williams

Hello,

That is the link for the Oracle Data Provider for .NET Core product. I'm not sure if that is the specific product you mean. It is not, for example, the link for the Oracle EF Core provider product.

Regards,

Mark

3907530

Hi Guys

Any updates on the public beta date?  I've requested private beta access almost a week ago, but haven't heard anything.  Are you still targeting the second quarter for the full release?  Seeing as the public beta hasn't started, I'm thinking that date will be pushed also? 

Thanks

Justin

Alex Keh-Oracle

Hi Justin,

The beta invitation to you was sent out on Feb. 28. I'll resend it again.

3907530

I received it the second time around.  Thanks Alex!

d77098dd-c260-43de-803d-b1d7ecfaae8c

Hi Alex,

Would you mind send me the invitation for the oracle EF Core? Many thanks.

Best regards,

Hongjun

3914849

Hi Alex,

Could you please send me the invitaition for Oracle EF Core? Thanks.

Alex Keh-Oracle

If you would like to join the EF Core beta program, email dotnet_us (at) oracle.com.

e903bc85-56d0-4a47-aceb-52482873f525

Could you please tell us what the hold up is in getting at least the public beta version out?  It's way behind schedule now.

Alex Keh-Oracle

The ODP.NET EF Core Beta 3 is available on nuget.org.

https://www.nuget.org/packages/Oracle.EntityFrameworkCore/

1 - 29
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 16 2018
Added on Apr 18 2018
1 comment
1,134 views