Home » Developer & Programmer » JDeveloper, Java & XML » How can we put attributes in aliases inside XMLForest (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi)
How can we put attributes in aliases inside XMLForest [message #480321] Fri, 22 October 2010 07:45 Go to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Hi,

I need to generate alias with attributes inside XMLForest.

I mean I want to have generate:
<root>
 <Invoice>
  <InvoiceHeader>
   <InvoiceNumber>XXXXXX</InvoiceNumber>
  <InvoiceHeader>
  <LineItem>
   <LineItemNumber>1</LineItemNumber>
   <ChargeAmount Name="Weight">100</ChargeAmount>
  </LineItem>
 </Invoice>
 <Invoice>
  <InvoiceHeader>
   <InvoiceNumber>ZZZZZZZZZ</InvoiceNumber>
  <InvoiceHeader>
  <LineItem>
   <LineItemNumber>2</LineItemNumber>
   <ChargeAmount Name="Weight">200</ChargeAmount>
  </LineItem>
 </Invoice>
</root>


That's the query you can take to test :
WITH OBIN AS (
SELECT 'XXXXXX' invoice, 1line ,100 charge FROM dual
UNION
SELECT 'ZZZZZZZZZ',2 linen ,200 charge FROM dual
)
SELECT 
XMLElement("root",
XMLAgg(
XMLElement("Invoice", 
XMLForest(
	XMLForest(
	invoice "InvoiceNumber"					  
	) "InvoiceHeader",
	XMLForest(
	line "LineItemNumber",
	charge "ChargeAmount Name=""Weight"""
	)"LineItem"
)
)
)
)
xml FROM OBIN


I have the error ORA-0300001 unimplemented feature.

Someone has the solution to put attribute in xmlforest aliases please ?

Thanks for your help.






Re: How can we put attributes in aliases inside XMLForest [message #480345 is a reply to message #480321] Fri, 22 October 2010 09:22 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
You could use XMLATTRIBUTES as in:

WITH OBIN AS (
SELECT 'X' invoice, 1 line ,100 charge, 'Weight' id FROM dual
UNION
SELECT 'Z',         2 line ,200 charge, 'YYY'       FROM dual
)
SELECT 
  XMLElement("root",
    XMLAgg(
      XMLElement("Invoice", 
        XMLForest(
          XMLForest(invoice "InvoiceNumber") "InvoiceHeader",
          XMLForest(   line "LineNumber", charge "ChargeAmount" )"LineItemNumber",
          XMLElement("ChargeAmount", XMLATTRIBUTES (id AS "name")) "ChargeItem"
        )
      )
    )
  )
xml FROM OBIN;

XML                                                                             
---------------------------
<root>
 <Invoice>
  <InvoiceHeader>
   <InvoiceNumber>X</InvoiceNumber>
  </InvoiceHeader>
  <LineItemNumber>
   <LineNumber>1</LineNumber>
   <ChargeAmount>100</ChargeAmount>
  </LineItemNumber>
  <ChargeItem>
   <ChargeAmount name="Weight"></ChargeAmount>
  </ChargeItem>
 </Invoice>
 <Invoice>
  <InvoiceHeader>
   <InvoiceNumber>Z</InvoiceNumber>
  </InvoiceHeader>
  <LineItemNumber>
   <LineNumber>2</LineNumber>
   <ChargeAmount>200</ChargeAmount>
  </LineItemNumber>
  <ChargeItem>
   <ChargeAmount name="YYY"></ChargeAmount>
  </ChargeItem>
 </Invoice>
</root>                                                                    
1 row selected.

For fixed name use:
...
  XMLATTRIBUTES ('Weight' AS "name")
...

[Updated on: Fri, 22 October 2010 09:28]

Report message to a moderator

Re: How can we put attributes in aliases inside XMLForest [message #480454 is a reply to message #480345] Mon, 25 October 2010 00:06 Go to previous messageGo to next message
czinsou
Messages: 23
Registered: August 2009
Junior Member
Thansk _jum for your answer but the problem is that I can't change the xml structure. That's a file that I have to generate according a specific structure I have to respect it ...
Re: How can we put attributes in aliases inside XMLForest [message #480455 is a reply to message #480454] Mon, 25 October 2010 00:48 Go to previous message
_jum
Messages: 577
Registered: February 2008
Senior Member
I think you can use XMLATTRIBUTES and modify the query to achieve your goal.
Previous Topic: id
Next Topic: extract element name hierarchy
Goto Forum:
  


Current Time: Fri Mar 29 11:04:50 CDT 2024