OraFAQ Forum: JDeveloper, Java & XML » How can we put attributes in aliases inside XMLForest

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)

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

How can we put attributes in aliases inside XMLForest [message #480321]

Fri, 22 October 2010 07:45

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.

Report message to a moderator

Re: How can we put attributes in aliases inside XMLForest [message #480345 is a reply to message #480321]

Fri, 22 October 2010 09:22

_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

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 ...

Report message to a moderator

Re: How can we put attributes in aliases inside XMLForest [message #480455 is a reply to message #480454]

Mon, 25 October 2010 00:48

_jum
Messages: 577
Registered: February 2008

Senior Member

I think you can use XMLATTRIBUTES and modify the query to achieve your goal.

Report message to a moderator

Previous Topic:	id
Next Topic:	extract element name hierarchy

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 17:32:56 CST 2025