Home » SQL & PL/SQL » SQL & PL/SQL » xml auto generate atribute (19c)
xml auto generate atribute [message #686834] Wed, 18 January 2023 02:58 Go to next message
mape
Messages: 297
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
Re: xml auto generate atribute [message #686835 is a reply to message #686834] Wed, 18 January 2023 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68352
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>
Re: xml auto generate atribute [message #686836 is a reply to message #686835] Wed, 18 January 2023 04:15 Go to previous messageGo to next message
mape
Messages: 297
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.

Re: xml auto generate atribute [message #686837 is a reply to message #686836] Wed, 18 January 2023 05:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68352
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>
Re: xml auto generate atribute [message #686838 is a reply to message #686837] Wed, 18 January 2023 08:28 Go to previous messageGo to next message
mape
Messages: 297
Registered: July 2006
Location: Slovakia
Senior Member
thanks for help
Re: xml auto generate atribute [message #686908 is a reply to message #686838] Tue, 31 January 2023 00:27 Go to previous message
mathguy
Messages: 10
Registered: January 2023
Junior 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.)
Previous Topic: Get customer with GREATEST amount of purchases
Next Topic: Duplicate rows by generating date based on column value
Goto Forum:
  


Current Time: Thu Feb 09 03:06:24 CST 2023