OraFAQ Forum: SQL & PL/SQL » xml auto generate atribute

Home » SQL & PL/SQL » SQL & PL/SQL » xml auto generate atribute (19c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

xml auto generate atribute [message #686834]

Wed, 18 January 2023 02:58

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

Hello

Does anyone of you know if it is possible to automatically generate attributes from xml?
I have xml with some structure and I need to create a table with the same data attributes likes xml.

thanks for help

Regards
Martin

Report message to a moderator

Re: xml auto generate atribute [message #686835 is a reply to message #686834]

Wed, 18 January 2023 03:24

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If you have the following XML docs (extract from some old topics) what would be the tables:

<Invoice>
  <LineItem>
  <LineItemNumber>1</LineItemNumber>
  <ChargeCode>P</ChargeCode>
  <ChargeAmount Name="Weight">340.000</ChargeAmount>
  <ChargeAmount Name="Valuation">999.000</ChargeAmount>
  <Tax>
    <TaxType>VAT</TaxType>
    <TaxAmount>16.000</TaxAmount>
  </Tax>
  <AddOnCharges>
    <AddOnChargeName>ISCAllowed</AddOnChargeName>
    <AddOnChargeAmount>-23.000</AddOnChargeAmount>
  </AddOnCharges>
  <TotalNetAmount>333.000</TotalNetAmount>
  <DetailCount>2</DetailCount>
</LineItem>
</Invoice>

<InvoiceTransmission>
  <Invoice>
 <InvoiceHeader>
     <InvoiceNumber>ABX1234567</InvoiceNumber>
 </InvoiceHeader>
 <LineItem>
    <LineItemNumber>1</LineItemNumber>
    <ChargeCode>P</ChargeCode>
 </LineItem>
 <LineItem>
    <LineItemNumber>2</LineItemNumber>
     <ChargeCode>C</ChargeCode>
 </LineItem>
   </Invoice>
   <Invoice>
      <InvoiceHeader>
 <InvoiceNumber>ABX9999</InvoiceNumber>
      </InvoiceHeader>
      <LineItem>
 <LineItemNumber>1</LineItemNumber>
 <ChargeCode>K</ChargeCode>
      </LineItem>
      <LineItem>
 <LineItemNumber>2</LineItemNumber>
 <ChargeCode>T</ChargeCode>
      </LineItem>
   </Invoice>
</InvoiceTransmission>

Report message to a moderator

Re: xml auto generate atribute [message #686836 is a reply to message #686835]

Wed, 18 January 2023 04:15

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

It could be two tables. I just want to know how to read attributes from anyone XML. And based on attributes I can create table.

Report message to a moderator

Re: xml auto generate atribute [message #686837 is a reply to message #686836]

Wed, 18 January 2023 05:30

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I just want to know how to read attributes from anyone XML.

You can use something like:

SQL> with
  2    data as (
  3      select '<Invoice>
  4    <LineItem>
  5    <LineItemNumber>1</LineItemNumber>
  6    <ChargeCode>P</ChargeCode>
  7    <ChargeAmount Name="Weight">340.000</ChargeAmount>
  8    <ChargeAmount Name="Valuation">999.000</ChargeAmount>
  9    <Tax>
 10      <TaxType>VAT</TaxType>
 11      <TaxAmount>16.000</TaxAmount>
 12    </Tax>
 13    <AddOnCharges>
 14      <AddOnChargeName>ISCAllowed</AddOnChargeName>
 15      <AddOnChargeAmount>-23.000</AddOnChargeAmount>
 16    </AddOnCharges>
 17    <TotalNetAmount>333.000</TotalNetAmount>
 18    <DetailCount>2</DetailCount>
 19  </LineItem>
 20  </Invoice>' data
 21      from dual
 22    )
 23  select regexp_substr(data,'<[^/>]+>', 1, level)
 24  from data
 25  connect by level <= regexp_count(data,'<[^/>]+>')
 26  /
REGEXP_SUBSTR(DATA,'<[^/>]+>',1,LEVEL)
------------------------------------------------------------------
<Invoice>
<LineItem>
<LineItemNumber>
<ChargeCode>
<ChargeAmount Name="Weight">
<ChargeAmount Name="Valuation">
<Tax>
<TaxType>
<TaxAmount>
<AddOnCharges>
<AddOnChargeName>
<AddOnChargeAmount>
<TotalNetAmount>
<DetailCount>

Report message to a moderator

Re: xml auto generate atribute [message #686838 is a reply to message #686837]

Wed, 18 January 2023 08:28

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

thanks for help

Report message to a moderator

Re: xml auto generate atribute [message #686908 is a reply to message #686838]

Tue, 31 January 2023 00:27

mathguy
Messages: 38
Registered: January 2023

Member

I don't understand. Is that what you were trying to get?

The answer you were apparently thanking for is one that gives you a list of opening tags of XML elements in your documents. What does that have to do with "attributes"?

Are you using the term "attributes" in the technical sense, as defined in the XML standards? If so, the answers you received so far have nothing to do with that. In any case, at least I, for one, am quite unclear as to what your task is. Showing us the desired output would help immensely. (Of course, it is also possible - but if so, then quite odd - that what you received so far is in fact all you need.)

Report message to a moderator

Previous Topic:	Get customer with GREATEST amount of purchases
Next Topic:	Duplicate rows by generating date based on column value

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 24 20:39:20 CDT 2023